relative column referance to capture the 6 week average
 

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,

relative column referance to capture the 6 week average
 

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?

--
Wigi
http://www.wimgielis.be = Excel/VBA, soccer and music


"Janis" wrote:

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,

relative column referance to capture the 6 week average
 

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

Jim Thomlinson


"Janis" wrote:

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,

relative column referance to capture the 6 week average
 

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

Jim Thomlinson


"Jim Thomlinson" wrote:

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

Jim Thomlinson


"Janis" wrote:

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,

relative column referance to capture the 6 week average
 

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.

"Wigi" wrote:

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?

--
Wigi
http://www.wimgielis.be = Excel/VBA, soccer and music


"Janis" wrote:

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,