Link to home
Start Free TrialLog in
Avatar of Jase Alexander
Jase AlexanderFlag for United Kingdom of Great Britain and Northern Ireland

asked on

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

and

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
J
Avatar of Jase Alexander
Jase Alexander
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

Sorry should have read

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




Regards
J
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?

Thanks
Joop
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
J
EESampleSheetRatingCalc.xlsx
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.



Regards
J
ASKER CERTIFIED SOLUTION
Avatar of Michael
Michael
Flag of Belgium image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
Phenomenal !!

Cant thank you enough

Regards
J
Avatar of byundt
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.

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

DCavg
=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.

Brad
EESampleSheetRatingCalcQ28161392.xlsx
Nice, I like the concept of the concatenated hlookup