Hi All-
I'm looking for help on efficiently calculating sales reps' commissions for deals up to reaching their budget (@ 6% rate) and over budget (@ 8% rate)
Ideally, I want a formula in the monthly "Total Deal € Value" field/column for "Up to 100%" and "Over 100%", that that shows how much is allocated in each.
Obviously, until a rep reaches their target, everything will always be calculated @ 6%. Then, in the month where they reach target, there#s likely to be a portion @ 6% and another porting @ 8%. The months after tha, everything will be paid @ 8%.
I'd like to keep things as clean as possible in that sheet. If reference tables are necessary, I'd like them on a different sheet. There will be a different sheet for each sales rep with their name in cell A2, which can be used for lookup, etc.
Hope this makes sense!
P.S.: I tried to attach a workbook with data in sheet 1 and the calculations I'm looking for in sheet 2, but couldn't, so there's just a picture for now.
I'm looking for help on efficiently calculating sales reps' commissions for deals up to reaching their budget (@ 6% rate) and over budget (@ 8% rate)
Ideally, I want a formula in the monthly "Total Deal € Value" field/column for "Up to 100%" and "Over 100%", that that shows how much is allocated in each.
Obviously, until a rep reaches their target, everything will always be calculated @ 6%. Then, in the month where they reach target, there#s likely to be a portion @ 6% and another porting @ 8%. The months after tha, everything will be paid @ 8%.
I'd like to keep things as clean as possible in that sheet. If reference tables are necessary, I'd like them on a different sheet. There will be a different sheet for each sales rep with their name in cell A2, which can be used for lookup, etc.
Hope this makes sense!
P.S.: I tried to attach a workbook with data in sheet 1 and the calculations I'm looking for in sheet 2, but couldn't, so there's just a picture for now.
