• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 194
  • Last Modified:

Formula to ascertain a rating from multiple scenarios - urgent - help please !!

Hi Experts

Here's one im hoping you can help me with:-

I have two tables of information that have an impact on ascertaining a certain rating based on the results.

First a category is selected in cell A1 that is either 1,2,3 or 4

Basically here are the two tables:-

Table 1
0 - 3 = N/A
3 - 10 = low
10 - 30 = medium
30 - 100 = high


Table 2
0% - 60% = n/a
60% - 90% = low
90% - 99% = medium
99% - 100% = high

The ratings table is as follows:-

 Category           0                 1              2                    2                 3                3              4
                         n/a             n/a           low            medium        low      medium        high
Low                    a               n/a            a                     b                b              c               n/a
Medium             b               n/a            b                    c                 c               d              n/a
High                 n/a                c              c                    d                d                d               e

What I want is if category 3 is selected in cell A1, and cell B1 totalled to 5 and cell B2 was 66% then cell B3 would look up the results criteris from table 1 and table 2 and based on the results would return a rating of 'b'.

Can you help as Im desperate and im sure this would be a challenge for you mega experts

Many Thanks in advance for your help
Jase Alexander
Jase Alexander
  • 4
  • 4
1 Solution
Jase AlexanderCompliance ManagerAuthor Commented:
Sorry should have read

First a category is selected in cell A1 of either 0,1,2,3 or 4

MichaelBusiness AnalystCommented:
Hi J,

can you post a sample workbook? It would make it easier for us to know how and where the tables are located.

Also, are the values of table 1 referring to the values of row 2 in the ratings table, and
the values of table 2 referring to the first column of ratings table
or the other way?

Jase AlexanderCompliance ManagerAuthor Commented:
HI There

Sample sheet attached

In amendment to the above the category is in cell C1 not A1 and the tables are labelled as Table 5 and Table 6 not Table 1 and Table 2 as I have suggested above.  The results table in this example is labeled Table 7.

The tables are in picture form at the bottom but obviously I want them in as actual data.

Based on this example as you can see the results gained from the data entered in the sheet in cell I30 relates to table 5 (highlighted green) and M30 relates to table 6 (highlighted green)

Based then on the category in Cell C1 and the results gained from cells I30 and M30, the rating in Table 7 is highlighted green (under 3 with high MTTP and low DC)

Hope this, and in turn, you can help.

Many Thanks
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Jase AlexanderCompliance ManagerAuthor Commented:
Apologies again

Should have read at the end (....high MTTFd and low DC) also the perfoirmance rating in M32 has been entered manually - it reads B but should be D as per the green highlight in table 7

(I altered the info to meet the criteria in the pics - apologies - the actual data I have results in a B rating)

M32 is the cell that should contain the formula or direct the macro to to get the rating required.

MichaelBusiness AnalystCommented:
The formula is in M32. It's an array formula, so it should be entered with ctrl+shift+enter (instead of just enter).

Since the actual tables were not in the workbook I copied them in at the bottom.
I had to change tables 5 and 6 slightly to make the formulas work. I hope this works for you.

If you need to move the tables around, you need to change the ranges accordingly. Tell me if you need any help with this.

MichaelBusiness AnalystCommented:
Oh about the highlighting of the cells in the tables.
I don't know too much about conditional formatting.
I would suggest that you post this in a separate question, once this one is resolved.
Jase AlexanderCompliance ManagerAuthor Commented:
Phenomenal !!

Cant thank you enough

I started with JazzyJoop's workbook because Table 5 and Table 6 were already set up. I modified Table 7 to match the picture and to have a concatenated Category | Table 6 column header.

I derived new formulas for MTTFd and DCavg because AVERAGEIF isn't the right way to do it. The requirements for these formulas were specified by the pictures.

=1/SUM(IF(J11:J29="Yes",1/I11:I29,0))            array-entered
=VLOOKUP(I31,Table5,2)                rating from Table 5

=SUM(IF(J11:J29="Yes",M11:M29/I11:I29,0))*I31            array-entered
=VLOOKUP(M31,Table6,2)               rating from Table 6

The overall rating could then be returned by this formula:
=IFERROR(HLOOKUP(C1 & "|" & O31,Table7,MATCH(J31,K64:K67,0),FALSE),"No match")
I used IFERROR to return "No match" if the combination of Category and Table 6 rating wasn't in Table 7.

MichaelBusiness AnalystCommented:
Nice, I like the concept of the concatenated hlookup

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now