I'm almost embarrassed to ask this, but having a heck of a time finding an answer.
I'm tracking Gross Commissions for salespeople at my company, and when they reach a specific target their commission split changes to a higher split.
This is the formula I'm using:
=IF($D$1<$F$3,(E6-F6)*$B$3,(E6-F6)*$D$3)
$B$3 is 80%.
$D$3 is 85%
The problem I'm having: Once the commission target is reached and the split goes from $B$3 to $D$3 all of the previous commissions that were calculated at $B$3 are recalculated at $D$3.
Is there a way to lock the formula at $B$3 once the value has been calculated?
I've been working on this for a week and I can't afford to pull out more hair.
