Calculating the difference over a threshold

[kmlloyd]

Edited:

I've created a new sheet for myself that I think is much more simple.

I've put an explanation in the file, but here goes.

I need Column R to calculate the current month's commission.

The "draw" is a loan to the salesperson, so we hold back all commissions until the loan is paid back.

SO....until the "YTD Earned Incentive" (column P) is equal to the Annual Draw (Q) R should be 0.

There is one complication I'm really struggling with:

1. The month where the earned incentive takes the YTD incentive over the draw amount is confusing. If the earned incentive is $5000 but they still owe $3000, their current incentive payment will be $2000. I don't know how to show that.

Thanks in advance. I hope I've explained it better.

[Richard Buttrey]

Hi,

I think you need to explain how you've calculated your manual results. It's not immediately obvious - to me at least.

Rgds

[kmlloyd]

Ok here goes:

Draw Top Up (D) = Draw Payable - Actual Commission Earned

Total to Pay (F) = Draw Payable OR IF the total of Draw Payback is >= the Total Draw Payable (B14), THEN Actual Commission Earned

Draw Pay Back (E) = Actual Commission Earned - Draw Payable up until the total Draw Payable reaches maximum of the Draw Payable (B14)

I hope this makes sense...I'll try to go through it again and clarify more if needed.

[Richard Buttrey]

Hi,

Total to pay on row 5 does not seem to agree with your definition.

The Total Draw Payback of 5000 is < B14 (24000) hence the OR condition is not imposed and F5 should be the 2000 Draw payable in B5, i.e. 2000 not 3000.
Or am I misunderstanding this?

Rgds

[kmlloyd]

Hi,

Total to pay on row 5 does not seem to agree with your definition.

The Total Draw Payback of 5000 is < B14 (24000) hence the OR condition is not imposed and F5 should be the 2000 Draw payable in B5, i.e. 2000 not 3000.
Or am I misunderstanding this?

Rgds

No it's just really confusing! I'm going to try to re-word this and maybe play with my example a bit so it makes more sense.

[kmlloyd]

bumping as I've edited my first post as well as the attachment. Thanks!!!

[kmlloyd]

bumping again...>I hope my explanation is clear. I tried to clean it up a bit.

[kmlloyd]

I've updated my sample and clarified my explanation again....really hoping someone can help me out! I've been staring at this thing for so long - there has to be a way to do it!