OFFSET, ADDRESS and all that jazz...

Posted by Mark on April 19, 2001 6:36 AM

Column B is a varying number of cells containing numbers, with new entries being added below each other, and I'm trying to total the contents of the 10 entries at the bottom of the column

Currently B1 to B97 have entries, so =SUM(OFFSET(B97,0,0,-10,1)) gives me the correct answer (i.e. B88+B89+B90+B91+B92+B93+B94+B95+B96+B97).

=ADDRESS(COUNT(B1:B65500),2) returns $B$97 correctly, so shouldn't =SUM(OFFSET(ADDRESS(COUNT(B1:B65500),2),0,0,-10,1)) give the total of the bottom ten of the numbers in the cells, regardless of the number of numbers?

I'm obviously missing something important.

Thanks in advance

Posted by Mark W. on April 19, 2001 6:45 AM

ADDRESS() returns a text value. OFFSET() requires
a reference as it's 1st argument.

Posted by Aladin Akyurek on April 19, 2001 7:57 AM

A question

Mark

Do your last 10 entries never contain one or more blanks?

Aladin

Posted by Dave Hawley on April 20, 2001 4:26 AM

Hi Mark

You need to nest the INDIRECT function in with it like:

=SUM(OFFSET(INDIRECT(ADDRESS(COUNT(B1:B65500),2)),0,0,-10,1))

Dave

OzGrid Business Applications