+ Reply to Thread
Results 1 to 6 of 6

IF statement formula continues to effect previous results

  1. #1
    Registered User
    Join Date
    03-31-2017
    Location
    UK
    MS-Off Ver
    MSO 365
    Posts
    12

    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!
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    41,980

    Re: IF statement formula continues to effect previous results

    If the total licence quota increases on particular dates, then you could perhaps have a lookup table that keeps getting added to, and then tweak your formula to include a date criterion.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Registered User
    Join Date
    03-31-2017
    Location
    UK
    MS-Off Ver
    MSO 365
    Posts
    12

    Re: IF statement formula continues to effect previous results

    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!
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    03-31-2017
    Location
    UK
    MS-Off Ver
    MSO 365
    Posts
    12

    Re: IF statement formula continues to effect previous results - Please Help!

    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
    Attached Files Attached Files

  5. #5
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    20,007

    Re: IF statement formula continues to effect previous results - Please 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

  6. #6
    Registered User
    Join Date
    03-31-2017
    Location
    UK
    MS-Off Ver
    MSO 365
    Posts
    12

    Re: IF statement formula continues to effect previous results

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 3
    Last Post: 01-22-2015, 12:59 PM
  2. Replies: 4
    Last Post: 07-29-2013, 06:42 AM
  3. [SOLVED] X-Axis continues to count blank cells with =IF formula in
    By Macnautor in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 08-22-2012, 11:18 AM
  4. Vlookup skipping previous results
    By J_Le4 in forum Excel General
    Replies: 1
    Last Post: 11-22-2011, 10:34 PM
  5. effect:IF statement
    By adhamija in forum Excel General
    Replies: 12
    Last Post: 05-01-2011, 11:57 AM
  6. Replies: 5
    Last Post: 09-03-2010, 05:05 PM
  7. Formula continues to calculate in the background
    By mofrader in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 08-30-2010, 01:08 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1