12 Replies Latest reply on Jul 26, 2017 2:38 PM by Namrata Gholve

# Window_STDEV and WINDOW_MEDIAN

I am trying to calculate the standard deviation and mean over a few days (user inputs how many days he/she wants to use to calculate the standard deviation/mean).

The window_stdev and windown_median calculates the standard deviation/mean per day; it, then, calculates the stddev or mean.

That is not what I want. For the past two days, I want to use all the data to calculate the stddev and mean.

For example,

day 1: 0.81, 1, 0.67, 0.01, 7

day2: 5, 0.04, 5, 0.7, 0.9

Day 1 standard of deviation: 2.88

Day 2 standard of deviation: 2.46

using window_stdev (stdev (resultvalue), 1, 0) = 0.294

standard deviation of all values: 2.53

Is there a way to obtain 2.53?

• ###### 1. Re: Window_STDEV and WINDOW_MEDIAN

Hi Anna,

According to the description of your scenario, I think you need to apply the function STDEV.

take a look at the attached workbook based on the data you supplied.

Best

Ramon

• ###### 2. Re: Window_STDEV and WINDOW_MEDIAN

I think I was not clear.

I am trying to calculate a running standard deviation.  The user inputs the number of days he/she wants the standard of deviation to be calculated, and for a certain time frame.

For example, the user can enter for time frame from Dec 24 of 2012 to Nov 24 of 2013, and the rolling standard of deviation of 5 days.

the standard dev for dec 24 includes the data of dec 24.

The standard dev for dec 25 includes the data of dec 24 + dec 25.

The standard dev for dec 26 includes the data of dec 24 + dec 25 + dec26

The standard dev for dec 27 includes the data of dec 24 + dec 25 + dec26 + dec27

The standard dev for dec 28 includes the data of dec 24 + dec 25 + dec26 + dec27 + Dec 28

The standard dev for dec 29 includes the data of dec 25 + dec26 + dec27 + Dec 28 + Dec29

The standard dev for dec 30 includes the data of dec26 + dec27 + Dec 28 + Dec29 + Dec30

The standard dev for dec 31 includes the data of dec27 + Dec 28 + Dec29 + Dec30 + Dec31

The standard dev for Jan 1, 2013  includes the data of Dec 28 + Dec29 + Dec30 + Dec31+Jan1

I do need a function similar to window_standard deviation to calculate the standard deviation based on the days the user input.

Th window function works great when I am calculating the window_sum or window_avg.  But, I am not getting the data I want from window_standard deviation and window_median.  That is because the window standard of deviation calculates per individual day, and not all data combined during those days.

• ###### 3. Re: Re: Window_STDEV and WINDOW_MEDIAN

Hi Anna,

See attached workbook, specifically Sheet 5.

Best,

Ramon

• ###### 4. Re: Re: Window_STDEV and WINDOW_MEDIAN

Hi Anna,

I did some improvement to the attached workbook in my previous post.

Best,

Ramon

• ###### 5. Re: Window_STDEV and WINDOW_MEDIAN

Hi Anna,

For this, you have to use LOOKUP(STDEV(DAYS),2)

2 --> is variable, if you need last 2 day 2 it depends upon the user.

Regards,

Muthu Krishnan. M

• ###### 6. Re: Re: Window_STDEV and WINDOW_MEDIAN

Ramon, Your standard deviation is calculating by day.  In one day, I have many values.

What I want is the standard deviation of all the values in the x number of days.

Thanks for the help.  I have calculate the standard deviation manually.  And, I am getting what I want.

Thanks.

• ###### 7. Re: Window_STDEV and WINDOW_MEDIAN

Muthu Krishnan

Can you please explain how this calculation will help the original poster?  When providing a solution, please provide a packaged workbook exemplifying how your answer solves the problem being addressed.  These one sentence answers are generally not very helpful without an explanation or example.

Thanks.

• ###### 8. Re: Window_STDEV and WINDOW_MEDIAN

Thanks Matthew

+1

Ramon

• ###### 9. Re: Window_STDEV and WINDOW_MEDIAN

Hi Anna,

Are you able to share your calculations just to learn how you did it?

Best,

Ramon

• ###### 10. Re: Window_STDEV and WINDOW_MEDIAN

Hi Ramon,

Yes, I can share my calculations.  But, I have to do it on the weekend.

I will list the requirements as well.

Thanks,

Anna

• ###### 11. Re: Re: Window_STDEV and WINDOW_MEDIAN

Hi Anna,

What I believe you're wanting here is something like TOTAL(STDEV([value])) with offsets so you can get a running standard deviation that is the row level aggregated across values defined by the offsets. However, TOTAL() doesn't take offsets. So a solution is to increase the level of detail in the view so that you can have a WINDOW_STDEV(MIN([value]) and use offsets for that to make it a running standard deviation. You'd need to have some sort of row-level identifier that you can bring into the view, and then do a WINDOW_STDEV(MIN([Value]) that is addressed on the day and row level ID, then finally use another calculation to only return the last value for each day.

I set up some data and a workbook in the attached.

Jonathan

2 of 2 people found this helpful
• ###### 12. Re: Window_STDEV and WINDOW_MEDIAN

Hello Anna,

I am trying to do the same thing as you mentioned in your question. Would you be able to give us some more information on how you resolved the running standard deviation problem for certain time frame?

Thanks!