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!
Bookmarks