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?

✔ 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;

0IF(E10>2000; E10*0.05; 0))Why do you have a

0before 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.

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.

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))

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

over2000 andover4000, so>=would be incorrect since that wouldinclude2000 and 4000.

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

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.

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;

0IF(E10>2000; E10*0.05; 0))Why do you have a

0before 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.

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 =)

Ask Your Question

Weekly Poll

Do you think manufacturers should do more to reduce phone and tablet usage among kids?

Discuss in The Lounge

Poll History