Conditional formatting for commission slabs

[Aisha890]

Hi guys

i have to choose commission rate against the values, if upto 4.99 then it wilb be 0.03, if in 5- 99.9, then the commission will be charged 0.05 and so on. i have successfully computed the values for 1 and 3rd slab ,Howver for the second i get a N/A value. Need help. have stated the formula used below

Commission Slab
Commission Roll over
Upto 4.99 0.03 0.01
5.00-99.99 0.05 0.01
100- above 0.10 0.02

Please Login or Register  to view this content.

Regards
Aisha

[Special-K]

=lookup(c11,{-1e99,4.99,5.00,100},{0.03,0.03,0.05,0.10})

[Special-K]

=LOOKUP(C11,{-1E99,4.99,5.00,100},{0.03,0.03,0.05,0.10})
or

=IF(C11>=100,B6,IF(C11>=5,B5,B4))

in descending order of values (100,5,4.99)

NOTE: In your original formula remove the AND() as your're only supplying one condition.

AND normally usues more than one condition, e.g.

A1=13
B1=11

=IF(AND(A1=13,B1=11),TRUE,FALSE)

Note, more than one condition in the AND(), both of them need to be correct for the result to be TRUE.