Sales commission or bonus

[Richard Robertson]

Greetings,

Please i need help to do a formula to compute the bonus in the sheet attached.

Thank you.

 

[Hui]

Richard

Firstly, Welcome to the Chandoo.org Forums

I don't think your example is correct
A value in J of 62% is less than the value in Cell Q5 and hence there should be no bonus ?

 

[Hui]

I would make a few changes
1. Change values in Amount% and Sales % to be Values not ranges
2. Delete the blank Column P
3. In L2: =IFERROR(INDEX($P$6:$S$9,MATCH(100*F4,$O$6:$O$9,1),MATCH(100*J4,$P$5:$S$5,1)),0) copy down



see attached file:

 

[Richard Robertson]

I would make a few changes
1. Change values in Amount% and Sales % to be Values not ranges
2. Delete the blank Column P
3. In L2: =IFERROR(INDEX($P$6:$S$9,MATCH(100*F4,$O$6:$O$9,1),MATCH(100*J4,$P$5:$S$5,1)),0) copy down

View attachment 31348

see attached file:

Thank you very much Sir.

 

[Richard Robertson]

Dear Sir,

Please i really appreciate the solution you gave me but am not ok with it. So i have sent it again, kindly look at it for me please.

 

[NARAYANK991]

Hi ,

The formula needs to be changed to :

=IFERROR(INDEX($P$6:$S$9,MATCH(100*J4,$O$6:$O$9,1),MATCH(100*F4,$P$5:$S$5,1)),0)

Narayan

 

[Richard Robertson]

Hi ,

The formula needs to be changed to :

=IFERROR(INDEX($P$6:$S$9,MATCH(100*J4,$O$6:$O$9,1),MATCH(100*F4,$P$5:$S$5,1)),0)

Narayan

now it has worked... God richly bless you people.