IF statement formula continues to effect previous results

Hi All!

Is there a way of stopping an IF statement formula from effecting previous results?

Linked to this solved thread: https://www.excelforum.com/excel-gen...ents-help.html

As described in the thread, the values in column K are calculated using different percentages depending on the value under "Total Licence Quota" (e.g. IF Total Licence Quota is under 90,000, then multiply by 4%, IF over 90,000 then multiply by 5% etc.)

The problem I have now is that the previous values are being affected, e.g. the commission value earnt in January is affected in latter months when the Total Licence Quota moves into the next 'tier' e.g. goes from under 90K to over 90K, to over 180K, to over 270K.

Does anybody have any ideas of how to prevent this at all?

Appreciate your help guys!

Hi Ali - thanks for this.

Apologies, I probably haven't explained this well. The calculation for the Commission Value differs depending on what the Total Licence Quota is. i.e. the formula uses higher percentages incrementally when the Total Licence Quota value hits £90K, then £180K, then £270K. The problem I am struggling with, is that the values under Commission Value are of course changing as the Total Licence Quota moves through those tiers.

So what I am tryig to avoid, is when Opportunity One returns a Commission Value for £3,600, I don't want further opportunities to effect this value - I'd like to "freeze" that value if that makes sense?

Appreciate your help Ali!

Hi All

I've been away with work for a few weeks and have been trying to figure this out but with no luck! Can anybody help me please?

I'm trying to work out commission values for each sales opportunity on this sheet.

The COMMISSION VALUE column is based on the REVENUE TYPE, the QUOTA VALUE and TOTAL LICENCE QUOTA.

As an example; the COMMISSION VALUE takes the QUOTA VALUE (£90,000) and if REVENUE TYPE = Licence, it multiplies by a certain percentage depending on the TOTAL LICENCE QUOTA value.

So the more the "Licence" sales the consultant accumulates, the higher the commission percentage:

If the TOTAL LICENCE QUOTA < £90,000 it multiplies by 4% commission
If the TOTAL LICENCE QUOTA < £180,000 it multiplies by 5% commission
If the TOTAL LICENCE QUOTA < £270,000 it multiplies by 6% commission
If the TOTAL LICENCE QUOTA > £270,000 it multiplies by 12% commission

The problem I have is that the COMMISSION VALUE in row 9 (which calculates to £3,600) needs to NOT be affected by future calculations (sales opportunities), but it is currently, as the TOTAL LICENCE QUOTA value increases.

(if you copy and paste the REVENUE OPPORTUNITY in H9 to H10 etc. you will see the TOTAL LICENCE QUOTA increases and therefore the COMMISSION VALUE in cell K9 also increases)

Is there a way of stopping the COMMISSION VALUE in row 9 (K9) from being affected once the original calculation has been done?

Can I add something to the end of the formula in K9 to ignore any further increases in the TOTAL LICENCE QUOTA?

This would be amazing if I can get this sorted! Really appreciate any help

Your Total License Quota in M9 will increase as you add more sales. Perhaps it would be better to call this column Cumulative License Quota and use this formula in M9:

=J9

and this one in M10:

=J10+M9

which can be copied down. Then in working out your commission, you can compare the individual sales with the cumulative sales to date at that point (i.e. against each corresponding value in column M).

Hope this helps.

Pete

Pete_UK - Many thanks for your advice and apologies for the delay in response!

You were quite right - I was trying to be too clever and trying to do too much in one formula!

I have it working perfectly (and with improvements over the last iteration) thanks to your wisdom!

Much appreciated