solution:Calculate commission - IF AND ??

[tc123]

Hi

I'm trying to calculate a sales commission besed on the number of meters of an item sold. The commission rate changes as the number of meters increases ie.

The sheet calculates the number of meters sold & the total cost of those meters but the commission is based on the meters not the value of the sale if that makes any sense.

ie. if between 5 & 10m sold commission would be total value of the job *10%
between 10 & 20 value of the job *12% & so on.

This is the formula I have at the moment which doesn't quite work.

=H25*IF(AND(K26>=5,K26<=14),H23*0.23,IF(K26<=29,H2 5*0.22,IF(K26<=59,H25*0.21,IF(K26<=104,H25*0.2,IF( K26<=210,H25*0.19,IF(K26<=2000,H25*0.18))))))

H25 is where i'd like the result. H23 contains the job value. K26 contains the total meters.

So ideally K26 = 10 would mean H25 would show 23% of H23.
Any ideas ???

Many thanks

Terry

[tc123]

Ok I think i've solved it and here's the solution :

=IF(AND(K26>=5,H23<=14),H23*0.24,IF(K26<=29,H23*0.23,IF(K26<=59,H23*0.22,IF(K26<=104,H23*0.21,IF(K26<=210,H23*0.2,IF(K26<=2000,H23*0.19))))))

I took out the H23 at the start.

Thanks

[daddylonglegs]

I presume the number of meters can't be under 5? If it is then your formula will apply 23% commission

Normally the best way to do such calculations wuld be to use a table showing the lower bound of each commission range and the corresponding commission. This allows the ranges and/or rates to be easily altered without changing formulas. It's also much easier to maintain and debug.

Using that approach you could have a table like this, e.g. in A1:B8

Meters Commission
0 0%
5 24%
15 23%
30 22%
60 21%
105 20%
211 19%

Then the formula becomes simply

=LOOKUP(K26,A2:B8)*H23