Solved

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

Posted on 2013-06-19
9
186 Views
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

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
0
Comment
Question by:spicecave
  • 4
  • 4
9 Comments
 

Author Comment

by:spicecave
ID: 39258862
Sorry should have read

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




Regards
J
0
 
LVL 6

Expert Comment

by:Michael
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?

Thanks
Joop
0
 

Author Comment

by:spicecave
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
J
EESampleSheetRatingCalc.xlsx
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

Author Comment

by:spicecave
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.



Regards
J
0
 
LVL 6

Accepted Solution

by:
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.

Joop
EESampleSheetRatingCalc.xlsx
0
 
LVL 6

Expert Comment

by:Michael
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.
0
 

Author Closing Comment

by:spicecave
ID: 39259291
Phenomenal !!

Cant thank you enough

Regards
J
0
 
LVL 81

Expert Comment

by:byundt
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.

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
0
 
LVL 6

Expert Comment

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

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
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.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
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…

730 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