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

Posted on 2013-06-19
Medium Priority
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:Jase Alexander
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4

Author Comment

by:Jase Alexander
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

by:Jase Alexander
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

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.


Author Comment

by:Jase Alexander
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 2000 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

by:Jase Alexander
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: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

762 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