Search form

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:
    Excel sales team commission example
  • 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:
    Complete worked example of calculating commission using nested IF statements
  • 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.

Download worked examples

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.

Add a comment to this lesson

Comments on this lesson

letter coding

I am trying to work a if statment.
It has a few ifs:
1. if there is a number which is the date (but can not count linking 0 as a number) that it reads cell range b5 to b10
2. in cell range b5 to b10 if there is nothing then in cell b11 place an x
3. in cell range b5 to b10 if there is an P then in cell b11 place a p
4. in cell range b5 to b10 if there is an h then in cell b11 place an h.

Help me out from this problem.

Hii, I have attached a excel file with this mail as an example. In that mail I have desktop(PC) data with their specifications (like ram gb, hdd etc.). So I have to calculate total amount after deductions. For eg. if total price of one desktop is Rs 2100 & the ram is missing(Means No is written if it loses) from it , then we will reduce Rs 400 from Rs 2100. Same case if HDD is missing then we will reduce Rs 500 from it & it ges on. After all the deduction we will get final price.

Solvng for 2 x criterias for commission

How would you construct a formula for where there are 2 qualifying criteria for one to attain commission? Say for one to earn a R1000 commission, they had to achieve 200 sales with a minimum average premium of say R50.

Total Sales = Cell E1
Average Premium = Cell D1

If Statement Creation.

Hello, I'm trying to create an IF statement to calculate 65% of the total column on costs over $2000, otherwise showing the original total. I am struggling on how to only capture %65 of the totals >2000.

Создаю копии сайтов от 500 рублей за лендинг

Здесь вы можете заказать копию любого сайта под ключ, недорого и качественно, при этом не тратя свое время на различные программы и фриланс-сервисы.

Клонированию подлежат сайты как на конструкторах, так и на движках:
- Tilda (Тильда)
- Wix (Викс)
- Joomla (Джумла)
- Wordpress (Вордпресс)
- Bitrix (Битрикс)
и т.д.
телефон 8-996-725-20-75 звоните пишите viber watsapp
Копируются не только одностраничные сайты на подобии Landing Page, но и многостраничные. Создается полная копия сайта и настраиваются формы для отправки заявок и сообщений. Кроме того, подключается админка (админ панель), позволяющая редактировать код сайта, изменять текст, загружать изображения и документы.

Здесь вы получите весь комплекс услуг по копированию, разработке и продвижению сайта в Яндексе и Google.

Хотите узнать сколько стоит сделать копию сайта?
напишите нам
8-996-725-20-75 звоните пишите viber watsapp

logical functions

Assist Please.
The cells K3 to K10 of a worksheet contain remarks on students performance such as very good, good, fair and fail depending on the average mark. Write a formula that can be used to count all students who have the remark "very good".

if condition Timevalue

=IF(AND(Z19>=TIMEVALUE("1:00:00"),SUM(AE19:AM19)),IF(AND(Z19>=TIMEVALUE("2:00:00"),SUM(AF19:AN19)),IF(Z19>=TIMEVALUE("3:00:00"),SUM(AG19:AO19))))

I am trying but first two conditions are working and last, I have to add 15 condition in one cell

Calculate marks in Percentage range

please let me know formula for below criteria,
if a student get 85% or above then he/She will get 10 marks.
if a student gets below 70% then he will get zero marks.
and who soever is lying in between 85% to 70% they should get marks accordingly..

Nesting If statements

originally, I was trying to nest about 14 statements and they would not work. i came up with a different way and tried to use 5 statements, but it still would not work. out of curiosity, I tried the same formula one at a time. the first 3 worked, but when i added the fourth it would not. Here is the original formula: =if(A4=single,100,if(A4=single-cart included,130,if(A4=single-cart storage included,130,if(A4=single-pool,99,if(A4=family,150,if(A4=family-junior,125,if(A4=family-cart included,180,if(A4=family-cart storage,180,if(A4=family-senior,100,if(a4=family-senior cart included,115,if(a4=family-cart storage,130,if(a4=family-pool,99,if(a4=corporate,100,if(a4=social,50,ERROR)

thanks for any help,
Jimmy

Nesting IF Statements

oh yeah, i did put them in parenthesis. i just realized the information i put above was not. as i stated earlier, i started doing one at a time. it worked or the first 3 but would not after 3.

Nesting IF Statements

i realized i was using an older version. it was in "compatible mode". it is working now.

multiple ifs and or

Hi, I was wondering how it would work if i had a table that provides 2 types of numbers pending on a criteria.. for instance lets say i had this table

Fruit Country A Country B
0 - 5 $50 $65
> 6 $100 $130

so say in cell A1, says what country this person is in and cell B1 says how many fruits they sold

I was thinking to do =if(and(A1=countryA, B1 <5), $50,if(and(A1=countryA,b1....

I am not sure how to make it so that if the country is B, then to provide the other numbers.....

help

how to count the following ?
100kg and below $3.00
100kg - 400kg is $4.00
400kg and above is $6.00

help

how to count the following ?
100kg and below $3.00
100kg - 400kg is $4.00
400kg and above is $6.00

Lookup or IF AND?

I am debating on which is best Lookup or if and... please help!
= if B2 = "CN" and C27 = "A" then show E20 (£1)
OR if B2 = "CN" and C27 = "B" then show £2
OR if B2 = "CN" and C27 = "C" then show £3
OR if B2 = "CN" and C27 = "D" then show £4
OR if B2 = "PI" and C27 = "A" then show £1.50
OR if B2 = "PI" and C27 = "B" then show £2.50
OR if B2 = "PI" and C27 = "C" then show £3.50
OR if B2 = "PI" and C27 = "D" then show £4.50

IF function

Can you please help me with this
Acquisition cost SUGGESTED MARK UP
P50.01-P100.00 P20+ 30% OF THE EXCESS OF P50 in acquisition cost

Thanks for responding immediately

IF function

Can you please help me with this
Acquisition cost SUGGESTED MARK UP
P50.01-P100.00 P20+ 30% OF THE EXCESS OF P50 in acquisition cost

Thanks for responding immediately

Help...simple condition rule confusion

I'm trying to create a form that calculates 2 different numbers. If i put N in a box, then it calculates 65% of the original number, if I put Y in the box, it calculates 70%
I know this is probably simple, but my brain is getting fried!!!

Help...simple condition rule confusion

I'm trying to create a form that calculates 2 different numbers. If i put N in a box, then it calculates 65% of the original number, if I put Y in the box, it calculates 70%
I know this is probably simple, but my brain is getting fried!!!

Help...simple condition rule confusion

I'm trying to create a form that calculates 2 different numbers. If i put N in a box, then it calculates 65% of the original number, if I put Y in the box, it calculates 70%
I know this is probably simple, but my brain is getting fried!!!

Pages

Add comment