Create variance slider?

**SueWithQuestion** · 10-01-2010, 11:21 AM

The attached sample file is a very small example of a data set I am working with.

You will see I've used a combination of a scroll bar and conditional formatting to created a level of interactivity with the data. (This technique was thanks to one of you wonderful folks here on the forum.) As you move the slider, cells whose values are below the slider value disappear (via having their text color changed to white in the conditional formatting.)

This technique is really great. I'd like to add another slider to highlight the amount of variance from month-to-month for each group.

My actual spreadsheet is much larger than this and I really cannot add new columns to hold variance values. Another challenge is the way the data is laid out with Jan through Jun being repeated.

I want another slider that will be 0% to 100%, and it will color in red those values whose variance from the previous month are above the current slider value.

Since there is never a "previous month" for the Jan values to be compared against, they should never get colored.

I hope I've described this well enough for someone to be able to help me.

**DonkeyOte** · 10-01-2010, 01:02 PM

Could you outline specifically which values are being compared in terms of variance ?

For ex. for Feb Group 1 - are you comparing C7 to C6, D7 to D6 & E7 to E6 or collective SUM(C7:E7) to collective SUM(C6:E6) ? Or something else entirely ?

Where there are missing values in prior periods (eg Group 2 Mar to Feb) what then ?
Do you intend to treat the blanks as 0 and thus generate a variance (unlike Jan for which no prior value is assumed)

**SueWithQuestion** · 10-01-2010, 01:43 PM

I want to compare variance from the previous month for each group, for each column.

Consider each column the number of items sold for different products. So , column C is number of cars sold, column D is boats sold, and column E is airplanes sold. I don't care about the variance between cars and boats. I only care about variance within a column for a six month period (i.e., the variance in car sales for each Group.)

So, for Group 1, the variance for column C's values from Jan to Feb, and then from Feb to Mar, and Mar to Apr, etc.

**DonkeyOte** · 10-01-2010, 04:50 PM

Sorry for delay - offline.

See if the attached does what you want ?

You didn't stipulate what should happen when there is no "prior" month value outside of Jan - ie interspersed blanks.
Nor did you specify in which direction was the variance of interest - I've assumed either (ie absolute variance).