+ Reply to Thread
Results 1 to 4 of 4

Formula help for metrics with different thresholds - very difficult problem

  1. #1
    Registered User
    Join Date
    07-27-2012
    Location
    Detroit, MI
    MS-Off Ver
    Excel 2003
    Posts
    2

    Formula help for metrics with different thresholds - very difficult problem

    I can usually figure stuff out, but need help on this one.

    I have groupings of metrics with thresholds for example

    0-52 would be level 1
    53-60 would be level 2
    61-78 would be level 3
    79-82 would be level 4
    83 and above would be level 5

    what I am trying to do is figure out how to calculate this. Some examples of expected results;

    If the person scored 53 he would be at level 2 or approximately 2.0
    If the person scored 59 he would be at level 2 or approximately 2.9
    If the person scored 61 he would be at level 3 or approximately 3.0
    If the person scored 81 he would be at level 4 or approximately 4.5
    If the person scored 84 he would be at level 5 (max would always be 5, could not have 5.3)

    The thresholds are always in groups of 5 and from 0-100
    The thresholds can be different, so it has to be some kind of formula
    I dont need the level, I need the actual percent (out of 5) that they are; like 4.23 or 2.75

    Has anyone come across anything like this? At the end of the day, since there are 5 metrics, each one is approximately 20%, so I am thinking i have to convert the threshold to percent of a 5th, but that has not worked for me so far.

    Any help or ideas, similar threads, anything at all would be appreciated.

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,936

    Re: Formula help for metrics with different thresholds - very difficult problem

    i dont have time to spend on this right now, sorry (shift is ending) but perhaps try to put your threshholds and levels in a table, and then use a vlookup to find the value and return the level?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    07-27-2012
    Location
    Detroit, MI
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: Formula help for metrics with different thresholds - very difficult problem

    That would give me the level, but I need to find the percentage of the level, if that makes sense. So are they at 3.0, 3.5, 3.9..

    Its very confusing, so hopefully I am explaining it well.

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,728

    Re: Formula help for metrics with different thresholds - very difficult problem

    In the attached file I've set up a little table in M1:N5 showing your thresholds and the levels. I've used column A for the scores in your example above, and this formula is in B2:

    =IF(A2>=$M$5,5,MATCH(A2,$M$1:$M$4)+(A2-INDEX($M$1:$M$4,MATCH(A2,$M$1:$M$4)))/(INDEX($M$1:$M$5,MATCH(A2,$M$1:$M$4)+1)-INDEX($M$1:$M$4,MATCH(A2,$M$1:$M$4))))

    which is then copied down as far as is necessary.

    Hope this helps.

    Pete
    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)

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