Search code examples
exceldatecriteriastandard-deviation

Finding standard deviation based on date and name of data

The title summarizes what I'm trying to do. My data looks like the following:

              201801  201802  201803 201804  201805  201806  201807  201808
    Carrots   1.05    1.10    1.12   1.09    1.08    1.06    1.20    1.26
    Lettuce   2.10    2.20    2.24   2.18    2.16    2.12    2.40    2.52
    Tomatoes  0.53    0.55    0.56   0.55    0.54    0.52    0.60    0.63

I want Excel to find the variance of these values if they are more recent than 201803.

So for Carrots I would want the variance of (1.09, 1.08, 1.06, 1.20, 1.26). I don't want to hard code this as I will have new data coming in every month adding one more month. So the variance of the a series will shift over one column every month.

What I want my ultimate outcome to look like is the following where the variance is calculated automatically from the data above that will change each month and chooses the data based on the series name (carrots).

             Variance of Price
   Carrots   .23
   Lettuce   .15
   Tomatoes  .11

I think an indicator for the dates would be helpful.

Solution

  • Use this as an array formula:

    =STDEV(IF($B$1:$AAA$1>B7,INDEX(B:AAA,MATCH(A8,A:A,0),0)))

    Being an array formula it needs to be confirmed with Ctrl-Shift-Enter instead of Enter when Exiting edit mode.

    enter image description here