Moving Range Average Calculation in Excel (in a single cell only).

[ed purtill]

Hi
I'm calculating Cpks in Excel but I'm having difficulty with the moving range average.

I'm using the formula min(usl-xbar/3sigma, xbar-lsl/3sigma) where sigma is moving range average/d2.

I'm trying to calculate the moving range in a single cell rather than get the range for each set to points and then average the answers.

Does anyone have any ideas? It would be greatly appreciated.
Thanks
Ed

 

[world quality]

Re: Moving Range Average Calculation

Ed,

See if this one helps or I can send you a single or subgroup, xmovingR chart.

 

[ed purtill]

Re: Moving Range Average Calculation

Hi
Not really I need the moving range average calculated in one cell. I'm doing this calculation across multiple column of data and don't want to generate as many columns again to calculate the moving range. Does that make sense?

 

[harry]

Re: Moving Range Average Calculation

Have you had a look at this thread: Individual/Moving Range Chart (IMR Chart) in Excel. Especially posts #5&8.

 

[ed purtill]

Hi Harry
All the calculations there involve creating the moving range first in a cell. I want to create the moving range average in one cell which would involve calculating the moving range for each and then averaging in one cell. Basically I want a more elegant piece of code.
Ed

 

[Bev D]

Hi Harry
All the calculations there involve creating the moving range first in a cell. I want to create the moving range average in one cell which would involve calculating the moving range for each and then averaging in one cell. Basically I want a more elegant piece of code.
Ed

I think the answer is that you can't do it easily unless it's a huge manually typed in formula - not relaly worth the time when you can type the formula for the first pair and then jsut copy down. There is no moving range function in Excel. you could write a macro I suppose but then I really wonder why this is necesarry. What value add does it bring? Isn't your time better spent analyzing the data? (unless you are trying to create atremplate for other users and variable amounts of data) in which case you should write a macro...

 

[ed purtill]

Got it with a little help from another forum
Press F2
=AVERAGE(IF($A$45:$A$65536<>"",IF(Stats!$A$46:$A$65536<>"",ABS(Stats!C$46:C$65536-Stats!C$45:C$65536))))
pressing contol shift and enter

Where column A is the data group and column C is the data range
Regards
Ed

 

[Darius]

In one cell, ???? Ed did it with "column A is the data group and column C is the data range"....

The only way is VBA Macros, do you accept Macros as a solution?:mg:

If so, I can do my homework.

 

[ed purtill]

Hey Darius
It was my own post so I'm all set now.
Thanks
Ed

 

[nasnyder]

Re: Moving Range Average Calculation

Ed,

See if this one helps or I can send you a single or subgroup, xmovingR chart.

Hello -
Any chance you could let me know the sheet protection password for this? I would like to customize the graph a bit.

Thank you so much!
Nate.