Calculating Commission

[hattan]

Hi I need help on formula to calculate commission in Excel

Commission Scale
1-50 @$2
51-100 @$4
101- above @$6

For example
if someone sell 161 units he/she gets $666 (50X2)+(50X4)+(61X6)
if someone sell 71 units he/she gets $184 (50X2)+(21X4)

Thank you

Hattan

 

[Marcelo Branco]

Welcome to Mr Excel

Maybe this can help
McGimpsey & Associates : Excel : Using SUMPRODUCT() with variable rates

M.

 

[sheetspread]

This?

Excel 2010
	A	B
1	161	666
2	71	184
Sheet7

Cell Formulas
Range	Formula
B1	=MIN(A1,50)*2 +MEDIAN(0,A1-50,50)*4 +MAX(A1-100,0)*6

 

[TinaP]

I never would have dreamt of using median for a formula like this. Genius!

 

[Dave Patton]

try either of

=((A2>0)*A2+(A2>50)*(A2-50)+(A2>100)*(A2-100))*2

or

=SUMPRODUCT(--(A2>E2:E4),A2-E2:E4,G2:G4)

E2:E4 has the brackets 0, 50.001, 100.001
G2:G4 has the rate differential

 

[hattan]

Thank you !

Welcome to Mr Excel

Maybe this can help
McGimpsey & Associates : Excel : Using SUMPRODUCT() with variable rates

M.

 

[hattan]

Thank you vey much for this! i just tried it and it works as what i wanted

This?

Excel 2010

	A	B
1	161	666
2	71	184

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet7​

Worksheet Formulas Cell Formula B1 =MIN(A1,50)*2 +MEDIAN(0,A1-50,50)*4 +MAX(A1-100,0)*6 <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[hattan]

Thank you !

try either of

=((A2>0)*A2+(A2>50)*(A2-50)+(A2>100)*(A2-100))*2

or

=SUMPRODUCT(--(A2>E2:E4),A2-E2:E4,G2:G4)

E2:E4 has the brackets 0, 50.001, 100.001
G2:G4 has the rate differential

 

[Marcelo Branco]

Thank you !

You are welcome. Thanks for the feedback.

M.