MegaSack DRAW - This year's winner is user - rgwb
We will be in touch
Today's Excel challenge
Right - I need to calculate % commission on sales.
The % varies with contract value, but the calculation is cummulative.
Here are the rates:
Contract Value
From To Rate
£0 - £5,000 4.00%
£5,001 - £10,000 3.50%
£10,001- £15,000 3.00%
£15,001 - £20,000 2.50%
£20,001 - £100,000 2.00%
£100,001 - £500,000 1.50%
Example calculation for a £12,000 contract
0-£5,000 £5,001 - £10,000 £10,001 - 2,000
((£5,000 x 4%) = £200)+((£5,000 x 3.5%) = £175)+((£2,000 x 3.0%) = £60) =£435
How would I put that in a single formula, so I can enter a contract value and return the amount of commission payable.
Can anyone beat Stoner?
[s]vlookup with the 'true' option at the end should work. Sorry, not going to do it all for you ;)[/s]
Must read the question propely 🙂
vlookup with the 'true' option at the end should work. Sorry, not going to do it all for you
Its a bit more involved than that - I think.
A series of something like +if(value>threshold, min (value-lower threshold, threshold-lower threshold) * incremental rate, 0) would work.
eg. like this (first 3 only) if your value is in c2. Obviously you can tart it up with indirection to a table of thresholds and rates to make it maintainable.
=IF($C$2>0,MIN($C$2,5000)*0.04,0)+IF($C$2>5001, MIN($C$2-5000, 5000)*0.035,0)+IF($C$2>10001,MIN($C$2-10000, 5000)*0.03,0)
=IF(G12>100000,((G12-100000)*0.015)+2250,IF(G12>20000,((G12-20000)*0.02)+650,IF(G12>15000,((G12-15000)*0.025)+525,IF(G12>10000,((G12-10000)*0.03)+375,IF(G12>5000,((G12-5000)*0.035)+200,IF(G12>5000,((G12-5000)*0.035)+200,G12*0.04))))))
Where the data is in G12
I'd use cell references for the different fixed values.
I dpon't guarantee this works as is either ❗
I'd use a slide rule and an abacus...
Thanks all (except TSY of course).
MB - yours works a treat 🙂