Using multiple IF statements in Excel
The IF() function in Excel allows you to evaluate a situation which has two possible outcomes (e.g. sales are greater than $1000) and calculate a different value for each outcome. However, sometimes you need to work with situations where there are more than two possible outcomes. That's where multiple, or nested, IF functions come in handy. In this tutorial we'll cover how to use nested IF functions to calculate sales commission for a team of sales people, given a range of different commission rates.
This lesson assumes you are already familiar with the IF function. If you aren't, you can learn how to use IF statements here.
Remember that the IF function works by evaluating a logical test - a calculation which can only be TRUE or FALSE, or 1 or 0. It then includes a calculation to perform if the logical test is TRUE, and another calculation if it is FALSE.
Nested IF functions work by replacing one or both of the TRUE/FALSE calculations with another IF function.
Calculating commission for a sales team based on monthly sales
Imagine you have a sales team of five people, and you need to calculate their commission for the month based on their sales figures.
-
You put the data into the following table:
-
Your commission plan works as follows:
- If someone sells less than $400 in a month, they get 7% commission.
- For sales between $400 and $750, they get 10% commission.
- For sales between $750 and $1000, they get 12.5%
- For sales over $1000, they get 16%
-
Rather than calculate each of these commission figures individually, you decide to use a nested IF formula instead. The logical tests you would use in this case are these:
- Is commission less than $400? If TRUE, then calculate commission.
- If FALSE, then is commission less than $750? If TRUE then calculate commission.
- If FALSE, then is commission less than $1000? If TRUE then calculate commission.
- If FALSE, then calculate commission (because it must be more than $1000 - we don't need to do another logical test for this).
-
The formula to represent this to calculate commission for Bob looks like this (I've highlighted the logical tests in bold to help you understand now the formula is put together:
=IF(B4<400,B4*7%,IF(B4<750,B4*10%,IF(B4<1000,B4*12.5%,B4*16%))) -
This formula can be confusing the first time you look at it. Let me run through it again.
- The logical test in the first IF statement checks if the sales figure in B4 is less than $400. If it is, it calculates commission at 7% and stops calculating. Otherwise, it must be greater than or equal to $400, so we move on to the next IF statement.
- The logical test in the second IF statement checks if the sales figure in B4 is less than $750. We already know it must be more than $400 or we wouldn't have got to this point. If it is less than $750, it calculates commission at 10%. Otherwise it must be greater than or equal to $750, so we move on to the next IF statement.
- The logical test in the third and final IF statement checks if the sales figure in B4 is less than $1000. If it is, it calculates commission at 12.5%. Otherwise, it must be greater than or equal to $1000, so it calculates commission at 16%. At this point there are no more IF statements, no more logical tests we need to do, and we have our answer.
-
Here's how our spreadsheet example looks once the formula has been entered into the Commission column. I've put the formula for each sales person in the cell next to the commission calculation so you can see how it looks:
- Check Rajiv's commission figure in the table above. Rajiv sold $750 in the month, which is right on the threshold between 10% and 12.5%. What commission does he get? He actually gets 12.5% because the formula checks whether his sales were less than $750, not less than or equal to $750. That means he receives commission at 12.5%. So it's important to pay attention to how you construct your logical tests.
Some things to remember when using nested IF functions
Nested IF functions are very powerful, but there are some things to consider before you dive in and start using them.
- As you can see from the commission formula above, using nested IF statements gets complicated quite quickly. Trying to decipher this takes a moment or two, especially if you haven't looked at the spreadsheet in a while.
- Making changes is also challenging - imagine if you decided to introduce another commission threshold of $850. You'd need to add another IF function into the formula.
- You can have up to 64 IF functions nested in a formula in Excel 2007, 2010 and Excel 2013. Excel 2003 only supported 7 IF functions in one formula. That said, if I find myself needing more than 10 I start considering other approaches to my problem - managing that many IF functions in one formula quickly gets complicated, never mind if you have to manage 20, 30, 40 or more.
- The VLOOKUP function can sometimes be a better solution in a scenario like this. You can see a really good example of where you should learn how the VLOOKUP function works in one of the comments below.
-
That's not to say that VLOOKUP is automatically a better solution:
- A commission spreadsheet like the one shown above is a good example where using VLOOKUP would be a better approach.
- However, if you needed to use different calculations depending on the outcome of each logical test, then nested IF statements may well be a a better way to go.
-
An example might be where sales people who exceed $1000 in sales also get a bonus of $100. The formula would then look like the following, which is not so easy (although not impossible) to do with a VLOOKUP function:
=IF(B4<400,B4*7%,IF(B4<750,B4*10%,IF(B4<1000,B4*12.5%,B4*16%+100)))
Finally, I've supplied a copy of the spreadsheet I used for the example above. You can download it here.
Our Comment Policy.
We welcome your comments and questions about this lesson. We don't welcome spam. Our readers get a lot of value out of the comments and answers on our lessons and spam hurts that experience. Our spam filter is pretty good at stopping bots from posting spam, and our admins are quick to delete spam that does get through. We know that bots don't read messages like this, but there are people out there who manually post spam. I repeat - we delete all spam, and if we see repeated posts from a given IP address, we'll block the IP address. So don't waste your time, or ours. One other point to note - if you post a link in your comment, it will automatically be deleted.
Comments on this lesson
Nested IF statement
Use this formula IF(E1<5999,150,IF(E1<60007999,300,IF(E1<800011999,400,"Some stmnt here")))
which use the IF statement with the Cell reference and check the last statement.
Make sure that the number of
Make sure that the number of If's you use are the same in terms of number with the number of closing bracket
help ! i have tried multiple diff formulas and nothing
min is 75 $ - 0.55 per kg - +45 0.55-+1000.60- +500 0.70 - +500 0.75- +1000 0.85.- +2000 0.90
gross weight is 100
2000
3000
234
122
1222
300
6000
formula
=+IF(AND(C13<5999),150,IF(AND(C13>=6000,C13<=7999),300,IF(AND(C13>=8000,C13<=11999),400,IF(AND(C13>=12000,C13<=14999),500)))) and so on
Thank you IF(AND(),)
This is really helpful, luckily i come across your post about IF(AND(),) condition.... Thanks a lot.
How to combine multiple IF Conditions
I want to calculate %age for the values located at different cells.
Min to mean% and mean to Max%, If the cell value is below mean it should take one formula and if the cell value is above mean it should take another formula.Tried below formula but could not get the result, pl advise
IF($V3<AA3,($V3/AA3*100-100),IF(AA3<$V3,($V3/AA3*100-100)))
USING THE FOLLOWING
USING THE FOLLOWING INFORMATION, PLEASE PROVIDE ME THE CORRECT FORMULA IN EXCEL.
Gross Monthly Earnings Amount to be contributed
Php 8,999.99 and below Php 100.00
9,000.00-9,999.99 112.50
10,000.00-11,999.99 125.00
11,000.00-11,999.99 137.50
etc.
Thank you in advance.
Use a VLOOKUP table instead of multiple IF statements
Hi Felix
Whilst you can get the result you want using multiple IF statements in a formula, I recommend you look at using a VLOOKUP function instead. You'll find it a lot easier, and more flexible if you need to change it later. You can learn about the VLOOKUP function here.
David
@Samuel Wainaina
Use VLOOKUP with true option for your range
its much easier than using IF function
If Statement
Need help. wrote following If statement, but all it gets me is True or false.
=IF(D5>89%,"A",If D5>79%,"B", IfD5>69%,"C")))))
Tryding to equate the percentages to a letter grade
Try without the % sign
I'd try this:
=if(d5>.89,"A",if(D5>.79,"B",if(d5>.69,"C","Fail")))
Note how I used the numeric value rather than a % value.
The example you gave was also missing the () in the correct places. Also, if you don't have a value for "Fail" as shown in my example, it will show FALSE as the result in the cell. You could put "" if you want the cell to be empty if the grade is not above 69%.
Also consider whether you should use >=.9 rather than >.89 - this will be more accurate if 90% is required for an A grade (your example would assign an A to a grade of 89.5%.
You can use the percent
You can use the percent symbols your missing the parenthesis after your IF
I don't even know where to start..
First, I would like to say thank you for your terrific lessons and taking the time to respond to the questions your visitors ask; it is very kind and helpful.
I hope you can help me, as I don't even know where to start. I am to create a formula in which the changes will be consistent throughout. Here is an example of what I am trying to achieve:
I have a dollar amount in cell M2. This dollar amount is sales numbers.
Column N is titled Blue Shirts, O is Red Shirts, P is Green Shirts, Q is Pink Shirts and R is Orange Shirts.
There is a bonus for each shirt the salesmen sells; the bonus is also determined by their total sales.
If the salesrep sells less than 750, there is no sales volume bonus. If they sell between 751 and 1,200 there is a 2% bonus; between 1,201 and 2,000 there is a 3% bonus; if they sell between 2,000 and 2,500 there is a 4% bonus, Etc. Etc.
If they sell a Blue shirt, the bonus is 0%, Red shirts is a 2% bonus, Green is a 1.3% bonus, pink is a 1.3% bonus and orange is a 1.3% bonus. ** This bonus will be on top of the sales volume bonus!
Each column for the color of shirts sold has an ‘X’ marking if they sold that color.
Is it possible to create a formula using an IF formula which will allow me to calculate the sales volume bonus AND the bonus for color of shirts sold so it is all tied in together?
I hope this makes sense, it is very hard to explain! This is the formula I have so far for just calculating the sales volume bonus (something is not working right, I am having a hard time making it consistent all the way down the rows):
=IF(M2<400000,M2*Data!$C$3,IF(M2<400001,M2*'Volume Bonus'!$C$5,IF(M2<600001,M2*'Volume Bonus'!$C$6,IF(M2<900001,M2*'Volume Bonus'!$C$7,IF(M2<1200001,M2*'Volume Bonus'!$C$8,IF(M2>1800001,M2*'Volume Bonus'!$C$9))))))
I thank you so much in advance for even taking the time to look at this problem, I sure hope you can help!
I should have been more clear..
For an example, the end result will tell me if the salesrep sold 450, they receive a 2% bonus; they also sold a red shirt and a orange shirt (which makes it a total of 3.3% bonus. How much is the total bonus given?
This will need to be done for a few hundred rows. Each column for the shirt colors have an X in the row to indicate if they sold that color...
Complicated Sales Commission Calculation (with download)
Hi Kristin
What I love about Excel is that there are many ways to skin this cat. Here's one which doesn't even use any IF functions (and stays considerably less complicated as a result).
First, let me show you how I interpreted your scenario:
I've calculated the Sales and Shirt bonuses in two separate columns, but the formulas could easily be combined into the same column once you've got it working.
I've also made an assumption that if some sells <750 then they get no bonus at all. That's why sales person B and C get nothing even though they both sold red shirts. It's easy to remove that assumption from the example, as you'll see.
I also created two separate tables to contain the threshold sales values and the commissions for each colour so that I can keep my formulas simple. I'd normally put them on a separate sheet to the sales data, but for this example they're on the same sheet:
Note that I have named each of the cells in the Shirt Bonus table so I can refer to the formulas by name. I won't cover how to do that here, but will come back to it in another lesson. Suffice to say that the cell containing the bonus for Blue is called, well, Blue. Naming a cell lets me refer to it by name in a formula rather than by using $F$14.
I then created the formula to calculate the sales commission based on actual sales (Sales Bonus). For this, I used a VLOOKUP() function to compare the sales with the values in the Sales Bonus Table. Here's the formula for sales person A:
=VLOOKUP(B2,$B$14:$C$18,2,1)
You can learn more about how to use VLOOKUP here.
I calculated the Shirt Bonus like this:
=((C2="X")*Blue+(D2="X")*Red+(E2="X")*Green+(F2="X")*Pink+(G2="X")*Orange)*(B2>750)
As I promised, no IF functions at all. Here's how this works.
- It looks at the value in C2 (the Blue column) to determine if it contains "X".
- If it does, the value of (C2="X") is returned as TRUE, or 1.
- If it doesn't, the value of (C2="X") is returned as FALSE, or 0.
- I then multiply the result by the value in Blue. If it doesn't contain "X", then the result will be multiplied by 0 which gives 0. Otherwise it's multiplied by 1, which gives whatever is in Blue (or Red, and so on)
- I repeat this for each column.
- Finally, I then look at the sales value to determine if it is >750. Again, the result is either 1 or 0. I multiply this by the rest of the formula. So if the sales value is <750, the result of the whole calculation is 0, otherwise the value is allowed to stand as calculated.
If you want to pay a bonus on shirt sales regardless of the $ value of sales, simply change this formula like so:
=(C2="X")*Blue+(D2="X")*Red+(E2="X")*Green+(F2="X")*Pink+(G2="X")*Orange
Having done this, I then add the two commission values together in column J, and calculate the $ bonus in column K. Once again, you could combine all of these into a single formula if you want.
The only shortcoming of this approach is the need for the two tables for Sales and Shirt bonuses. However, while it might be a shortcoming, it also gives you greater flexibility to extend the number of shirt colours in future, and also to change the threshold sales levels and related commission amounts.
Hopefully this gives you the result you're looking for - if not, please let me know!
I've attached my worked example here for you to dissect and re-use as you see fit. If you'd like more help, feel free to get in touch directly via the contact form.
David
if function with 4 arguments
please help me with my problem
in attendance matter
scenario:
if les than 8:30:00 am it will appear PRESENT
if greater than 8:31:00 am it will appear LATE
then if 9:01:00 am it will appear is HALFDAY
den if absent it will appear ABSENT
hope you can help me with this problem
In Between
How would you do in between ranges like this:
=IF(G4<100,100-G4,IF(G4>100<105,105-G4,IF(G4>105<115,115-G4,,IF(G4>115<125,125-G4,)))
Using IF on ranges
There are three ways to achieve this.
Option 1
In your example, you are doing this:
If G4 < 100 then the result is 100-G4. If G4 is not < 100, then move on to the next IF statement.
You have then tried to use G4>100<105, i.e. if G4 is more than 100 but less than 105. The thing to remember is that we already know that G4 is greater than 100, because it failed the test in the first IF statement. We wouldn't even be using the second IF statement if it had passed that test. So your function could look like this:
=IF(G4<100,100-G4,IF(G4<105,105-G4,IF(G4<115,115-G4,IF(G4<125,125-G4))))
Remember that each IF statement is only evaluated if the previous IF statement tested FALSE.
Bear in mind that the way I've written this function assumes that if G4=100, then the calculation will be 105-G4. Is that what you wanted? If not (i.e. if G4=100, then calculate 100-G4) then you should change each IF statement to <=, rather than <.
Option 2
The other option you have is to use the AND function as well as the IF function, like this:
=IF(G4<100,100-G4,IF(AND(G4>=100,G4<105),105-G4,IF(AND(G4>=105,G4<115),115-G4,IF(AND(G4>=115,G4<125),125-G4))))
This is longer, more complicated, but allows for a situation where you need to be specific about the parameters of the test you're performing. Note that the AND function requires that both statements be TRUE for the AND test to return TRUE to the IF function. As a side note, you could also use the OR function here if only one of the statements needed to be true.
Again, notice that I've used the <= symbol in each AND - if I hadn't, the formula would not always have returned the correct result.
Option 3
You can also use a VLOOKUP function rather than an IF function. I'd be inclined to take this option since it allows you to create a table of threshold values that the VLOOKUP function references, and you can change or add to those values at any time without having to rework your formula.
You can read more about the VLOOKUP function here (opens in a new tab).
Thank you! I test both, I
Thank you!
I test both, I wanted to avoid doing a vlookup to another table, but that is a great option. Both option 1 and 2 work, I went with option 2 and modified it to handle %.
=IF($G31<1,1-$G31,IF(AND($G31>=1,$G31<1.05),1.05-$G31,IF(AND($G31>=1.05,$G31<1.15),1.15-$G31,
IF(AND($G31>=1.15,$G31<1.25),1.25-$G31,"Achieved Top Tier %"))))
I really appreciate you taking the time to respond and so quickly!!!
Another Choice for Zero
Hi! Please can you help me? I need an IF equation for an answer of zero that returns different text than a false response. Thanks!
Situation (a) Plan is more than or equal to Actual = answer of Well Done!
Situation (b) Plan is less than Actual = answer of Oops...Over!
Situation (c) Plan=zero and Actual=zero = answer is no text at all.
=IF text question
can you help me please
i need a formula that will return a text initial based on the text in another cell;
for instance
if cell C20 contains "JAIL" (w/out quotations) then T20 will show "J" (w/out quotations)
i have been working on this for some time and can only get it to return #name?
thanking you in advance!!!
laura
THANK YOU!!! after just a few
THANK YOU!!! after just a few minutes of searching your lessons i figured it out..
=IF(C20="JAIL","J","")
and it was simple as i thought it would be.. i just couldn't see the forest for the trees i suppose :)
laura
how to use If and() & 0r() fuction
which funtion is used and how to get greatest number among them which can be written as e.g "first NO is greatest".plz solve my problem
Help
Hi,
I wonder if you can help. I am trying to Apple this formula for about 90 players using VB how do I do so?
=IF(G2:G15=AR4,AS4)
What I need to do is nested IF Statements however Excel only allows 7, so I thought maybe I could create a function in VB Excel.
What I need i to do is auto populate a cell A with if Cell B (Player Name) matches Cell C (Player Names)
Ok so to put it in pseudo code:
IF( Player Name entered in Cell A is = Player name on a list of players in Cell B, Take that Players Position in Cell C and put it in his Postion Column in Cell D)
E.G. I enter Tom, it looks in Cell B, if Tom Exists in Cell B, it looks for his Playing Position e.g. Defender and auto populates his Position in Cell D so resulting in it tell me Tom is a Defender.
I now want that to do so for any given cell in A.
Hope I make sense, and I hope you can help. Thanks in advance
Hi I have a similar scenario
Hi
I have a similar scenario but it has an accumulation on rows so will try to explain.
I have a sales threshold of £10,000 before i earn commision, in my spreadsheet i need to record products sold and the order value. To help with the formula i have created two columns, "Order Value" and "Claimable Value". So for instance in row one i sell something for £8,000 so enter this in to the "Order Value" column, in the next column "Claimable Value" i used the formula =IF(B2<(A1),"0",SUM(B2-(A1))). So in A1 i enter my threshold of £10,000, then in Cell B2 i enter £8,000 (Order Value) so the value then returned in C2 (Claimable Value) is £0....perfect. If i changed the "Order Value" to £12,000 in B2 then C2 returns £2,000....perfect as i know i can then apply a percentage commision to C2.
Now for my problem, when i move to the next line i can't figure out the cummulative formula to take in to account multiple lines. In my sheet i have a potential for 30 lines, as soon as i get over £10,000 in any line i want the "C" column to show the amount. So stick with the first example above in B2 i enter £8,000 so C2 returns £0, then on the row below in B3 i sell something for £4,000 because B2 & B3 exceed A1 by £2,000 (£10,000) i need the calculation to enter £2,000 in to C3. I could then move to the next line and enter £3,000 into B4, but this time i have already exceed £10,000, the calculation has given me the value in C3 but with this new sale i need to discount that as i am already over my threshold so get paid on the £3,000 in B4 so need this entered in to C4. Reason being each line has the potential to get paid at a different percentage which i enter else where so each distinct row needs to return it's value taking in to account the previous row and the threshold, to say i am stuck is an understatement.
As i said the formula above works perfectly for one row but expanding that to the rest is getting impossible, can you help?
Calculate sales as a running total
It looks like there are a couple of things you need to do here.
First, you should use an absolute reference to A1 in your formulas. In the example you used for C2, it would look like this:
=IF(B2<$A$1,"0",SUM(B2-$A$1))
The $ signs make the reference to A1 absolute. Not matter where you copy this formula within your spreadsheet, this formula will always refer to A1.Then, when you copy the formula in C2 down to C3, the formula in C3 will look like this:
=IF(B3<$A$1,"0",SUM(B3-$A$1))
Notice that the formula still refers to A1. Notice also that I removed the brackets you had around A1 in your example, since they aren't needed, and removing them simplifies the formula.
However, the real problem you've got is that this formula doesn't take account of additional sales in subsequent lines. Therefore, the formula you really need in C2 is one which calculates a running total of your sales, like this:
=IF(SUM($B$2:B2)<$A$1,"0",SUM($B$2:B2)-$A$1))
In both cases, the SUM function here will add up all the values between B2 and B2. This may seem odd, but it makes more sense when you look at this formula after copying and pasting it into C3:
=IF(SUM($B$2:B3)<$A$1,"0",SUM($B$2:B3)-$A$1))
Now the SUM function is calculating the sum of all the values between B2 and B3, i.e. your accumulated sales for those two lines. Therefore, if the cumulative total remains below the threshold value in A1, your commission will be 0. Otherwise, the commissionable amount will be the cumulative total minus the threshold value.
Let me know how you get on with this solution. You can read more about absolute and relative references here, and more about calculating a running total here. Both of these links will open in a new window. If you're still stuck, either post your questions here or contact me directly.
IF MULTIARGUMENTS
=IF(Sheet1!F3<="8:30:00 AM","PRESENT",IF(Sheet1!F3>="8:31:00 AM","LATE",IF(Sheet1!F3>="9:01:00 AM","HALFDAY",IF(Sheet1!F3="","ABSENT"))))
Please help me with this formula the 3rd and 4th arguments does not work
only the first and second argument work
MULTIPLE IF - WITH ADDITION
I have a complex formula and need to consider variables AND addition. Here's my scenario.
- My users will determine the variables by marking 'Y' or 'N' on each of 8 store segments
- My formula must take each of the 8 criteria into consideration and pass that column by (if it's an 'N') or use the field for calculation if it's marked 'Y'
- In some scenarios, ALL 8 store segments could be in selected
Here's my formula, and it's not performing as intended:
=IF($M8="y",$M12*V$9,IF($M8="N",0)+IF($N8="y",$N12*V$9,IF($N8="N",0))
+IF($O8="y",$O12*V$9,IF($O8="N",0)+IF($P8="y",$P12*V$9,IF($P8="N",0)
+IF($Q8="y",$Q12*V$9,IF($Q8="N",0)+IF($R8="y",$R12*V$9,IF($R8="N",0)
+IF($S8="y",$S12*V$9,IF($S8="N",0)+IF($T8="y",$T12*V$9,IF($T8="N",0))))))))
To simplify for your reference, here's two segments of the formula:
=IF($M8="y",$M12*V$9,IF($M8="N",0)+IF($N8="y",$N12*V$9,IF($N8="N",0))
M8 denotes the first store segment
M12 denotes distribution centers n8 denotes the second store segment
V9 denotes the number of pieces per location
My question is this? How can I ADD the IF's together? A simple SUMIF won't work as I need the individual IF statements to function independently, then add the results together. HELP!
Any suggestions?
Pleas assist help me to correct this formula
=IF(E1<5999,150,IF(60007999,300,IF(800011999,400,)))
for the table below
Gross Income (Ksh) Monthly Contribution (Ksh)
Less than 5,999 150
6,000 - 7,999 300
8,000 - 11,999 400
12,000 - 14,999 500
15,000 - 19,999 600
20,000 - 24,999 750
25,000 - 29,999 850
30,000 - 49,999 1,000.00
50,000 - 99,999 1,500.00
Over 100,000 2,000.00