| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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, |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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, |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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, |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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, |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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, |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| help with 12 week rolling average | Excel Worksheet Functions | |||
| capture last cell in column | Excel Discussion (Misc queries) | |||
| Average Function with Day of Week Criteria | New Users to Excel | |||
| Capture and list data from a column or row | Excel Worksheet Functions | |||
| Capture event when exit Column G | Excel Discussion (Misc queries) | |||
Linear Mode
