+ Reply to Thread
Results 1 to 7 of 7

IF or PRODUCT or LOOKUP or RANGE or What to do?

  1. #1
    Registered User
    Join Date
    12-14-2009
    Location
    Los Angeles, California
    MS-Off Ver
    Excel 2007
    Posts
    5

    IF or PRODUCT or LOOKUP or RANGE or What to do?

    I have a running total from E2:E52. I'll input small numbers but they start to add up. I have two tiers or scenarios or arguments that I'm trying to do.

    Everything in the RANGE of $15,000 to $19,999.99 is multiplied by 2%.

    IF it starts totalling up $20,000 or more I want it multiplied by 3%.

    I figured out so far I can't do this with just one formula/cell. So whatever I have in E53 or in my Total Cell; will have to be with two different arguments or in seperate cells.

    I've searched the forums with those key words but I'm realizing I have a case by case function to figure out.

    If I don't make myself clear, my apologies and if there is a similar post as mine here; I'll look at it. Just let me know.

    Thanks in advance.

  2. #2
    Forum Expert darkyam's Avatar
    Join Date
    03-05-2008
    Location
    Houston, TX
    MS-Off Ver
    2013
    Posts
    2,191

    Re: IF or PRODUCT or LOOKUP or RANGE or What to do?

    OK, so say your total is $30,000. You want the formula to essentially multiply $4,999.99*.02 and then add $10,000*.03, correct?

    If yes, then =MAX(0,E53-15000)*0.02+MAX(0,E53-20000)*0.01 should work.

  3. #3
    Registered User
    Join Date
    12-14-2009
    Location
    Los Angeles, California
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: IF or PRODUCT or LOOKUP or RANGE or What to do?

    it's my fault for not giving an example. sorry.

    so say;

    I'm running a total and it's summing up down there.

    500
    750
    450
    390
    1000
    250
    620
    and so on.

    And when finally I get a total it crosses a threshold of 15,000.00 which then matters. Anything that keeps adding up in the total of between 15,0000 to 19,999.99 will be multiplied by 2%.

    Once this total hits $20,000.00 or more then it is now multiplied by 3%.

    (At this point the 2% is now redundant.)

    I hope I'm coming accross.

    Many thanks again.

  4. #4
    Forum Expert darkyam's Avatar
    Join Date
    03-05-2008
    Location
    Houston, TX
    MS-Off Ver
    2013
    Posts
    2,191

    Re: IF or PRODUCT or LOOKUP or RANGE or What to do?

    So the whole number is multiplied then? In that case, use =E53*Lookup(E53,{0;15000;20000},{0;.02;.03}). If neither this nor the previous formula are what you are after, could you please post the expected result when the total is $30,000, just so we have a figure to match in our formulas?

  5. #5
    Registered User
    Join Date
    12-14-2009
    Location
    Los Angeles, California
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: IF or PRODUCT or LOOKUP or RANGE or What to do?

    yeah so 30000 is just an example.

    It could be any number that is multiplied by 2% if it falls between 15,000.00 and 19,999.99.

    It could be any number that is multiplied by 3% if it is 20,000.00 or more.

    so say, yeah; it totalled up to 30,000.00 then it's multiplied by 3% and the answer would be 900.00

    But in a slow month if it's 17,000.00 then it matters to multiply by 2% and the answer would then be 340.00.

    Talking about dollars so cents are involved.

    Thanks, you are so godly.
    Last edited by GOLDENBARREL; 12-15-2009 at 01:16 AM.

  6. #6
    Registered User
    Join Date
    12-14-2009
    Location
    Los Angeles, California
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: IF or PRODUCT or LOOKUP or RANGE or What to do?

    BTW

    I'm going to try the formula.

    thx

  7. #7
    Registered User
    Join Date
    12-14-2009
    Location
    Los Angeles, California
    MS-Off Ver
    Excel 2007
    Posts
    5

    Talking Re: IF or PRODUCT or LOOKUP or RANGE or What to do?

    WOW!

    This guy IS godly. It worked perfectly.

    Infinite thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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