# Formula Structure For IF / THEN With Logical Test Value Ranges

Feb 19, 2014
I'm trying to create a formula that will determine the bonus (%) for sales made. Here's the table:

<10000 sales = 0% bonus

>=10000 sales but <=29999 sales = 5% bonus

>=30000 sales but <=to 44999 = 7% bonus

>50000 sales = 10% bonus

Since the sales and bonus figures are subject to change each year I'd like the formula to point to the numbers but that part seems easy if I can get the formula right.

View 3 Replies
ADVERTISEMENT
Oct 8, 2012

I have a spreadsheet where I have a list of patients who were evaluated on a certain date unique to that patient. Some of the patients have a subsequent date of death, and some do not (they're still alive).

I need to create a logical test which indicates whether or not the patient died at specific intervals after their unique evaluation date-- at 3 months, 6 months, and 12 months. Additionally, I need to measure whether the patient died at 0-6 months, or 0-12 months, or is still alive.

In other words, if Patient A was evaluated on 1/1/2008 and subsequently died on 10/31/2008, I need to do a logical test that says 3 months = false (no death), 6 months = false (no death) and 12 months = true (death). True = 1 and False = 0 (so I can do a simple column add to figure out how many died at each interval). Additionally, for the 0-6 month test it should calculate = 0, but for the 0 - 12 month test it should calculate = 1.

I'm totally stumped as to how to do this. Currently my spreadsheet reads as follows: (I've put ** next to cells that I need formulas to calculate. If no **, assume it's a value I've inputted manually)

A1 = Patient number (numerical order)

B1 = Patient Initials

C1 = Initial Evaluation date

D1 = Date of death (if any- blank if patient is still alive)

E1 = Patient death indicator (1 = dead, 0 = alive)

F1** = Indicate whether or not patient died within 3 months of evaluation (1 = dead, 0 = alive)

G1** = Actual date of death within 3 month interval

H1** = Indicate whether or not patient died within 6 months of evaluation (1 = dead, 0 = alive)

I1** = Actual date of death within 6 month interval

J1** = Indicate whether or not patient died within 12 months of evaluation(1 = dead, 0 = alive)

K1** = Actual date of death within 12 month interval

L1** = Did patient die within 0-6 months of evaluation? (1 = dead, 0 = alive)

M1** = Actual date of death within 0-6 months

N1** = Did patient die within 0-12 months of evaluation? (1 = dead, 0 = alive)

O1** = Actual date of death within 0-12 months

View 7 Replies
View Related
Apr 8, 2009

I am trying to create a logical test with three possible results:

h2 <10.2 "needs improvement"

(if false) h2=10.2 "meets standard"

if false h2>10.2 "exceeds standard"

Basically, I am referring to a cell to determine if it meets, exceeds or does not meed a specific standard. In this case 10.2.

View 4 Replies
View Related
Feb 7, 2010

Under what conditions does a simple logical test, like

View 5 Replies
View Related
Sep 16, 2008

Here is the formula I am trying to write:

