+ Reply to Thread
Results 1 to 9 of 9

2 axis scale using INDEX, SUMPRODUCT, and "greater than" and "less than" functions

  1. #1
    Registered User
    Join Date
    01-15-2013
    Location
    Indianapolis
    MS-Off Ver
    Office 365
    Posts
    78

    2 axis scale using INDEX, SUMPRODUCT, and "greater than" and "less than" functions

    So I Have 3 sheets "Scale", "Incentive Calcs", and "Data"

    What I am trying to do here is to get a $/Direct value from Column R in sheet "Scale" into the sheet "Incentive Calcs", Column I based on each row's values from Columns G and H.

    Example formula from Cell I2 is this:

    Please Login or Register  to view this content.
    Currently this formula is resulting in #VALUE

    On Sheet "Scale" is the tables and on sheet "Data" is just merely the reference for sheet "Incentive Calcs"

    Column I for "Incentive Calcs" is the issue here and once I can get these values to properly populate, the rest of the sheet will align itself.

    I inherited this from someone else and would like the expertise of this community to help me solve it.

    Thank you
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,570

    Re: 2 axis scale using INDEX, SUMPRODUCT, and "greater than" and "less than" functions

    Please Login or Register  to view this content.
    Arrays must be the same size

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
    Ben Van Johnson

  3. #3
    Registered User
    Join Date
    01-15-2013
    Location
    Indianapolis
    MS-Off Ver
    Office 365
    Posts
    78

    Re: 2 axis scale using INDEX, SUMPRODUCT, and "greater than" and "less than" functions

    You are right that does work, but there is something wrong with the formula based on the criteria. Cell I2 should result in "0" not "0.01" since Cell H2 is "0.9865" which is not on the scale. The Perf Low scale starts at "1"! Does there have to be an IF statement in the formula?

    I guess I need to explain this better. If a person with a Direct% is 0.80 or higher and their Prod% is 1.01 and higher then find the intersect on sheet "Scale" that matches thier specific Dir% and Prod% and display it. If one or both do not meet the minimum 0.80 Direct% and 1.01 Prod% than display "0".

    I hope that makes sense.
    Last edited by Clooney003; 09-19-2017 at 04:00 PM.

  4. #4
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,570

    Re: 2 axis scale using INDEX, SUMPRODUCT, and "greater than" and "less than" functions

    The sumproduct does return zero for I2, but I don't know how many others will also return zero so, you need to test the sumproduct for zero before using it to return the row TO the INDEX().

  5. #5
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,570

    Re: 2 axis scale using INDEX, SUMPRODUCT, and "greater than" and "less than" functions

    try:
    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    01-15-2013
    Location
    Indianapolis
    MS-Off Ver
    Office 365
    Posts
    78

    Re: 2 axis scale using INDEX, SUMPRODUCT, and "greater than" and "less than" functions

    Ok great, we are moving forward thanks, protonLeah.

    Now on the "scale" sheet, we have a MAX value of 125% Prod. Anyone who hits that number and higher will be locked at 125% and now their Direct% will dictate how much they will receive in Column I ("Rate") in "Incentive Calcs". In this case, we will look at row 16.

    Now if we match up this user's Prod% and Direct% we know he hit 132% Prod and 81% Direct. We see on the "Scale" sheet that intersects with the value "$1.36". Currently, this formula is returning a #VALUE error due to his 132% Prod%.

  7. #7
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,570

    Re: 2 axis scale using INDEX, SUMPRODUCT, and "greater than" and "less than" functions

    mod #2:
    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    01-15-2013
    Location
    Indianapolis
    MS-Off Ver
    Office 365
    Posts
    78

    Re: 2 axis scale using INDEX, SUMPRODUCT, and "greater than" and "less than" functions

    Thank you for that. I will try this tomorrow when i get to work. In the meantime I came up with a crude method using a totally different set of formulas involving INDEX and MATCH and wrapping the referenced cells in =ROUND()

  9. #9
    Registered User
    Join Date
    01-15-2013
    Location
    Indianapolis
    MS-Off Ver
    Office 365
    Posts
    78

    Re: 2 axis scale using INDEX, SUMPRODUCT, and "greater than" and "less than" functions

    Ok, I got it where it needs to be. I fixed the crude work-around and made the chart bigger to cover the high performers. Also deleted the old references in sheet "Scale" and just used the chart for reference.

    Please Login or Register  to view this content.
    In columns G:H of IncentiveCalcs, I wrapped the existing formulas with =ROUND() so that my exact match would work.

    Thank you very much for getting me where I needed to be.
    Attached Files Attached Files

+ 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: 5
    Last Post: 02-05-2019, 12:03 AM
  2. Replies: 2
    Last Post: 07-24-2017, 02:19 AM
  3. [SOLVED] Column X-Ref list - Sheet1 Col A "pages", Col B:FL "Req" to Sheet2 ColA "req", ColB "page"
    By excel-card-pulled in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 04-07-2017, 09:30 AM
  4. Replies: 35
    Last Post: 01-13-2016, 02:16 AM
  5. Replies: 4
    Last Post: 11-17-2013, 12:05 PM
  6. [SOLVED] How to USE """"" cells count """"" change font color
    By austin123456 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-09-2013, 06:14 AM
  7. Replies: 5
    Last Post: 06-26-2006, 09:23 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