IF functions in Excel

[stephenheppell]

Ok, im not yet that used to Excel, however i want to create a
function, that corresponds with a cell that has a cummilative total.
So what i want is that if the total in this cell is less than 4000 i
get the result of 40, if it is more than 4000 but less than 20000 i
get a result of 25, and if it is more than 20000 it equals 20

could someone perhaps show me the right formula for this. Thanks

 

[JE McGimpsey]

Ok, im not yet that used to Excel, however i want to create a
function, that corresponds with a cell that has a cummilative total.
So what i want is that if the total in this cell is less than 4000 i
get the result of 40, if it is more than 4000 but less than 20000 i
get a result of 25, and if it is more than 20000 it equals 20

could someone perhaps show me the right formula for this. Thanks

One way:

=LOOKUP(A1,{0,40;4000.1,25;20000.1,20})

another:

=IF(A1<=4000, 40,IF(A1<=20000, 25, 20))

another:

=40-15*(A1>4000)-5*(A1>20000)

 

[stephenheppell]

Many thanks that works a treat. Could you explain how the lookup
function is working? Thats the one ive used and its great, spot on but
not quite sure how it works thanks

 

[Geoff Lilley]

Many thanks that works a treat. Could you explain how the lookup
function is working? Thats the one ive used and its great, spot on but
not quite sure how it works thanks

- Show quoted text -

This might help clarify:
http://support.microsoft.com/kb/324986

In JE's instance, the "array" syntax is being used.

HTH
Cheers
Geoff