# Thread: Calculating Commission Thanks: 0 Likes:  1 Post #4386645 (1)

1. ## Calculating Commission

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  Reply With Quote

2. ## Re: Calculating Commission  Reply With Quote

3. ## Re: Calculating Commission

This?

Excel 2010
AB
1161666
271184

Sheet7

Worksheet Formulas
CellFormula
B1=MIN(A1,50)*2 +MEDIAN(0,A1-50,50)*4 +MAX(A1-100,0)*6  Reply With Quote

4. ## Re: Calculating Commission

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

5. ## Re: Calculating Commission

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  Reply With Quote

6. ## Re: Calculating Commission

Thank you ! Originally Posted by Marcelo Branco   Reply With Quote

7. ## Re: Calculating Commission

Thank you vey much for this! i just tried it and it works as what i wanted Originally Posted by sheetspread This?

Excel 2010
A B
1 161 666
2 71 184
Sheet7

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

8. ## Re: Calculating Commission

Thank you ! Originally Posted by 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  Reply With Quote

9. ## Re: Calculating Commission Originally Posted by hattan Thank you !
You are welcome. Thanks for the feedback.

M.  Reply With Quote

## User Tag List

commission calculation, excel, excel formula, excel formula commission 