Calculate commission - IF AND ??

**tc123** · 12-08-2007, 07:33 AM

Hi

My 2nd post

......

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,H25*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

**mudraker** · 12-08-2007, 07:42 AM

tc123

Please read forum rules & post your question in the correct forum - Your thread looks like you are after a formula solution to your problem & should be posted in the Excel Worksheet Functions forum

A thread with the rules is available at the top of each forum or see link below for rules