=IF(A21=(VLOOKUP(XX52,AN24:AN52,0,V38),V45,0)

The above is in cell X45

This does not work, but this is what I want it to do:

I have a list of text, if cell A21 equals a range in that text then I want the cell to return whatever is in Cell V45, if A21 does not equal within the range of text, then I want it to return a zero in cell X45.

You can see that I tried using Vlookup as the logical test, but I am not doing something right or vlookup cant be used that way.

View 9 Replies
View Related
Feb 17, 2012

I am trying to build a logical formula and it turns wrong values. I have four criteria in numbers, the codes are: 1 invoices, 3 credits, 7 Debits, 8 Returns, 9 Payments. All of these codes have positive numbers. I am trying to convert "credits", "returns", "payments" in negative . the rest "invoices" "debits" stay positive. In column A i have dollar values all positive, column B "codes" mentioned above, column C "i want to put the correct values.

View 9 Replies
View Related
May 17, 2007

I have data in column A. The data contains a name, an underscore and initial/s. For example: rebecca_rt, john_j, etc... I'm trying to pull only the characters to the right of the underscore. I played around with the below formula that I found under a similar question thread. But, it doesn't quite work. As it is now, it returns ecca_rt when I just want rt. I'm sure this is probably really close =IF(ISERROR( FIND("_",A1)),A1,RIGHT(A1,FIND("_",A1)-1))

View 3 Replies
View Related
Jun 18, 2014

My logical test is =IF("D2"="E2",1,2). When I autofill or copy down, the row numbers do not change to "D3"="E3", etc. How do I delineate text and also have the ability for autofill or copy to change the rows as it goes?

View 2 Replies
View Related
Aug 22, 2014

Basically I have a column (lets call it column A) whereby I manually fill the cells green once I have received some documents, another column which has a numeric value in it (column B) and I want to create a third column which basically just copies column B but ONLY if column A is filled with a colour (actual colour doesn't matter cause I only use green)

I tried using the IF function but I don't know how to use cell colour as the logical test

View 3 Replies
View Related
Nov 9, 2009

I'm trying to use the following formula to verify if the "," is within a cell. Something is not right, because even for True conditions, I'm getting a False return. I think my True logical test is not written right.

View 3 Replies
View Related
Nov 15, 2013

I need a formula that will return a range of values from a list.

Example: I need a formula in cell A2 that looks at the list and will return all numbers that are >99 but 199 but299 but

View 6 Replies
View Related
Feb 28, 2013

excel 2007. Here is the situation:

I am using the Index and Match function to lookup for two specific criterias in a different worksheet. So far, it is working well, but it gets complicated. I want to look for the criterias in 6 different worksheets based on what a certain column is saying. Here is an example:

A

B

C

D

[Code].....

So, if the continent is Asia in the column A, I want Excel to look in the Asia worksheet for the city and the venue and return me the contact information. Same, if the continent says Europe, I want it to look in the Europe worksheet or the city and the venue and return me the contact information. So on and so forth.

The formula I have at the moment is this:

{=INDEX('Asia'!$1:$1048576;MATCH(B3&C3;'Asia'!D:D&'Asia'!E:E;0);9)}

How do I incorporate the logical test for it to look for the proper worksheet knowing that all my continent worksheet have the same structure?

View 3 Replies
View Related
Jul 24, 2013

I am trying to get excel to count a value if (and only if) two logical conditions are fulfilled on two different cell ranges.

1st condition: In cell range x, the value should = C2

2nd condition: In cell range y, the value should be greater than 0

I've tried an AND statement and failed, and I've also tried COUNTIF statemetns and had no more luck. This could well be due to my skill level however.

View 4 Replies
View Related
Apr 22, 2006

I am attempting to calculate commission (J2) based on the data entered in cell D2

1. The total fees (H2) is a part of the calculation. It represents a value from .5% to a maximum of 3%.

2. If the Loan Description is specifically ‘80/20’ then 80% of the Loan amount is used in calculating the commission. (note: ‘80/20*’ is also a valid entry). Otherwise the total loan amount is used.

D E F G H I J

Loan Dsc. Int. DSCNT% Y-S-P% Fees Loan Amount Commission

80/20 7.52.0 1.0 3.0 $137,403 $4,122.09

I attempted this formula and obviously it’s incorrect:

Calculating Commission (J2)

=IF(H2=" "," ",=IF(D2="80/20",(I2*0.80)*(H2*0.01),I2 * (H2*0.01))

1. If H2 is blank then TRUE enter a blank

2. FALSE: H2 contains a fee rate then calculate the commission Commission Calculation: If the Loan Description is 80/20 then take 80% of the loan amount and multiply it by the rate fee amount (as a percentage) to get the commission. If the Loan Description is NOT 80/20 then use the whole loan amount in the calculation.

View 3 Replies
View Related
Jun 22, 2014

In column N I have a list of dates. In column Z I want to display either "Yes" where the date in column N has already passed or "No" if the date in column N has not passed. The formula I'm using in column Z is =IF(N2<TODAY(),"Y","N") however it just gives me "No" on every row regardless of whether date is passed or not.

View 11 Replies
View Related
Dec 16, 2008

I need a solution for this situtaion! Iam using a french version of excel2003!

I have #DIV/0! in P24 (i.e an error value) So I need to check in Q24 such that if (P24=#DIV/0!,0,P24). I need to have a logical formula for this one!

View 3 Replies
View Related
Sep 14, 2009

How to find whether a particular cell is blank or is text in a given row/column?

To elaborate more, I have some data in a row:

A B C D E

15 N 16 17 18

I am using a below formula to find whether there is any text in the row

ISTEXT(row(A1:E1)) but I am getting result as False.

View 9 Replies
View Related
Feb 4, 2014

2 formulas that I need for my work (related with linear recurrences; this is serious work). First, I would like to know if is it possible to write a formula (or a set of formulas) that allows to write in a first column, in the first cell, the number 1 and in the following m cells, m distinct numbers chosen in a set S of n numbers (n > m); in the second column again the number one, and another set of m distinct numbers chosen in the same set S (here the order of the elements matters which means that the set {1,2} can be written as (1,2) and (2,1)). And I would like to do this until all the choices of m (ordered) numbers chosen in the set S are exhausted. Example: S = {2,3,4} which means that n=3. Suppose m=2. Then the columns would be

111111

223344

342423

The second formula I'm searching is (I think) a logical formula. What I would like is a formula that checks if the numbers in a fixed number of consecutive cells of a column are distinct and also non-null. If that's the case the formula should assign TRUE and if (at least) two cells are equal or (at least) one is zero, it would assign FALSE.

View 2 Replies
View Related
Sep 16, 2009

I want to use the If formula but want the value if true/false to be another formula, not just a set value. Here's what I came up with (obviously wrong, please excuse my lack of experience) =IF(D2>C2,"=(D2-C2)*5","=((60-C2)+D2)*5 ")

View 2 Replies
View Related
Sep 28, 2009

I am trying to get more than one outcome from a logical function in a single cell. I am teaching a class and i want this particular cell to add up all the points from tests and quizzes and then give me an outcome of a grade (A,B,C,D, or F) Right now i can only get the cell to give me an outcome of A or B. I need the cell to be able to give me 5 possible outcomes (A,B,C,D,F).

Right now i have this formula in this particular cell......=IF(Q6>234,"A")......so if Q6 is greater than 234pts they get an A...if not they get a B. How can i also give ranges of total points to have the cell give C,D, or F?

View 5 Replies
View Related
Jun 12, 2006

I am working between two worksheets. In one worksheet I have a list of

account numbers. In the second worksheet I have the same account numbers in

a different order. What I am trying to do is, If the account number from

worksheet one is found in the column with the account numbers in worksheet

two, I want to display the matching title for the account number that is in

the same row but different column in worksheet two.

So essentially (if acct # from column x in wks 1= an acct in number in

column m from wks 2, display the corresponding title in the same row of the

matching acct # found in column m). Is this possible?

View 10 Replies
View Related
Jul 10, 2013

I have following formula that returns #value!

=IF(OR(OFFSET(LIVE_JNL,ROW(5:5),COLUMN(C:D))="",OFFSET(LIVE_JNL,ROW(5:5),

COLUMN(C:D))=0,""),OFFSET(LIVE_JNL,ROW(5:5),COLUMN(C:D)))

Where the value in the cell which is referenced is either blank or zero then blank must be returned, otherwise the value which is being referenced by the offset formula.

View 2 Replies
View Related
Aug 8, 2007

what was wrong with this formula, an error message comes up saying VALUE, im trying to get the formula to say that - If 2 dates are different then subtract one from the other to get a number of working days, but if the dates are both the same then to -1 from the value as for some reason the value is coming back as 2 when it should be 0?

=IF(OR(M17>0,N17>0),NETWORKDAYS(M17,N17,holidays)+1+Q17,IF(OR(M17=N17),NETWORKDAYS(M17,N17,holidays)-1,0))

View 9 Replies
View Related
Dec 2, 2009

Sometime it’s so hard to explain what it is you are having problems with, specially in the little title. I'm trying to write a formula which should be very simple but isn’t . The formula is in box F10 if B10 has anything in it and its going to have a mixture of text and numbers (first text then numbers) then it’s should equal to the value in the box C7. Here is the formula I wrote that doesn’t work.

View 4 Replies
View Related
Aug 21, 2013

I am trying to determine Long Term Gain (LTG,) Long Term Loss (LTL,) Short Term Gain (STG,) Short term Loss (STL,) or No Loss nor Gain (NGL)testing two cells (A1 and B1)and setting a third cell (C1) to the text LTG, LTL, STG, STL, or NGL depending on the results of testing cells A1 and B1.

A1 represent a number of years and B1 represent gains or losses (negative)in dolars.

The way I see the logic is as follows:

If cell A1 or cell B1 are either one of them equal to 0, then it is neither a Gain nor a Loss (NGL.)

If cell A1 is greater than or equal to 1, then it is Long Term; else, if A1 is greater than 0 and less than 1, then it is Short Term.

On the other hand, if cell B1 is greater than 0, then it is a Gain; if B1 is less than 0 (a negative number,) then, it is a Loss.

I need to find (if it is posible in Excel) one formula to test the two cells for posible outcomes:

If A1 = 0 then C1 = NGL

If B1 = 0 then C1 = NGL

If A1 >= 1 and B1 > 0 the C1 = LTG.

If A1 >= 1 and B1 < 0 the C1 = LTL.

If A1 < 1 and B1 > 0 then C1 = STG

If A1 < 1 and B1 < 0 then C1 = STL

View 3 Replies
View Related
Jun 27, 2013

From a large worksheet I'm trying to find out if a particular value is used in that formula. E.g. formula: (In cell C1)=A1+B1*.75

I want to check if .75 is used in the formula (which is 75%) There are 1000's of record would it be possible to check the entire column C

View 6 Replies
View Related
May 11, 2007

I'm having an intelligence lapse and I need help with a formula

Simply need to return a "1" if value in Cell X is equal to or greater than -(minus)4% and a "0" if value is less than -(minus)4%.

View 9 Replies
View Related
Jan 25, 2007

I am creating a spreadsheet to monitor horse race betting but my formulas aren't coming out right.

The problem is to do with adding winnings to a running balance or minusing each bet's stake money from the running balance if the bet loses.

I have two formulas that are giving me a problem, both are in the P column in the attached spreadsheet. The first one, located in row 2, being slightly different as it is the first data row and so contains the starting balance in the formula.

=IF(OR(J2="W",J2="P",300+S2),IF(J2="L",300-O2,"")).

This produces a blank or error result.

=IF(OR(J3="W",J3="P",P2+S3),IF(J3="L",P2-O3,""))

This produces an error whatever is in the J column

View 9 Replies
View Related
Dec 28, 2008

I need to take a specific action when a cell has an actual formula in it versus when it just has a "value". Is there a procedure or command which will allow me to identify if a certain cell has a "formula" (like =sum(a1: a5) ) or just a value.

View 3 Replies
View Related
Apr 11, 2008

I teach history to four different groups of students that, in my spreasheet, I have labeled "1", "2", "3", and "4." The number of the group to which each student belongs is listed in CK2:CK100

Each quarter, I give all students a test and list the corresponding score next to each student's name. The scores are listed in CM2:CM100.

The name of each student appears in CL2:CL100.

I am looking for the formulas to extract the highest five scores in each group starting at CS2. So CS2 would contain the value of the highest score obtained by a student in group 1, CS3 the second highest score in that group and so on through group 4.

View 9 Replies
View Related