Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

excel problem

Posted on 2012-03-29
4
Medium Priority
?
283 Views
Last Modified: 2012-03-30
Hi All,

I have a spreadsheet which has 4 categories; I need these 4 categories for each entry to rank high =3, med=2,low=1, or N/A=0. Based off the scores of the 4 categories I need a final column "Overall" to populate with a low,med,or high.  Please let me know if further clarification is needed.

*Please see attachment.

Thanks,example.xlsx
0
Comment
Question by:J_Drake
[X]
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
  • 2
4 Comments
 
LVL 40

Expert Comment

by:als315
ID: 37785947
It is not a problem to calculate totals (for High, for example):
=COUNTIF($B$3:$E$32;"High")
but what result you like to see in overall? Maximum? If at least one is High - High, etc?
0
 

Author Comment

by:J_Drake
ID: 37786569
Hi,

Thanks for the response. The overall column I need to be an average of the other 4 categories based on these values: high =3, med=2,low=1, or N/A=0.

Then I need run that against the listing p15:18.

Example- entry 1 is made up of a null (0),low(1),low(1),null(0) this would be 2 points.
I need this to be less than or equal to 4 but greater than zero- which would make it a low.

Let me know if you need further clarification of what I'm trying to do.


Thanks again,
0
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 1280 total points
ID: 37786670
Try this formula in G3

=VLOOKUP(MATCH(B3,{"N/A","Low","Medium","High"},0)-1+MATCH(C3,{"N/A","Low","Medium","High"},0)-1+MATCH(D3,{"N/A","Low","Medium","High"},0)-1+MATCH(E3,{"N/A","Low","Medium","High"},0),{0,"N/A";1,"Low";5,"Medium";8,"High"},2)
0
 

Author Closing Comment

by:J_Drake
ID: 37788272
Thanks! That was exactly what I was looking for.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

Outlook for dependable use in a very small business   This article is about using the Outlook application (part of Microsoft Office) in a very small business, or for homeowners where dependability and reliability are critical requirements. This …
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

636 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