Solved problem with nested if.

October 13, 2011 at 04:01:06
Specs: Windows 7, 4gb
i have tried forever to solve this.
I'm writing a bill. and i have to put in two different percentages. if they go over 2000 they get 5% off if they go over 4000 they get 15% off.
i want that in one line in exel.

this is my formula so far: =IF(AND(E10>2000<3999);E10*0,05;)*IF(AND(E10>4000);E10*0,15;0)


i cant get them to work together.
any bright people out there that can halp?


See More: problem with nested if.

Report •


✔ Best Answer
October 15, 2011 at 07:09:35
Let's start with the comma vs. semi-colon issue.

Based on this website, I believe this to be a Windows 7 setting, specifically the "List Separator" character.

http://www.treeplan.com/formula-you...

Since I am running a US English based version of Windows XP with Excel 2010, I need to use commas.

Regardless of whether a comma or semicolon is used, the syntax for a Nested IF is the same. Since I can't test anything with a semi-colon, I'll leave it to you to make those changes.

That said, let's look at the formula that you posted in your latest response:

=IF(E10>4000; E10*0.15; 0 IF(E10>2000; E10*0.05; 0))

Why do you have a 0 before the second IF clause? That is sure to cause an error.

Now let's look at mine:

=IF(E10>4000, E10*0.15, IF(E10>2000, E10*0.05, 0))

Follow the logic of my formula as it relates to the logical flow of an IF statement:

IF(logical_test, [value_if_true], [value_if_false])

Logical Test for 1st IF:

E10>4000

value_if_true for 1st IF:

E10*0.15

value_if_false for 1st IF:

IF(E10>2000, E10*0.05, 0)

Logical Test for 2nd IF:

E10>2000

value_if_true for 2nd IF:

E10*0.05

value_if_false for 2nd IF:

0

Here is a table of values returned using my formula:

E10:   2000    0           Not greater than 2000, so no discount
E10:   2001    100.05      Greater than 2000: 2001*.05 = 100.05
E10:   4000    200         Not greater than 4000, 4000*.05 = 200
E10:   4001    600.15      Greater than 4000: 4001*.15 = 600.15

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
October 13, 2011 at 09:31:44
the Excel syntax for AND is as follows:

AND(E10>2000,E10<3999)

All conditions that you are ANDing have to be standalone entities, separated by a comma.

re: AND(E10>4000)

There is no need for an AND here since you are only checking one condition

Untested:

=IF(AND(E10>2000,E10<3999),E10*0.05,IF(E10>4000,E10*0.15,0))

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
October 13, 2011 at 14:27:23
Nice DerbyD!

But don't forget to include 3,999.01 - 4,000.

=IF(AND(E10>=2000,E10<4000),E10*0.05,IF(E10>=4000,E10*0.15,0))


Report •

#3
October 13, 2011 at 15:35:29
Actually, the best solution is probably:

=IF(E10>4000, E10*0.15, IF(E10>2000, E10*0.05, 0))

I used the AND mainly to show the correct syntax. The actual values to be tested would be up to the OP based on his actual data.

Besides, he said over 2000 and over 4000, so >= would be incorrect since that would include 2000 and 4000.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
October 15, 2011 at 04:34:52
for some reason it does not work. i will try to explain better.

everything is about this cell(E10). =SUM(E6:E9)

based on that cell, if it passes 2000NOK= 5%, passes 4000NOK= 15%(the discount they get)

i have exel 2010. i get a message that i need to use the ; instead of , to go to the true/false.

ex. =IF(E10>4000; E10*0.15; 0 IF(E10>2000; E10*0.05; 0))

the easiest solution would be making two cells 5% and 15%. but the extra task is to make it in one formula. i have tried alot, but cant figure it out. how do you nest them together. what do you wright in betwen to make them work together. i tried too read about all sorts of if commands and combinations.
this is the final test in exel. its just a basic level exel but for us who wanted we could take it further.


Report •

#5
October 15, 2011 at 07:09:35
✔ Best Answer
Let's start with the comma vs. semi-colon issue.

Based on this website, I believe this to be a Windows 7 setting, specifically the "List Separator" character.

http://www.treeplan.com/formula-you...

Since I am running a US English based version of Windows XP with Excel 2010, I need to use commas.

Regardless of whether a comma or semicolon is used, the syntax for a Nested IF is the same. Since I can't test anything with a semi-colon, I'll leave it to you to make those changes.

That said, let's look at the formula that you posted in your latest response:

=IF(E10>4000; E10*0.15; 0 IF(E10>2000; E10*0.05; 0))

Why do you have a 0 before the second IF clause? That is sure to cause an error.

Now let's look at mine:

=IF(E10>4000, E10*0.15, IF(E10>2000, E10*0.05, 0))

Follow the logic of my formula as it relates to the logical flow of an IF statement:

IF(logical_test, [value_if_true], [value_if_false])

Logical Test for 1st IF:

E10>4000

value_if_true for 1st IF:

E10*0.15

value_if_false for 1st IF:

IF(E10>2000, E10*0.05, 0)

Logical Test for 2nd IF:

E10>2000

value_if_true for 2nd IF:

E10*0.05

value_if_false for 2nd IF:

0

Here is a table of values returned using my formula:

E10:   2000    0           Not greater than 2000, so no discount
E10:   2001    100.05      Greater than 2000: 2001*.05 = 100.05
E10:   4000    200         Not greater than 4000, 4000*.05 = 200
E10:   4001    600.15      Greater than 4000: 4001*.15 = 600.15

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
October 16, 2011 at 06:34:02
Thanx alot for the help derbidad03.
i had to try back and forth and ended up with this formula.
=IF(E10>4000;E10*15%;0+IF(E10>2000;E10*5%;0))

i got a error on 0,15 and 0,05. also i could not connect them. but after reading your input and trying more, i figure it out. because when i added another formula by using the function tab on the left side of the command line. it added a + sign in front of second if. thats when i understod how to connect them.
exel is actually pretty fun when dealing with nesting and formulas =)


Report •

Ask Question