IF Formula trouble

Highlighted
Occasional Visitor

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.

0 Replies