m5edward

New Member
Joined
Jul 20, 2016
Messages
42
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.

StoreYr 1 GrowthYr 2 GrowthYr 3 GrowthYr 4 GrowthYr 5 GrowthYr 6 GrowthYr 7 GrowthYr 8 GrowthYr 9 GrowthYr 10 GrowthAction
1.03.09.05.05Renovation
2.07.04.05.04.05.01.07Renovation
3.08.06.02.07Renovation
4.05.04.05.01.07.08.04.06Renovation
5.04.05.08.06Renovation
6.06.05.01.07.01.04.07.05.03Relocation
7.02.02.05.07Relocation
8.07.06.07.01.04.09.01.08.06.04Relocation
9.04.07.03.05Relocation
10.12.05.07.08.09.03.06.01.07.04Relocation

<tbody>
</tbody>




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),"")

ActionYr 1 GrowthYr 2 GrowthYr 3 GrowthYr 4 GrowthYr 5 GrowthYr 6 GrowthYr 7 GrowthYr 8 GrowthYr 9 GrowthYr 10 Growth
Renovation.054.054.050.061.047.042.051.054.061.048
Relocation.066.050.056.051.061.051.041.045.051.062

<tbody>
</tbody>




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.

ActionYr 1 GrowthYr 2 GrowthYr 3 GrowthYr 4 GrowthYr 5 GrowthYr 6 GrowthYr 7 GrowthYr 8 GrowthYr 9 GrowthYr 10 Growth
Renovation5555222100
Relocation5555333332

<tbody>
</tbody>



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!!!
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

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