#value! error trying to create a simple dynamic named range

[Janis]

The lastsix named range is the range I want to average each week but I have
to insert a column so it moves over one if it isn't a dynmic range.

LastSix=OFFSET(Sheet2!AF2,ROW( ), COUNTA(Sheet2!2:2)-6,1,6)

My formula to average each cell in the dynamic column.

=IF(SUM(LastSix)=0,0,ROUNDUP(SUM(LastSix)/COUNTIF(LastSix,">0"),2))


This will be used to build a report from an Access download and as much as
can be dynamic the better.

tia,

 

[Jim Thomlinson]

As opposed to a Dynamic named range (which I recommended yesterday when I was
not thinking straight about dragging the formula) just use the formula
something like this

OFFSET(A2,0, COUNTA(2:2)-6,1,6)

=IF(SUM(OFFSET(A2,0, COUNTA(2:2)-6,1,6))=0,0,ROUNDUP(SUM(OFFSET(A2,0,
COUNTA(2:2)-6,1,6))/COUNTIF(OFFSET(A2,0, COUNTA(2:2)-6,1,6),">0"),2))

Things to note are:
First just get a simple sum working
=sum(OFFSET(A2,0, COUNTA(2:2)-6,1,6))
You may need to modify some of the numbers like the -6 and the A2. Place
this formula in a row other than 2.
I noticed you are referencing AF2... Is that were your data starts. If so
then you are going to need to play around with the offset formula quite a bit
to get it to work.

Finally Offset is a volatile funtion meaning that it is always calculated so
if you have hundreds or thousands of these formulas your performance is going
to suffer.