If formula

[koba]

Hi All

Can you please help me with an if formula

if A1 is between 0-500 then display A1*.15, otherwise if A1 is betwee
501-1000 then display A1*.10, otherwise if A1 is greater then 1000 the
display A1*.05.

I know its simple but i just cant work it out! It is the betwee
501-1000 that is giving me trouble.


Your help is much appreciated.

Thanks
Andre

 

[Biff]

Hi!

Try one of these:

=IF(ISNUMBER(A1),A1*LOOKUP(A1,{0;1;501;1001},{0;0.15;0.1;0.05}),"")

=IF(ISNUMBER(A1),IF(A1>1000,A1*0.05,IF(A1>500,A1*0.1,A1*0.15)),"")

Biff

 

[Bryan Hessey]

=IF(A1<501,A1*0.15,IF(A1<1001,A1*0.1,A1*0.05))

or

=IF(A1<1,"",if(A1<501,A1*0.15,IF(A1<1001,A1*0.1,A1*0.05)))

to ignore zero or negatives.

--