+ Reply to Thread
Results 1 to 10 of 10

Trying to determine values with multiple ranges and variable payout levels

  1. #1
    Registered User
    Join Date
    01-06-2012
    Location
    MN
    MS-Off Ver
    Excel 2013
    Posts
    62

    Trying to determine values with multiple ranges and variable payout levels

    I am trying to build a tool that will help us evaluate various scenarios. Customers can accrue points and we want to change the amount of $ we award them for their various point ranges (think airline programs).

    We ere evaluating three types of programs (A, B and C). The names of the programs could be changed to numbers if it would be easier. We have a number of point ranges as shown in columns H:I

    We would like to be able to change the annual points earned in cell C2 and see the amount the customer would earn in cells D4:F4 is the program was not retroactive (in other words, the $ they earn stay static and doesn’t change once they hit higher levels ) and in cells D5:F5 if retroactive where all points they earn would pay out at the highest level earned.

    If anybody could give me some help it would be greatly appreciated. I’ve used a few IF statements but this one is above me. Thanks!
    Attached Files Attached Files
    Last edited by AliGW; 04-18-2019 at 02:16 PM.

  2. #2
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.92 (24120731))
    Posts
    9,106

    Re: Need help solving for various scenarios

    you cn use
    =VLOOKUP($C4,$H$4:$L$13,3,TRUE)

    But can you explain the retroactive again , as i have just used the same formula in row 4 and 5
    if you want that to NOT update - gain, then my need to use VBA
    OR copy and paste special > value

    Also you mention C2 ???
    dont see that entry in the example
    Attached Files Attached Files
    Wayne
    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2503 (Windows 11 Home 24H2 64-bit)
    Posts
    90,480

    Re: Need help solving for various scenarios

    Administrative Note:

    We would very much like to help you with your query, however the thread title does not really convey what your request is about.

    Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

    Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

    (Note: this change is not optional. No further help to be offered until this moderation request has been fulfilled.)
    Last edited by AliGW; 04-18-2019 at 04:52 AM.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    NB:
    as a Moderator, I never accept friendship requests.
    Forum Rules (updated August 2023): please read them here.

  4. #4
    Registered User
    Join Date
    01-06-2012
    Location
    MN
    MS-Off Ver
    Excel 2013
    Posts
    62

    Re: Need help solving for various scenarios

    Thank you and I am sorry for the improper thread title. The rule makes sense to me now. I have already had one contributor, should I try to work with them (etaf), or, rename the thread and start again? Lastly, I am not exactly sure which functions I should list in the title as I suspect there are multiple solutions.

  5. #5
    Registered User
    Join Date
    01-06-2012
    Location
    MN
    MS-Off Ver
    Excel 2013
    Posts
    62

    Re: Need help solving for various scenarios

    etaf - thanks for your help and we are making good progress. I made a few updates to the test spreadsheet to help explain what I am trying to accomplish. With retroactive points, all points earned are worth the highest point value per the point ranges. With the 'not retroactive,' points earned stay at the value of the range when they were earned. In other words, if a customer earned 2,250 points they would have a total dollars earned of $712.25 in cell E8 (pulling values from the B category broke out as follows; 999 points at $0.25/point, 1,0000 points at $0.35/point and 250 points paying out at the $0.45 level. The same customer shipping 2,250 points from column C would show a total dollars earned of $937.15 broke out as follows 999 points at $0.35, 1,000 points at $0.45 and 250 points at $0.55/point.

    Is there a formula I can add to Cell E8 and F8 to automatically calculate this if we play 'what if's' by changing the annual points in cell C4?


    I should not have made reference to cell C2, that was my error - sorry.

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2503 (Windows 11 Home 24H2 64-bit)
    Posts
    90,480

    Re: Need help solving for various scenarios

    You don’t need to start again, but you may not proceed with this thread until you have amended the title as requested. Instructions about how to do this are in my previous post. Please do it now.

    Do not list functions in your title - just say what you are trying to achieve.

  7. #7
    Registered User
    Join Date
    01-06-2012
    Location
    MN
    MS-Off Ver
    Excel 2013
    Posts
    62

    Trying to determine values with multiple ranges and variable payout levels

    Updating thread title

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2503 (Windows 11 Home 24H2 64-bit)
    Posts
    90,480

    Re: Need help solving for various scenarios

    You’ve updated the wrong post. I will amend the opening post for you.

  9. #9
    Registered User
    Join Date
    01-06-2012
    Location
    MN
    MS-Off Ver
    Excel 2013
    Posts
    62

    Re: Need help solving for various scenarios

    Thanks you - sorry to cause the issue in the first place, I didn't mean to circumvent the rules

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2503 (Windows 11 Home 24H2 64-bit)
    Posts
    90,480

    Re: Trying to determine values with multiple ranges and variable payout levels

    No problem - I hope you get a resolution soon.

+ 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. [SOLVED] Using IF for three scenarios
    By mrsjayok in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-02-2018, 12:28 AM
  2. [SOLVED] IF with 3 scenarios?
    By Catsonheat in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-11-2018, 12:10 PM
  3. [SOLVED] Help with scenarios
    By Tmax95 in forum Excel General
    Replies: 1
    Last Post: 06-27-2014, 12:20 PM
  4. Replies: 6
    Last Post: 12-09-2007, 11:09 AM
  5. Scenarios
    By Peo Sjoblom in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-06-2005, 04:05 AM
  6. Scenarios
    By JosefS in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 03:05 AM
  7. Scenarios
    By JosefS in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 10:05 PM

Tags for this Thread

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