Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

IF formula

Posted on 2014-10-17
8
Medium Priority
?
77 Views
Last Modified: 2014-10-17
Collumn AB on the attached "combined" sheet contains a number.

I need a formula in the neighbouring cell AC to perform the following;

IF AB(...) = 11 return "Platinum"
IF AB(..) >=9<11 return "Gold"
IF AB(..)>=7<9 return "Silver"
IF AB(...)>=6<7 return "Bronze"
IF AB (...)<6 return "Poor Performer"

Hopefully this should be fairly straight forward.
EE-combined.xlsx
0
Comment
Question by:robmarr700
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 52

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 1200 total points
ID: 40386475
Copy the below formula to the cell AC:2 and then drag it till the end of AC column:
=IF(AB2<6;"Poor Performer";IF(AB2<7;"Bronze";IF(AB2<9;"Silver";IF(AB2<11;"Gold";"Platinum"))))

Open in new window

0
 

Author Comment

by:robmarr700
ID: 40386484
This is returning an error and highlighting the first number "6" in the sequence of the formula
0
 
LVL 34

Expert Comment

by:Rob Henson
ID: 40386485
Alternatively setup a small table as per below:

0      Poor Performer
6      Bronze
7      Silver
9      Gold
1000000      Platinum

Then use a lookup formula:

=VLOOKUP(AB2,$A$2:$B$6,2)

A2:B6 being the table above.

Thanks
Rob H
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 34

Accepted Solution

by:
Rob Henson earned 800 total points
ID: 40386494
Maybe Vitor's suggestion is not working because his locale uses the semicolon in formulae rather than a comma.

With commas instead:

=IF(AB2<6,"Poor Performer",IF(AB2<7,"Bronze",IF(AB2<9,"Silver",IF(AB2<11,"Gold","Platinum"))))

Thanks
Rob
0
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 40386498
Robmarr, check if you copied all characters. Or you can post here your Excel file so I can check it for you.
0
 
LVL 27

Expert Comment

by:ProfessorJimJam
ID: 40386509
Please find attached.
EE-combined.xlsx
0
 

Author Comment

by:robmarr700
ID: 40386512
Good Spot Rob,

Thanks Guys
0
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 40386520
Yes, Regional Settings. For us comma it's used for decimal numbers so we need to use ';' for separate parameters.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
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.

927 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