Calculating Moving Range with Blank Cells

Hello all,

I am attempting to create a control chart for some data, and need an average moving range (MR bar). This would be simple if my data was all together in column A, simply using something like...

Please Login or Register  to view this content.

However, I have blank cells which for all intensive purposes I want to pretend aren't there. I was thinking maybe a conditional statement used in conjunction with a lookup argument may get me there, but I am having trouble.

example data

Data
1.914535
1.803167



2.091054
1.983503


2.014628


1.789016
1.827065
1.86465

1.938139
1.96658

1.878581
1.867826


Using a manual method and the above code and data (copy and pasting into excel) I get:

MR_Bar=0.09453282

In terms of replicating your manual calcs. in column D with a consistent formula

D4:
=IF(AND(ISNUMBER($C4),COUNT($C$4:$C4)>1),ABS($C4-LOOKUP(9.99E+307,$C$3:$C3)),"")
copied down

A single cell calculation would be more convoluted - not clear if that's the requirement or not.

TBH I have been unable to conjure anything up to do this in a single cell (ie without helpers).

The complexity [for me] is establishing a method by which to create an appropriate array of values where each value is the prior numeric value for each numeric value found in the data set.
(the difficulty arises given a) interspersed blanks b) data points are not in order [understandably])

There is probably a way, however, if it exists it will be hideous, inefficient and Volatile (SUBTOTAL/OFFSET requirement)

To certify that I did think about it... note it doesn't work as it's WIP:

=AVERAGE(IF((SUBTOTAL(2,OFFSET($C$4,0,0,ROW($C$4:$C$24)-ROW($C$4)+1,1))>1)*SUBTOTAL(2,OFFSET($C$4,ROW($C$4:$C$24)-ROW($C$4),0)),ABS($C$4:$C$24-0)))
CTRL + SHIFT + ENTER

the remaining issue is of course replacing the 0 with an appropriate Array of values and this is the hard bit
the array should contain n-1 numeric values (where n = data points), each value is the prior numeric value for a given data point

because the data point values are not sorted (understandable) this precludes using a SUBTOTAL(4 approach against an increasingly large OFFSET range
given the "n" values (B) appear irrespective of adjacent numerical data point you can't use that value either (could then have used a SUMIF to retrieve value)

... to top it all I can't get a LOOKUP approach to work either !

edit: I don't like to concede defeat on a formula calculation so I will continue to think about if only for the challenge (or until such time as another kind soul resolves)

Just to elaborate regards:

Originally Posted by D.O

given the "n" values (B) appear irrespective of adjacent numerical data point you can't use that value either (could then have used a SUMIF to retrieve value)

If we were to change B4:B24 such that:

B4: =ROWS(B$4:B4)*SIGN($C4)
copied down to B24

Then in theory you could use:

=AVERAGE(IF((SUBTOTAL(2,OFFSET($C$4,0,0,ROW($C$4:$C$24)-ROW($C$4)+1,1))>1)*SUBTOTAL(2,OFFSET($C$4,ROW($C$4:$C$24)-ROW($C$4),0)),ABS($C$4:$C$24-SUMIF($B$3:$B$23,SUBTOTAL(4,OFFSET($B$3,0,0,ROW($B$3:$B$23)-ROW($B$3)+1,1)),$C$3:$C$23))))
confirmed with CTRL + SHIFT + ENTER

But I accept that this is still in effect use a helper calculation along the way (such that the n values in Column B are adjusted based on whether or not there is an adjacent data point)

And in reality that's not the best use of a helper cell as we've seen

One way is to use this formula

=AVERAGE(ABS(N(OFFSET(C4,SMALL(IF(ISNUMBER(C4:C24),ROW(C4:C24)-ROW(C4)),ROW(INDIRECT("2:"&COUNT(C4:C24)))),0))-N(OFFSET(C4,SMALL(IF(ISNUMBER(C4:C24),ROW(C4:C24)-ROW(C4)),ROW(INDIRECT("1:"&COUNT(C4:C24)-1))),0))))

confirmed with CTRL+SHIFT+ENTER

Thanks for looking at this dll - that's very clever indeed.