• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 59
  • Last Modified:

if formula with colour based formatting

I need assistance with a formula which if a score is 1-4 (e.g. 1, 2, 3 or 4), the cell is formatted clear background with "minor" as the text, if score is 5-6 (e.g. 5 or 6) the cell is formatted "low" with green background, if score is 7-10 (e.g. 7, 8, 9 or 10) the cell is formatted colour yellow with "moderate" as the text, if the score is 11-15 (e.g. 11, 12, 13, 14 or 15) the cell is formatted colour orange with "significant" as the text", and if the score is 16-24, the cell is formatted colour red with "critical" as the text. Can this be easily achieved?
0
pma111
Asked:
pma111
  • 6
1 Solution
 
Roy CoxGroup Finance ManagerCommented:
You need to use Conditional Formatting for the colour and I would suggest creating a lookup table for the scores then use VLOOKUP which can be used to find the nearest match. I'll provide an example of what I mean if it helps
0
 
Roy CoxGroup Finance ManagerCommented:
Here's a good explanation for VLOOKUP closest match
0
 
pma111Author Commented:
Example would be good
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Roy CoxGroup Finance ManagerCommented:
I'll  put one together for you. It might be in the morning as I'm about to finish for today.
1
 
Roy CoxGroup Finance ManagerCommented:
I've found one that created previously.
VLOOKUP.xlsx
0
 
Roy CoxGroup Finance ManagerCommented:
Some feedback from pma111 would be appreciated.
0
 
Roy CoxGroup Finance ManagerCommented:
Pleased to help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now