Commission Calculation

**Aquamore** · 01-29-2015, 03:58 PM

Hi,

I was wondering if someone can recommend a clean solution for this problem:

I have a list of sales reps, their sales target and actual results and I need to calculate their commission payout; they first 75% of their target goals is payed at 3%, from 75.01% to 100% at 4% and everything above 100% at 5%
I'd like to have 4 Columns: Sales Rep, Target, Actual and Commission. I can have it solved in a pretty messy way with a few helper columns, but may be excel gurus know of a simple way so I can have a relatively easy formula to put in Column D to take care of that. Thanks a lot!Untitled.png

**npatridge** · 01-29-2015, 04:13 PM

I think this may work for you?

=(IF( B2<C2, (B2*0.75*0.03)+(B2*0.25*0.04)+((C2-B2)*0.05), IF( B2*0.75>C2, C2*0.03, (C2-(B2*0.75))*0.04)+(C2-(B2*0.25))*0.03))

**npatridge** · 01-29-2015, 04:14 PM

Oops, double post..

**pjwhitfield** · 01-29-2015, 04:30 PM

Please Login or Register  to view this content.

**npatridge** · 01-29-2015, 04:32 PM

PJ,

I went to test your results against mine and your formula returns an error (too many arguments). Can you revise?

Commission Calculation

LinkBack

Thread Tools

Rate This Thread

Display