Conditional Format with nested If functions

Tatts

New Member
Joined
Aug 8, 2012
Messages
46
I have the following data and i am trying to create conditional formulas showing if an item is currently either behind or on schedule by highlighting the Status column green or red. I tried a formula for green that worked if an item was always on schedule at each Date point. It doesn't work if an item has ever been behind schedule but is now back on schedule i.e Items D and E.
Currently my conditional format formulas are:
Green =IF((G2>F2)*1+(E2>D2)*1+(C2>B2)*1=0,1,0)
Red =IF(G2>F2,1,IF(E2>D2,1,IF(C2>B2,1,0)))

<tbody>
</tbody>
I'm stuck here and can't figure out a formula that will give the current colour as shown in the Status column. Does anyone have a suggestion for improving those formulas?
Date 1 ScheduledDate 1 ActualDate 2 ScheduledDate 2 ActualDate 3 ScheduledDate 3 ActualStatusColour I currently have
Item A5-Apr-135-Apr-135-May-135-May-135-Jun-135-Jun-13GreenGreen
Item B10-Apr-1310-Apr-1310-May-1311-May-1310-Jun-13RedRed
Item C15-Apr-1315-Apr-1315-May-1316-May-1315-Jun-1316-Jun-13RedRed
Item D20-Apr-1321-Apr-1320-May-1321-May-1320-Jun-1320-Jun-13GreenRed
Item E25-Apr-1326-Apr-1325-May-1325-May-1325-Jun-13GreenRed

<tbody>
</tbody>
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Just checking what should happen for these two items?

Row 10 has nothing completed but no scheduled date has passed so I assume it should be green?

Row 11 was on time for the first scheduled date but the second scheduled date has passed without its "Actual" column being completed, so I assume it should be red?

Excel Workbook
ABCDEFGH
1Date 1 ScheduledDate 1 ActualDate 2 ScheduledDate 2 ActualDate 3 ScheduledDate 3 ActualStatus
10Item F25-Apr-1325-May-1325-Jun-13
11Item G10-Mar-1310-Mar-1310-Apr-1310-Jun-13
CF
 
Upvote 0
For 10 - Item F - I would priorities the Green conditional format and it would be nested in an if function along the lines of =if(c10="",0,New formula goes here) so should stay unformatted.
For 11 - Item G - Correct, it should be red.

Forgot to mention i am using Excel 2010.

As an aside, Excel Jeanie says it works up until 2007, will it work for 2010?
 
Upvote 0
For 10 - Item F - I would priorities the Green conditional format and it would be nested in an if function along the lines of =if(c10="",0,New formula goes here) so should stay unformatted.
I'm not quite sure I follow that but I've assumed row 10 in that screen shot should be green.

There may be a simpler way, but to avoid very long formulas I have used a couple of helper columns (I & J). These columns could be hidden after populating them with their formulas.

Formulas in I2 & J2 are copied down to the end of the data

I manually coloured H2:H9 Green and only used a Conditional Format to change the relevant cells to red. However, you could use a second CF condition for Green if you want.

Select H2:H?? and apply the CF formula shown.

Excel Workbook
ABCDEFGHIJ
1Date 1 ScheduledDate 1 ActualDate 2 ScheduledDate 2 ActualDate 3 ScheduledDate 3 ActualStatus14/04/136
2Item A5-Apr-135-Apr-135-May-135-May-135-Jun-135-Jun-136FALSE
3Item B10-Apr-1310-Apr-1310-May-1311-May-1310-Jun-134TRUE
4Item C15-Apr-1315-Apr-1315-May-1316-May-1315-Jun-1316-Jun-136TRUE
5Item D20-Apr-1321-Apr-1320-May-1321-May-1320-Jun-1320-Jun-136FALSE
6Item E25-Apr-1326-Apr-1325-May-1325-May-1325-Jun-134FALSE
7Item F25-Apr-1325-May-1325-Jun-130FALSE
8Item G10-Mar-1310-Mar-1310-Apr-1310-Jun-132TRUE
9Item H4-Apr-130TRUE
CF
Cells with Conditional Formatting
CellConditionCell FormatStop If True
H21. / Formula is =J2Abc




As an aside, Excel Jeanie says it works up until 2007, will it work for 2010?
Yes, it works with 2010 (my screen shot above is done in 2010) and I'm pretty sure with 2013 as well.
 
Upvote 0
Thanks Peter, i'm so used to trying to fit everything into one formula that i didn't even think of helper columns.
Your assumption is correct, when i said priorities (prioritise) i was talking about the conditional formatting where you set one rule above another and it then gets priority.

I'll check out Excel Jeanie too cos your posts looks much nicer with a neatly formatted table.
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top