relative column referance to capture the 6 week average

[Janis]

Is there a way to change this formula to be 6 relative columns? I average
the last 6 weeks( columns z:ae) but I have to insert a new column each week
so I have to change the range to aa:af .... It would be great to have it sum
the 6 columns instead of having to hard wire them.

=IF(SUM(Z15:AE15)=0,0,ROUNDUP(SUM(Z15:AE15)/COUNTIF(Z15:AE15,">0"),2))

Is it possible?

tia,

 

[Wigi]

Hi

if I use your formula as stated, it works perfectly if I insert a new column
to the left of column Z. Xhere do you insert a new column, then, such that
the formula is screwed up?

 

[Jim Thomlinson]

Assuming you are not trying to copy this formula down then you can use a
simple dynamic named range. For example define a named range call LastSix
with this as the formula...

=OFFSET(Sheet2!A15, 0, COUNTA(Sheet2!15:15) - 6, 1, 6)

Now you can use
=IF(SUM(LastSix)=0,0,ROUNDUP(SUM(LastSix)/COUNTIF(LastSix,">0"),2))

Check out this link for mor info on dynaic named ranges...
http://www.cpearson.com/excel/named.htm

If you need to copy this formula down it can still be done but the second
argument (which is 0) will need to be changed to Column() +or- some number...

 

[Jim Thomlinson]

Sorry the second argument thing is not right. For 1 it would be row and even
then it probably would not work as a dynamic named range. You could however
use that formula directly in your formula with the second argument as Row()
and that should work...

 

[Janis]

I inserted the column right before the average and it didn't update when I
put a figure in. The reason is it sums z2:Ae2. I inserted the new value on
AF. It moved the average column from AE to AF.