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

Posted on 2013-06-19
Last Modified: 2013-06-19
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
Question by:spicecave
  • 4
  • 4

Author Comment

ID: 39258862
Sorry should have read

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


Expert Comment

ID: 39258971
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?


Author Comment

ID: 39259031
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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.


Author Comment

ID: 39259033
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.


Accepted Solution

Michael earned 500 total points
ID: 39259133
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.


Expert Comment

ID: 39259136
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.

Author Closing Comment

ID: 39259291
Phenomenal !!

Cant thank you enough

LVL 81

Expert Comment

ID: 39260394
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.


Expert Comment

ID: 39260414
Nice, I like the concept of the concatenated hlookup

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question