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

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]161[/TD]
[TD="align: right"]666[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]71[/TD]
[TD="align: right"]184[/TD]

</tbody>

Sheet7​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B1[/TH]
[TD="align: left"]=MIN(A1,50)*2 +MEDIAN(0,A1-50,50)*4 +MAX(A1-100,0)*6[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[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.