+ Reply to Thread
Results 1 to 7 of 7

Excel Math problem

  1. #1
    Registered User
    Join Date
    09-21-2009
    Location
    mexico
    MS-Off Ver
    Excel 2007
    Posts
    20

    Excel Math problem

    Need help to create a formula that will, first multiply the value of two cells, then divide by 3600 & multiply the answer by a certain percentage depending on which range the answer falls into. eg.
    If 2100 (A1) x 210 (B2) divided by 3600 falls between 1200 & 1400 then multiply by 110%, if answer falls between 1000 & 1200 then multiply by 112%, if answer falls between 900 & 1000 then multiply by 114%.
    Thank you in advance for any assistance.

  2. #2
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Excel Math problem

    not quite your answer, but one more question:

    (2100*210)/3600 = 122.5 according to my Excel. What do you want to do with results that do not fall within any of the three ranges you mention?

  3. #3
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Excel Math problem

    Try this

    =(A1*B1)/3600*VLOOKUP((A1*B1)/3600,{1,1;900,1.14;1000,1.12;1200,1.1},2,TRUE)

    if the result of A*B/3600 is less than 900, the number is not changed.

  4. #4
    Registered User
    Join Date
    09-21-2009
    Location
    mexico
    MS-Off Ver
    Excel 2007
    Posts
    20

    Re: Excel Math problem

    I apologize, 2100 should be 21000, the the example given.
    If 21000 (A1) x 210 (B1) divided by 3600 falls between 1200 & 1400 then multiply by 110%.
    If (A1 x B1)/3600 falls between 1000 & 1200 then multiply by 112%
    If (A1 X B1)/3600 falls between 900 & 1000 then multiply by 114%.
    The numbers in the A & B columns will appear on approx 365 rows (days of the week in 2009) & will vary from day to day. Numbers in the A column will range from 21000 to 14000. Numbers in the B column will range from 200 to 240.
    Thank you for your assistance.

    Joe

  5. #5
    Registered User
    Join Date
    09-21-2009
    Location
    mexico
    MS-Off Ver
    Excel 2007
    Posts
    20

    Re: Excel Math problem

    Thank you!! That is amazing! Can you explain the formula to me so that if needed more ranges can be added? eg if ((A1 X B1)/3600) falls into the range of 800 to 900.
    Cannot thank you enough for helping me out!

    Joe

  6. #6
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Excel Math problem

    Hi again

    the formula basically says:

    calculate the result of A*B/3600 and then look up this value in the table

    1 - 1
    900 - 1.14
    1000 - 1.12
    1200 - 1.1

    if you find the number in the first column of the table, return the number in the second column of the same table row.

    VLOOKUP is normally used with a range consisting of real cells, but you can use an array constant instead of a cell range. The array for this Vlookup has 2 columns and four rows. An array constant is defined by separating cells in the same row with a comma and separating rows with a semicolon. So the array looks like

    1,1;
    900,1.14;
    1000,1.12;
    1200,1.1

    put the whole thing on one line and wrap it in curly brackets to complete the array constant definition

    {1,1;900,1.14;1000,1.12;1200,1.1}

    The TRUE argument of the VLookup function means that if an exact match is not found, the nearest match that is smaller than the lookup value will be substituted.
    For this to work, you need to sort the lookup range by the first column, in ascending order.

    so, if you want to change your formula later to include a different percentage for, say, 950 - 1000, make sure that you don't simply put 950 at the end of the array, but rather like this:

    1,1;
    900,1.14;
    950,1.13;
    1000,1.12;
    1200,1.1

    ... which makes the complete array look like

    {1,1;900,1.14;950,1.13;1000,1.12;1200,1.1}

    Looks confusing at first, but works a treat!

  7. #7
    Registered User
    Join Date
    09-21-2009
    Location
    mexico
    MS-Off Ver
    Excel 2007
    Posts
    20

    Re: Excel Math problem

    WOW!!! i have to tip my hat to you, that is a great explanation of how the formula works.
    Thank you, very much indeed.

    Joe

+ 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