Search form

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:
    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.

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.

Add a comment to this lesson

Comments on this lesson

function

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

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

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.

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:

Excel sales commission calculation example, sales data

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:

Excel sales commission calculation example, bonus table

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?

Try using a logical test on each criteria

My first thought is you probably don't even need to use the IF function for this scenario. I would try something like this:

=($M12*V$9*($M8="Y"))+($N12*V$9*($N8="Y"))+etc

What you'll find is that ($M8="Y") will evaluate to TRUE if M8 contains "Y" and FALSE if M8 doesn't contain "Y". In Excel, TRUE=1 and FALSE=0. So each segment in the formula will be multiplied by 1 or 0 depending on whether the check cell contains "Y". Those segments that don' t have "Y" will therefore be calculated as 0, and ignored in the final total.

If you want to idiot-proof this a little, you could allow for different inputs in M8 by replacing ($M8="Y") in the formula with the following:

(OR($M8="Y"),($M8="Yes"),($M8="y")))

This will ensure the formula works for a variety of inputs. The OR statement will evaluate each logical test within it separately (you can have as many as you like). If any of the tests are true, then the OR function will return TRUE. If all of them are not true, then the OR statement will return FALSE.

Hopefully this helps - if not, please let me know.

Help

Hi all,
i need to create a formula in excel sheet like:
if there is many type of cell like numeric and alphabet then how i create a multiple IF condition formula in sheet for exaple

if
H then H
if
W then W
if
CL then CL
if
Time 8:15 then P

kindly help

Checking if a cell contains text or numbers

It depends whether you want to test for specific text values, like H, W, CL, or whether you want to have one set of tests if the value is text, otherwise another set of tests if the value is a number (time is stored as a number in Excel).

You can use the ISTEXT() function to check whether a cell contains text. It will return TRUE if the value in the cell is a text value, and false if it is a number. This is a useful function if you have imported some data into a spreadsheet and need to check whether numbers have been imported as numbers or text values.

For example:

=IF(ISTEXT(A1),value_if_true,value_if_false)

The value_if_true and value_if_false would each be another IF statement, depending on what you want.

In terms of testing whether a value contains a certain time, you will need to use the TIMEVALUE() function.

For example, let's assume you've entered a time into cell A2 by typing 8:15 directly into the cell. Excel will recognise this as a time and store it as a number, but format it as a time.

Your IF function would then look like this:

=IF(TIMEVALUE("8:15")=A2,value_if_true, value_if_false)

Note that if you wanted to use 8:15pm, you would need to either enter the time as "20:15" or "8:15 PM".

Hi,

Hi,
Kindly help me to create formula for the below table:
achvmnt target %incentive
<90 0
90-99% 50%
100% 100%
101,102…199% 101,102…199%
200 &> 200% 200%
if the acheivement is < 90% i should get 0 as the incentive % if the acheivement ranges from 90-99% incentive% should automatically show 50%,if it ranges from 101% to199% -incentive % should be 101,102 means 102 like that it should follow if the incentive acheived by employee is more than 200% it should restricted to 200%

calculate income tax

kindly help me to create a formula:
i need to crate a calculator of income tax
if income <=200000 no tax
if income >200000 to 500000 10% tax
if income >500000 to 1000000 20% tax
if income >1000000 then 30% tax
i want it to be created in nested if .

Calculating Income Tax

Hi Tarun

This is a fairly simple scenario which a formula containing nested IF statements will handle easily:

Let's assume that the income amount is in cell A1:

=IF(A1<=200000,0,IF(A1<500000,10%,IF(A1<=1000000,20%,30%)))

This formula will calculate the amount of tax. To calculate the tax amount, you could use this version of the formula instead:

=IF(A1<=200000,0,IF(A1<500000,A1*10%,IF(A1<=1000000,A1*20%,A1*30%)))

I hope this helps!

Regards

David

Calculating income tax - response

Hi - I'm not sure this works. I was hoping that it would as it would solve a problem for me. In the example a value for A1 of 300,000 would return a tax payment of 30,000 (10%) but it should return 10,000. The first 200,000 is tax free so the 10% rate only applies to that portion of the value of A1 that is over the tax free threshold. I am trying (and failing) to structure a commission scheme on the same basis i.e. nothing is paid until a threshold of 100,000 is reached, the next 50,000 earn 10%, the next 50,000 15% - but the first 100,000 do not count so earnings of 200,000 earn 12,500 (50,000 * 10% + 50,000 * 15%). Can you suggest how to do this?

If Function using progressive tax and ranges

Hi I am having problems combining the following if functions into one formula:

=if(B19>=0,C7*15)

=IF(B7>50000,7500+(75000-50000)*0.25)

=IF(B7>75000,13750+(100000-75000)*0.34)

=IF(B7>100000,22250+(335000-100000)*0.39)

=IF(B7>335000,113900+(10000000-335000)*0.34)

=IF(B7>10000000,3400000+(15000000-10000000)*0.35)

=IF(B7>15000000,5150000+(18333333-15000000)*0.38)

For example, if some has income of 100,000. I want to calculate the tax.

So the first range is taxable income over $0, but not over $50,000 must pay 15% on for any amount over $0. Then the next range is taxable income over $50,000 but not over $75,000 must pay $7,500 plus 25% on income over $50,000 and so on and so forth.

Please help me! I have a difficult time combining the formulas. Thank you!!

I TAX

DEAR FRIEND,
THIS MAY HELP YOU.

Calculating incentives

Hi Priyanka

I've just posted a response to another comment directly below this one which looks at a very similar sales commission example. You should be able to use that example as the basis for solving your problem.

I would also recommend you consider using a VLOOKUP function instead. Your incentive table is fairly complicated and (I suspect) subject to change over time. Keeping the table in a separate location on the spreadsheet and referring to it from a VLOOKUP function will make it much easier to change the contents of the incentive table. You can read our VLOOKUP lesson here (opens in a new tab).

Reg