A Simple Sum?

[Domigos]

I have a spreadsheet with long columns of numbers, and I really want a way of having a single formula in a cell at the top of each column which sums the last six items in the column. Sounds simple doesn't it - but it is really doing my head in at the moment.

I'm sure that one of you clever guys out there has come across this before - any ideas that would point me in the right direction?

 

[Aladin Akyurek]

On 2002-10-24 11:02, Domigos wrote:
I have a spreadsheet with long columns of numbers, and I really want a way of having a single formula in a cell at the top of each column which sums the last six items in the column. Sounds simple doesn't it - but it is really doing my head in at the moment.

I'm sure that one of you clever guys out there has come across this before - any ideas that would point me in the right direction?

(1.) Activate Insert|Name|Define.
(2.) Enter BigNum in the Names in Workbook box.
(3.) Enter the following in the Refers to box:

9.99999999999999E+307

(4.) Click OK.

Let A be the first column whose last 6 items you want to sum...

In A1 enter & copy across:

=SUM(OFFSET(A2,MATCH(BigNum,A:A)-CELL("Row",A2),0,-6,1))

 

[eliW]

Hi Domigos,

This also can do it:

=SUM(OFFSET(A2:A65536,COUNT(A2:A65536)-6,0,6,1))

Eli

 

[Domigos]

Aladin, you really are the dogs doo daa's when it comes to this stuff - what can I say, your elegant solution works like a dream.....

Have now moved on a bit with the project and have added a second column that gives me a running total alongside the original column (using an IF Formula) - I now have the problem of trying to get your excellent formula to work when there is a whole series of zeros beneath the figures I am interested in! Have not succeeded yet - but I am still working away at it. It would appear this is not quite as easy as I thought it might be! (There's an understatement if ever there was one).

Many sincere thanks for your efforts on my behalf.

Domigos