+ Reply to Thread
Results 1 to 5 of 5

Calculate rating based on cut point table

  1. #1
    Registered User
    Join Date
    05-11-2008
    MS-Off Ver
    2010
    Posts
    91

    Calculate rating based on cut point table

    I'm am trying to calculate a rating based on a table that has certain cut points. I have certain types of items that are assigned letters (A, B, C, etc.). Depending on what each item's score is, they will get assigned a certain rating. The rating cut points are in a different table. The way the table works is that if the score comes in above the minimum cut point for that rating then it will get assigned that rating, otherwise it gets assigned the next rating below. I have attached a sample spreadsheet. So referring to the spreadsheet, looking at the Summary tab in row 5...for type B, the score is 3.5. When this is referenced to the Table tab and if you look at row 6, it will show that for type B a score of 3.5 is less than the cut point of 4 in column D, but higher than the cut point of 3 in column C which translates into a rating of 2 (ratings are referenced on the same column of the minimum cut point in row 4). So if you look back on the Summary tab in cell C5, the formula is currently calculating a correct rating of 2. My questions are as follows:

    Can I achieve this formula without having to use the INDIRECT function like how I did? Since the INDIRECT function makes the naming of the tab static (in this case "Table!"), I wouldn't want the formula to bomb out if the name of the tab should change for whatever reason.

    Or is there a better way to achieving these results as opposed to the way I am doing it now?

    TIA!
    Attached Files Attached Files

  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 rating based on cut point table

    Hi,

    One way

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    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 Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Calculate rating based on cut point table

    Try:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

  4. #4
    Registered User
    Join Date
    05-11-2008
    MS-Off Ver
    2010
    Posts
    91

    Re: Calculate rating based on cut point table

    Nice thanks guys. Just what I was looking for. Nice use of the OFFSET function to eliminate the need for the INDIRECT function.

  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 rating based on cut point table

    Quote Originally Posted by som3on3_10 View Post

    Can I achieve this formula without having to use the INDIRECT function like how I did? Since the INDIRECT function makes the naming of the tab static (in this case "Table!"), I wouldn't want the formula to bomb out if the name of the tab should change for whatever reason.

    Or is there a better way to achieving these results as opposed to the way I am doing it now?

    TIA!
    As an aside, and not relevant here since the ways you've been given are probably better anyway, but you can always use

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    in a formula to get the name of the sheet tab which you could incorporate in an INDIRECT() function and not need to worry about a change of tab name.

+ 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. calculate points based off a set point system
    By steggspolak in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-21-2013, 07:23 AM
  2. Online ladder table based on ELO rating system
    By Kuusilehto in forum Excel General
    Replies: 1
    Last Post: 12-18-2012, 05:31 AM
  3. Copy X Number of Rows Based on Checkbox, and Calculate High Point?
    By djpony in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-26-2011, 05:01 PM
  4. Excel 2007 : Calculate a Rating
    By Deap in forum Excel General
    Replies: 4
    Last Post: 01-22-2011, 06:15 PM
  5. Calculate Rating
    By Peterke in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-03-2005, 03:05 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