+ Reply to Thread
Results 1 to 7 of 7

Calculate Banding based on 2 variables

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    06-17-2010
    Location
    Donemana, NI
    MS-Off Ver
    Excel 2007
    Posts
    386

    Calculate Banding based on 2 variables

    Hi,
    I need to calculate bands based on the below table, I need a value to be populated in column I based on the forecast hours/ productivity. Can anyone help with this?

    So for example if the data in G3 = 52 & H3 = 105 then the output would be 1.5

    Thanks
    Johnny

    Hours Sold Productivity
    50-99.9 0.5 1 1.5 2
    100-124.9 1.5 2 2.5 3
    125-139.9 2.5 3 3.5 4
    140> 3.5 4 4.5 5

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Calculate Banding based on 2 variables

    Hi,

    This is not at all clear. Please put the data in a workbook and upload that so that we can see in context. In addition please explain what causes you to pick the value 1.5 as the answer to the above. It's not obvious where G3 and H3 fit into the request.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Calculate Banding based on 2 variables

    Thanks, but I still don;t understand how you calculate the numbers in B16:F19. Please explain the calculations with reference to specific cells in the b3:h7 range.

  4. #4
    Forum Contributor
    Join Date
    06-17-2010
    Location
    Donemana, NI
    MS-Off Ver
    Excel 2007
    Posts
    386

    Re: Calculate Banding based on 2 variables

    Richard,
    The numbers in B16:F19 are a matrix which gives output dependent on the values in columns G & H, For example if column G value was 56 and column H value was 67 then the result would be 0.5. I have just noticed that I dont have the productivity figures in the matrix box B16:F19, I have attached a new modified copy for your review which is hopefully a bit clearer with a highlighted result column.
    Attached Files Attached Files

  5. #5
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Calculate Banding based on 2 variables

    Hi,

    One approach with INDEX(MATCH()) functions - see attached.
    Attached Files Attached Files

  6. #6
    Forum Contributor
    Join Date
    06-17-2010
    Location
    Donemana, NI
    MS-Off Ver
    Excel 2007
    Posts
    386

    Re: Calculate Banding based on 2 variables

    Please see attached example
    Attached Files Attached Files

  7. #7
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    5,088

    Re: Calculate Banding based on 2 variables

    Can you give more details how we got I3 = 1.5 = ?? / ???
    - Battle without fear gives no glory - Just try

+ 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. how to calculate times based on two variables
    By Przymorze in forum Excel General
    Replies: 5
    Last Post: 05-10-2014, 06:48 PM
  2. calculate cycle time based on other variables
    By brywhi11 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-05-2013, 10:34 AM
  3. Calculate for three variables
    By SRodin in forum Excel General
    Replies: 2
    Last Post: 01-09-2012, 12:24 PM
  4. Formula to calculate single cell based on many variables
    By malawimick in forum Excel General
    Replies: 7
    Last Post: 12-15-2011, 02:13 AM
  5. Replies: 6
    Last Post: 12-22-2008, 06:16 PM
  6. Replies: 2
    Last Post: 05-14-2008, 04:57 PM
  7. Selecting patient data based on multiple variables to calculate mean and SD
    By Andrew! in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-01-2008, 02:14 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