Complex Conditional Formatting Scenario

[m5edward]

Hi Everyone,

Thank you in advance for whatever help you may be able to provide.


Purpose/Scope

Analyzing the effectiveness of retail store renovations and relocations.


Existing Material

I have many slices or views, but let's assume there is only one.

The back-end looks like this. Please note that empty cells will still have a formula in them as they are being populated from a data dump.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Store[/TD]
[TD]Yr 1 Growth[/TD]
[TD]Yr 2 Growth[/TD]
[TD]Yr 3 Growth[/TD]
[TD]Yr 4 Growth[/TD]
[TD]Yr 5 Growth[/TD]
[TD]Yr 6 Growth[/TD]
[TD]Yr 7 Growth[/TD]
[TD]Yr 8 Growth[/TD]
[TD]Yr 9 Growth[/TD]
[TD]Yr 10 Growth[/TD]
[TD]Action[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD].03[/TD]
[TD].09[/TD]
[TD].05[/TD]
[TD].05[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Renovation[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD].07[/TD]
[TD].04[/TD]
[TD].05[/TD]
[TD].04[/TD]
[TD].05[/TD]
[TD].01[/TD]
[TD].07[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Renovation[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD].08[/TD]
[TD].06[/TD]
[TD].02[/TD]
[TD].07[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Renovation[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD].05[/TD]
[TD].04[/TD]
[TD].05[/TD]
[TD].01[/TD]
[TD].07[/TD]
[TD].08[/TD]
[TD].04[/TD]
[TD].06[/TD]
[TD][/TD]
[TD][/TD]
[TD]Renovation[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD].04[/TD]
[TD].05[/TD]
[TD].08[/TD]
[TD].06[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Renovation[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD].06[/TD]
[TD].05[/TD]
[TD].01[/TD]
[TD].07[/TD]
[TD].01[/TD]
[TD].04[/TD]
[TD].07[/TD]
[TD].05[/TD]
[TD].03[/TD]
[TD][/TD]
[TD]Relocation[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD].02[/TD]
[TD].02[/TD]
[TD].05[/TD]
[TD].07[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Relocation[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD].07[/TD]
[TD].06[/TD]
[TD].07[/TD]
[TD].01[/TD]
[TD].04[/TD]
[TD].09[/TD]
[TD].01[/TD]
[TD].08[/TD]
[TD].06[/TD]
[TD].04[/TD]
[TD]Relocation[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD].04[/TD]
[TD].07[/TD]
[TD].03[/TD]
[TD].05[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Relocation[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD].12[/TD]
[TD].05[/TD]
[TD].07[/TD]
[TD].08[/TD]
[TD].09[/TD]
[TD].03[/TD]
[TD].06[/TD]
[TD].01[/TD]
[TD].07[/TD]
[TD].04[/TD]
[TD]Relocation[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]




The front-end looks like this and is populated with an 'Averageifs' formula, simplified example is below. "CompStores" is a named data range.

=IFERROR(AVERAGEIFS(CompStores[Growth Year 2],CompStores[Action],$A1),"")

[TABLE="width: 500"]
<tbody>[TR]
[TD]Action[/TD]
[TD]Yr 1 Growth[/TD]
[TD]Yr 2 Growth[/TD]
[TD]Yr 3 Growth[/TD]
[TD]Yr 4 Growth[/TD]
[TD]Yr 5 Growth[/TD]
[TD]Yr 6 Growth[/TD]
[TD]Yr 7 Growth[/TD]
[TD]Yr 8 Growth[/TD]
[TD]Yr 9 Growth[/TD]
[TD]Yr 10 Growth[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Renovation[/TD]
[TD].054[/TD]
[TD].054[/TD]
[TD].050[/TD]
[TD].061[/TD]
[TD].047[/TD]
[TD].042[/TD]
[TD].051[/TD]
[TD].054[/TD]
[TD].061[/TD]
[TD].048[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Relocation[/TD]
[TD].066[/TD]
[TD].050[/TD]
[TD].056[/TD]
[TD].051[/TD]
[TD].061[/TD]
[TD].051[/TD]
[TD].041[/TD]
[TD].045[/TD]
[TD].051[/TD]
[TD].062[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]




Just for clarity, the counting table (if needed) would look like this. Hopefully this project will not need a counting table, especially considering switching "averageifs" to "countifs" cannot easily be done by a search+replace because countifs has less arguments.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Action[/TD]
[TD]Yr 1 Growth[/TD]
[TD]Yr 2 Growth[/TD]
[TD]Yr 3 Growth[/TD]
[TD]Yr 4 Growth[/TD]
[TD]Yr 5 Growth[/TD]
[TD]Yr 6 Growth[/TD]
[TD]Yr 7 Growth[/TD]
[TD]Yr 8 Growth[/TD]
[TD]Yr 9 Growth[/TD]
[TD]Yr 10 Growth[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Renovation[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Relocation[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]



The Task

• Highlight each cell in the front-end based on how many observations there are with the purpose of knowing how confident we are in that growth %.
• The counting system I would like to use in real life is under 5 observations = red, 5 to 9 = yellow, 10 or above = green.

Please help me, I really don't want to have to do this by hand lol. Thanks to anyone who even attempts to help!!!