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

E

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

W

#### world quality

Re: Moving Range Average Calculation

Ed,

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

#### Attachments

• 422 KB Views: 1,116
E

#### 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?

E

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

##### Heretical Statistician
Super Moderator
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...

E

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

D

#### 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.

Last edited by a moderator:
N

#### 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.

How MR (Moving Range) in Z-MR chart is calculated Using Minitab Software 5
SPC, Moving Range Chart and Weighted Graph Basic Questions Statistical Analysis Tools, Techniques and SPC 1
IMR (Individuals and Moving Range) Chart for Short-Run SPC - How much data to include Statistical Analysis Tools, Techniques and SPC 1
M When a moving range is 0, is it acceptable to have a PP/PPK of 0 also? Statistical Analysis Tools, Techniques and SPC 8
Individuals 2 Point Moving Range Chart Statistical Analysis Tools, Techniques and SPC 6
J Moving Range Upper and Lower Control Limits when alpha=0.002 Statistical Analysis Tools, Techniques and SPC 4
R Individuals and Moving Range Chart problem Statistical Analysis Tools, Techniques and SPC 6
Moving Range or Range Charts for Subgrouped Data Statistical Analysis Tools, Techniques and SPC 6
A Individual/Moving Range Chart (IMR Chart) in Excel Excel .xls Spreadsheet Templates and Tools 8
E Formulas and Constants - Control Limits for Individual and Moving Range Control Chart Statistical Analysis Tools, Techniques and SPC 2
A SPC Rules Question for Moving Range chart Statistical Analysis Tools, Techniques and SPC 9
M Individual or Moving Range Chart? Parts run in lots of 7 with 4 key wall thicknesses Statistical Analysis Tools, Techniques and SPC 5
Moving Range charts and capability? Statistical Analysis Tools, Techniques and SPC 6
Travel in Hawaii, and moving to Honolulu Travel - Hotels, Motels, Planes and Trains 0
Moving from AC7004 to AS9100D AS9100, IAQG, NADCAP and Aerospace related Standards and Requirements 5
FAI Requirement After Moving CNC Machine? AS9100, IAQG, NADCAP and Aerospace related Standards and Requirements 15
Quality's Authority - Company Moving In ISO 9000, ISO 9001, and ISO 9004 Quality Management Systems Standards 17
Cl. 9.2 - Moving parts - Applicable only to machinery? IEC 60601 - Medical Electrical Equipment Safety Standards Series 1
Quality plan for moving locations ISO 13485:2016 - Medical Device Quality Management Systems 3
What do CB´s change when doing a partial moving ? ISO 9000, ISO 9001, and ISO 9004 Quality Management Systems Standards 1
Earthing of moving parts, designing tips IEC 60601 - Medical Electrical Equipment Safety Standards Series 2
Moving and positioning of patient - Mechanical hazard IEC 60601 - Medical Electrical Equipment Safety Standards Series 18
D Company Moving - Relabelling Question Medical Device and FDA Regulations and Standards News 0
Moving on - I accepted an offer to be a Supplier Quality Engineer at GE Healthcare Coffee Break and Water Cooler Discussions 42
Moving from client-server to cloud-based, is that a new submission? Medical Information Technology, Medical Software and Health Informatics 3
M How to calculate benefits? Moving some developed non controlled software/automation systems Service Industry Specific Topics 2
Moving from Contract Manufacturing to Production In-House ISO 13485:2016 - Medical Device Quality Management Systems 3
What to expect when moving to higher-risk medical devices? Other Medical Device and Orthopedic Related Topics 1
It is acceptable moving remote locations staff to manufacturing plant for auditing? IATF 16949 - Automotive Quality Systems Standard 3
How is documentation affected moving to an electronic system? ISO 13485:2016 - Medical Device Quality Management Systems 13
L Moving from ProArc to IFS Document Management Document Control Systems, Procedures, Forms and Templates 1
K Sister Company Moving to AS - First time "consultant" - Help? AS9100, IAQG, NADCAP and Aerospace related Standards and Requirements 17
I Moving into Clinical Trials and need to find GMP Suppliers 21 CFR Part 820 - US FDA Quality System Regulations (QSR) 1
B Moving (Transition) From ISO 14001:2004 to ISO 14001:2015 ISO 14001:2015 Specific Discussions 3
P Moving from a Paper based Quality System to an Electronic Quality System Manufacturing and Related Processes 14
S Validation requirement after moving labeling data base to different drive Software Quality Assurance 7
REACH due dilligence and meeting the ever moving SVHC target RoHS, REACH, ELV, IMDS and Restricted Substances 5
C Moving to a different Notified Body Quality Manager and Management Related Issues 5
R What Documentation is done by QA in FMCG (Fast Moving Consumer Goods) Misc. Quality Assurance and Business Systems Related Topics 2
Risk Analysis for moving manufacturing equipment ISO 14971 - Medical Device Risk Management 17
C ISO9001: Moving from site certification to corporate certification Quality Manager and Management Related Issues 9
Moving a Calibration/Test Laboratory to a new Location General Measurement Device and Calibration Topics 5
R Moving from ISO 9001 to AS9120A Document Control Systems, Procedures, Forms and Templates 2
Q Moving steps from ISO 9001 to TL 9000 TL 9000 Telecommunications Standard and QuEST 4
N Protective Earthing of Medical Device Moving Parts IEC 60601 - Medical Electrical Equipment Safety Standards Series 4
F Moving our Distribution Company - ISO 9001 Certification Impact ISO 9000, ISO 9001, and ISO 9004 Quality Management Systems Standards 4
NCR and CAPA in the FMCG (Fast Moving Consumer Goods) Industry Nonconformance and Corrective Action 1
D A2LA Certification and moving to a new Facility General Measurement Device and Calibration Topics 6
S Moving to a New Company for a New Position Coffee Break and Water Cooler Discussions 3
S Validation and Qualification when moving to another Facility Qualification and Validation (including 21 CFR Part 11) 2