Link to home
Start Free TrialLog in
Avatar of David Spigelman
David SpigelmanFlag for United States of America

asked on

Calculate sales based on commissions with sliding scale

I'm trying to set up a spreadsheet that calculates commissions, using a sliding scale. Frontwards is easy: Take the value of the sales. Use the base commission, and add it to the sliding commission based on a lookup on the bonus, based on the sales value. No problem.

But I'd like to also be able to do it backwards. In other words, I want to know that if I want to make $X commission, then this is how much I need in sales. But it has to factor in that same sliding scale. And I don't know how to manage that. I'm attaching the spreadsheet here.Commissions.xlsx
ASKER CERTIFIED SOLUTION
Avatar of byundt
byundt
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of David Spigelman
David Spigelman
Flag of United States of America image

ASKER

That's exactly what I needed, as well as what I needed to understand! What I was struggling with was that there actually are "impossible" solutions to this, which just have to be worked around. I did change the formatting in F10 back to displaying non-fractions, but adjusted it to do a roundup with 0 places. Reasoning that, in that field, we're talking about the number of sales that need to be made. Either a sale is made or it isn't - there can't really be half-sales. So if the end value is slightly overshot, that will still give you the commission level you wanted.

Anyway, thanks so much for the assist on this!