Traffic Light Formula Difficulty...

[thelostscott]

Hi all,

I have an issues trying to come up with a formula that will determine a Quarter/Year concatonation into a Traffic Light system.

Basically I have a table like what is shown below that has dates going across each row where the end result I'm seeking is a Colour. The colour will depend on whether the Quarter/Year is < or > Current Quarter & YEAR(TODAY()).

<table style="width: 402px; height: 112px;" border="0" cellpadding="0" cellspacing="0"><col style="mso-width-source:userset;mso-width-alt:3693;width:76pt" width="101"> <col style="mso-width-source:userset;mso-width-alt:3108; width:64pt" span="2" width="85"> <col style="mso-width-source:userset;mso-width-alt:438;width:9pt" width="12"> <col style="mso-width-source:userset;mso-width-alt:3108;width:64pt" width="85"> <tbody><tr style="mso-height-source:userset;height:11.25pt" height="15"> <td class="xl218" style="height:11.25pt;width:76pt" width="101" height="15">Q1 2002</td> <td class="xl219" style="border-left:none;width:64pt" width="85">Q3 2005</td> <td class="xl220" style="border-left:none;width:64pt" width="85">Q3 2010</td> <td class="xl216" style="width:9pt" width="12"> </td> <td class="xl217" style="width:64pt" width="85">Red</td> </tr> <tr style="mso-height-source:userset;height:11.25pt" height="15"> <td class="xl218" style="height:11.25pt;border-top:none" height="15">Q1 2004</td> <td class="xl219" style="border-top:none;border-left:none">Q3 2010</td> <td class="xl220" style="border-top:none;border-left:none">Q3 2015</td> <td class="xl216"> </td> <td class="xl221" style="border-top:none">White</td> </tr> <tr style="mso-height-source:userset;height:11.25pt" height="15"> <td class="xl218" style="height:11.25pt;border-top:none" height="15">Q3 2008</td> <td class="xl219" style="border-top:none;border-left:none">Q4 2013</td> <td class="xl220" style="border-top:none;border-left:none">Q3 2018</td> <td class="xl216"> </td> <td class="xl217" style="border-top:none">Amber</td> </tr> <tr style="mso-height-source:userset;height:11.25pt" height="15"> <td class="xl218" style="height:11.25pt;border-top:none" height="15">NOT FOUND
</td> <td class="xl219" style="border-top:none;border-left:none">Q3 2013</td> <td class="xl220" style="border-top:none;border-left:none">Q3 2018</td> <td class="xl216"> </td> <td class="xl217" style="border-top:none">White
</td> </tr> <tr style="mso-height-source:userset;height:11.25pt" height="15"> <td class="xl218" style="height:11.25pt;border-top:none" height="15">Q1 2012</td> <td class="xl219" style="border-top:none;border-left:none">NOT FOUND</td> <td class="xl220" style="border-top:none;border-left:none">NOT FOUND</td> <td class="xl215"> </td> <td class="xl217" style="border-top:none">Green</td> </tr> </tbody></table>
The formula I'm using currently that isn't really good enough as it doesn't take in to account what quarter we are currently in and if the first value if "NOT FOUND" it does't shift onto the next date, it just returns a "White":

=IFERROR(IF(VALUE(RIGHT($BT7,4))>=$BZ$11,"Green",IF(VALUE(RIGHT($BU7,4))>=$BZ$11,"Amber",IF(VALUE(RIGHT($BV7,4))<$BZ$11,"Red","White"))),"White")

With BZ11 being YEAR(TODAY())

I hope that makes sense to people. Please let me know if I can explain it in greater detail.

Many thanks!

 

[Steve=True]

Change your "White" at the very end of your fomula to the second if formula.

I now yeild amber. I am sure someone else can get the quarters for you, probably with MOD

=IFERROR(IF(VALUE(RIGHT($A4,4))>=$G$1,"Green",IF(VALUE(RIGHT($B4,4))>=$G$1,"Amber",IF(VALUE(RIGHT($C4,4))<$G$1,"Red","White"))),IF(VALUE(RIGHT($B4,4))>=$G$1,"Amber",IF(VALUE(RIGHT($C4,4))<$G$1,"Red","White")))

 

[thelostscott]

Thanks for that. I tried to apply that formula to other rows with different dates and it returned a new batch of "White" results (errors). Any ideas?

 

[Steve=True]

I replaced your BZ11 with G1 since i didn't want to do my testing all the way out there. Did you replace it back?

 

[thelostscott]

Yes, I reapplied the old cell locations

 

[Steve=True]

Without seeing the data, i can't be sure why you are getting a new error. Either upload the file or try the "Formula" ribbon the click on "Evaluate Formula" when you are on one of the cells that is erroring. It will show you how it is going through the formula logically.