Conditional Formatting Nested Formulas

  • Thread starter Thread starter Marissa
  • Start date Start date
M

Marissa

I'm working with a large spreadsheet that has multiple status options. Each
status has different criteria for example:

Status Green Yellow Red
T <60 >=60,<=80 >80
S <20 >=20,<=45 >45
D <30 >=30,<=55 >55
C <90 >=90,<=120 >120
B <30 >=30,<=60 >60
A <10 >=10,<=40 >40

I would like to apply conditional formats according to the status criteria.
Is that possible given that each status has a different criterial. Thank you!
 
One way to get going here ..

Illustrated in this sample:
http://www.freefilehosting.net/download/3jddl
Complex Criteria CF.xls

Construct:
In a sheet: x,
Set up the param table below in A2:C7
T 60 80
S 20 45
D 30 55
C 90 120
B 30 60
A 10 40

Then in your data sheet,

Assume the key col values are in B2 down, Statuses (T,S,D etc) in C2 down
Using 4 adjacent cols to the right, say cols D to G
Enter these labels into D1:G1 : Colour, Green, Yellow, Red

Put in, and array-enter,
ie press CTRL+SHIFT+ENTER [CSE] to confirm each formula:
D2: =INDEX($E$1:$G$1,MATCH(TRUE,ISNUMBER(E2:G2),0))
E2: =MATCH(1,(C2=x!A$2:A$7)*(B2<x!B$2:B$7),0)
F2: =MATCH(1,(C2=x!A$2:A$7)*(B2>=x!B$2:B$7)*(B2<=x!C$2:C$7),0)
G2: =MATCH(1,(C2=x!A$2:A$7)*(B2>x!C$2:C$7),0)

Select D2:G2, copy down to the last row of data. Col D will return the
required "colour" for each data row. Hide away cols E to G as desired. You
could then easily point to col D's colour values to conditionally format as
desired.

Take care to visually see that the top cells D2:G2 are correctly
array-entered before you copy down. Look in the formula bar, each formula
should be wrapped with curly braces by Excel: { }. If you don't see the
curlies, that means it wasn't correctly array-entered. Click inside the
formula bar, and re-do the CSE.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads: 15,500, Files: 352, Subscribers: 53
xdemechanik
 
Here's a slightly different approach......

Assuming the same table as Max, but in D2:F7 in the same sheet as your data,
then with status in column A and value in B, select the column(s) you want to
format and use these conditions

condition 1
=$B1>VLOOKUP($A1,$D$2:$F$7,3,0)*ISNUMBER($B1)
format red

condition 2
=$B1>VLOOKUP($A1,$D$2:$G$7,2,0)*ISNUMBER($B1)
format yellow

condition 3
=ISNUMBER($B1)
format green

Max said:
One way to get going here ..

Illustrated in this sample:
http://www.freefilehosting.net/download/3jddl
Complex Criteria CF.xls

Construct:
In a sheet: x,
Set up the param table below in A2:C7
T 60 80
S 20 45
D 30 55
C 90 120
B 30 60
A 10 40

Then in your data sheet,

Assume the key col values are in B2 down, Statuses (T,S,D etc) in C2 down
Using 4 adjacent cols to the right, say cols D to G
Enter these labels into D1:G1 : Colour, Green, Yellow, Red

Put in, and array-enter,
ie press CTRL+SHIFT+ENTER [CSE] to confirm each formula:
D2: =INDEX($E$1:$G$1,MATCH(TRUE,ISNUMBER(E2:G2),0))
E2: =MATCH(1,(C2=x!A$2:A$7)*(B2<x!B$2:B$7),0)
F2: =MATCH(1,(C2=x!A$2:A$7)*(B2>=x!B$2:B$7)*(B2<=x!C$2:C$7),0)
G2: =MATCH(1,(C2=x!A$2:A$7)*(B2>x!C$2:C$7),0)

Select D2:G2, copy down to the last row of data. Col D will return the
required "colour" for each data row. Hide away cols E to G as desired. You
could then easily point to col D's colour values to conditionally format as
desired.

Take care to visually see that the top cells D2:G2 are correctly
array-entered before you copy down. Look in the formula bar, each formula
should be wrapped with curly braces by Excel: { }. If you don't see the
curlies, that means it wasn't correctly array-entered. Click inside the
formula bar, and re-do the CSE.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads: 15,500, Files: 352, Subscribers: 53
xdemechanik
---
Marissa said:
I'm working with a large spreadsheet that has multiple status options. Each
status has different criteria for example:

Status Green Yellow Red
T <60 >=60,<=80 >80
S <20 >=20,<=45 >45
D <30 >=30,<=55 >55
C <90 >=90,<=120 >120
B <30 >=30,<=60 >60
A <10 >=10,<=40 >40

I would like to apply conditional formats according to the status criteria.
Is that possible given that each status has a different criterial. Thank you!
Click to expand...
Click to expand...
 
Back
Top