Nested Formula help

[Ray]

Hello -

I have a sheet which contains data in two columns, E40:F92. Column F
contains Last Years data (so all rows have data) ... Column E is this
year's data so rows in the 'future' evaluate to #N/A. I need to
develop a 4 week pace, comparing this year to last year. Here's a
formula I did for a similar application that worked well, but the
future weeks were totally blank:

=(SUM(OFFSET($E$40,COUNTA($E$40:$E$92)-1,0,1,1):OFFSET($E$40,COUNTA($E
$40:$E$92)-4,0,1,1))/SUM(OFFSET($e$40,COUNTA($e$40:$e
$92)-1,-3,1,1):OFFSET($e$40,COUNTA($e$40:$e$92)-4,-3,1,1)))-1

I'll make it more confusing -- the last available week of this year
(Col E) should be discarded. For example, the data today looks like
this:
Non-Zero data exists in E40:E80 (E81:E92=#N/A)
E80 should be 'thrown out', with the 4wk pace being compared on:
Sum(E76:E79)/Sum(F76:F79)

The 'offset' formulas above are used to build the range, with the
'counta' formulas used to calc the number of non-blank cells ... this
is where the #N/A cells become the problem.

Can anyone please help to modify this formula so it does what I need
it to? I also need to do an 8 week pace ...

TIA,
Ray

 

[T. Valko]

Try replacing COUNTA with COUNT. COUNT ignores everything but numbers.

Biff