If condition problem

[thara05]

Hi all,

This is my first post in the forum and hope to learn excel from professionals who are in this forum. Just I start to learn MS Excel and have a confuse in IF function.

This is my question, I am trying to write a function and can't seem to figure it out. Could anybody here help me write this formula correctly?

If total >= 15000                then      Total*12%
If total >= 12000                then      Total*11%
If total >= 9000                  then      Total*10%
If total >= 6000                  then      Total*9%
Else                                              Total*8%

I tried something like this but no any luck.

=IF(total>= 15000.(total/100)*12,IF(total>= 12000.(total/100)*11,IF(total>= 9000.(total/100)*10)))

any idea would be greatly appreciated.

Thank you.

 

[jardenp]

=IF(total>=15000,total*.12,IF(total>=12000,total*.11,IF(total>=9000,total*.1,IF(total>=6000,total*.09,total*.08))))

And welcome to the board. This is the best excel board out there! I've been helped countless times. Bookmark it!

 

[Jonmo1]

Welcome to the board.

Try
=A1*(LOOKUP(A1,{0,6000,9000,12000,15000},{8,9,10,11,12})/100)

A1 = Total

 

[shaftandspanner]

Hi thara05,


You were almost there, but there were a few periods used where they should have been commas. I've also completed the formula to include the else condition.


Try this:
=IF(A1>= 15000,A1*12/100,IF(A1>= 12000,A1*11/100,IF(A1>= 9000,A1*10/100,IF(A1>=6000,A1*9/100,A1*8/100))))

Total is in A1

 

[thara05]

thanks for all answer

these two is working for me

=A1*(LOOKUP(A1,{0,6000,9000,12000,15000},{8,9,10,11,12})/100)
=IF(A1>= 15000,A1*12/100,IF(A1>= 12000,A1*11/100,IF(A1>= 9000,A1*10/100,IF(A1>=6000,A1*9/100,A1*8/100))))

can you elaborate the difference between these two?

Thank you

 

[Jonmo1]

You're welcome.

The Nested IFs is doing step by step testing if A1 is >1500, then is A1 >1200 and returning the appropriate result when the IF is TRUE

The Lookup takes the number from A1
Finds the largest number in the first array {0,6000,9000,12000,15000} that is less than or equal to A1
The returns the corresponding number from the 2nd array {8,9,10,11,12}
Then devides that result by 100 (to get a percentage 8/100 = 0.08)
Then multiplies A1 by that result.

 

[thara05]

@Jonmo1 more thanks for your straightforward explanation.

here is another problem.

conditions something like this

If total >= 15000 AND minimum >= 2000                then      total*10%
Else                                                                            total*5%

I tried to write the formula like this but it seems not fine?

=IF(AND(total>=15000,minimum>=2000),total*10, total*5)

can you tell me what is the wrong with this?

Thank you.

 

[Jonmo1]

I don't see anything 'wrong' with it.
Can you tell us what you think is wrong with it?
Does it give an error? What error?
Does it give the wrong result? What result DID it give, what result did you expect?
What are the values of Total and Minimum?

 

[thara05]

Total is in E2 = 12400
Minimum is in H2 = 3000

formula is
=if(and(E2>=15000, H2>=2000), E2*10, E2*5)

 

[Jonmo1]

Why did you change it from And to Not?

Perhaps
=if(not(AND(E2>=15000, H2>=2000)), E2*10, E2*5)