Offset function trouble

[Heath]

I am haveing difficulty finding the error in my formula.
I am trying to create a worksheet in which I can type any
type of number and the sheet will calculate a moving
average for the data in question. Here is my sheet.

Date Adj. Close* sma 3
2-Aug-04 1106.62
3-Aug-04 1099.69
4-Aug-04 1098.63 #N/A
5-Aug-04 1080.7 #N/A
6-Aug-04 1063.97 #N/A
9-Aug-04 1065.22 #N/A
10-Aug-04 1079.04 #N/A
11-Aug-04 1075.79 #N/A
12-Aug-04 1063.23 #N/A
13-Aug-04 1064.8 #N/A
16-Aug-04 1079.34 #N/A
17-Aug-04 1081.71 #N/A
18-Aug-04 1095.17 #N/A
19-Aug-04 1091.23 #N/A
20-Aug-04 1098.35 #N/A
23-Aug-04 1095.68 #N/A
24-Aug-04 1096.19 #N/A
25-Aug-04 1104.96 #N/A
26-Aug-04 1105.09 #N/A
27-Aug-04 1107.77 #N/A
30-Aug-04 1099.15 #N/A
31-Aug-04 1104.24 #N/A

with this function in column C
=IF(ERROR.TYPE(AVERAGE(OFFSET(B3,-1,0,-$D$1,1)))
=4,"",AVERAGE(OFFSET(B3,-1,0,-$D$1,1)))

Any suggestions would be greatly appreciated

 

[duane]

Assuming You Want To Input A Date In Cell D1 And Calculate A 3 Day Av
Including That Date With 1st Data In Row 3

=average(offset(b3,match($d$1,$b$3:$b$1000,1),0,1,1)ffset(b3,match($d$1,$b$3:$b$1000,1)-2,0,1,1))

Should Do It - You Can Add An Error Test =if(iserror(expression) If Yo
Wan

 

[Ron Rosenfeld]

I am haveing difficulty finding the error in my formula.
I am trying to create a worksheet in which I can type any
type of number and the sheet will calculate a moving
average for the data in question. Here is my sheet.

Date Adj. Close* sma 3
2-Aug-04 1106.62
3-Aug-04 1099.69
4-Aug-04 1098.63 #N/A
5-Aug-04 1080.7 #N/A
6-Aug-04 1063.97 #N/A
9-Aug-04 1065.22 #N/A
10-Aug-04 1079.04 #N/A
11-Aug-04 1075.79 #N/A
12-Aug-04 1063.23 #N/A
13-Aug-04 1064.8 #N/A
16-Aug-04 1079.34 #N/A
17-Aug-04 1081.71 #N/A
18-Aug-04 1095.17 #N/A
19-Aug-04 1091.23 #N/A
20-Aug-04 1098.35 #N/A
23-Aug-04 1095.68 #N/A
24-Aug-04 1096.19 #N/A
25-Aug-04 1104.96 #N/A
26-Aug-04 1105.09 #N/A
27-Aug-04 1107.77 #N/A
30-Aug-04 1099.15 #N/A
31-Aug-04 1104.24 #N/A

with this function in column C
=IF(ERROR.TYPE(AVERAGE(OFFSET(B3,-1,0,-$D$1,1)))
=4,"",AVERAGE(OFFSET(B3,-1,0,-$D$1,1)))

Any suggestions would be greatly appreciated

Something like:

=AVERAGE(OFFSET(B2,0,0,-$D$1))

or, with an error test:

=IF($D$1>ROW(),"",AVERAGE(OFFSET(B2,0,0,-$D$1)))

Our averaging formulas do the same thing, although mine is slightly simpler.

Your #N/A comes from your ERROR.TYPE function. The argument for that function
needs to be an error number (1..n). When your average gives a real number,
this reduces to =IF(ERROR.TYPE=1102.31,...) for example, and that number is a
meaningless ERROR.TYPE; hence the #N/A.


--ron

 

[Guest]

I have also discovered another way to do this task with a count if function.
It goes like this: with data in column B and you formula in C in cell C2
=IF(COUNTIF($B$1:B1,">0")>=$D$1,AVERAGE(OFFSET(B1,0,0,-$D$1)),"") and the
moving average entry cell is D1. thanks for your help!

 

[Guest]

I have also discovered another way to do this task with a count if function.
It goes like this: with data in column B and you formula in C in cell C2
=IF(COUNTIF($B$1:B1,">0")>=$D$1,AVERAGE(OFFSET(B1,0,0,-$D$1)),"") and the
moving average entry cell is D1. thanks for your help!