How do I add the last 10 cells

[ianbon]

If I have a column labelled, say, temperature, that is often being added
to, how do I add up the last 10 cells. I want excel to do it for me
automatically. I want a formula that will find the last filled-in (non
blank) cell in a column and then add up the 10 entries above that.

 

[Ken Wright]

Assuming your data starts in A10, then in A9 perhaps, try the following:-

=SUM(OFFSET($A$10,COUNT(A10:A9995)-10,,10))

If your data started in A15, formula would be:-

=SUM(OFFSET($A$15,COUNT(A15:A10000)-10,,10))

This assumes you have no other data below this range in that column (Or at least within the COUNT
range).

 

[Ken Wright]

Also assumes you have no blanks in your data anywhere in that range

 

[Don Guillett]

try this to sum the last 10 in col E regardless of blanks.
NOT an array formula

=SUM(OFFSET($E$1,MATCH(999999999,E:E)-10,,10))

 

[Ken Wright]

Hi Bob - Did you just pinch our sunshine?? We were sunny till about an hour ago - Probably headed
over your way :-(

I just tried again with different data, threw random numbers into A15:A45, tried putting the
formula into a number of different cells and it seems to work fine. IF however, I put it in a
cell, say B20, and then copy B20 and paste it into a cell in a lower numbered row, It will bomb,
because Excel tries to make the A1 relative to the original cell, which obviously it can't.
Fixing the A1 as absolute helps in that respect though.

I couldn't get the example you gave to work. I did try a tweak on that approach though and came
up with the following:-

=SUM(OFFSET(INDIRECT("A"&LARGE((($A$1:$A$10000<>"")*ROW($A$1:$A$10000)),1)),,,-10)) array entered

 

[Ken Wright]

Works for me. Neater than mine too.

 

[Don Guillett]

Thank you, kind sir.

Works for me. Neater than mine too.

 

[Ken Wright]

LOL - In fact Don, I'm quite happy to admit that yours is much smaller than mine <vbg>

 

[Don Guillett]

Well now. Us Texans don't take kindly to that.
It's not the size you know, it's the quality that counts.

 

[Ken Wright]

rotflmao

--
Regards
Ken.......................

Well now. Us Texans don't take kindly to that.
It's not the size you know, it's the quality that counts.

 

[Tom Ogilvy]

Aladin Akyurek who, I believe introduced this technique a while back,
usually uses the largest number a cell can store to insure the function
works correctly for any number in the column

An example of one of his postings:
=INDEX(E:E,MATCH(9.99999999999999E+307,E:E)-1)

so Don's formula would be modified

=SUM(OFFSET($E$1,MATCH(9.99999999999999E+307,E:E)-10,,10))

 

[Ron Rosenfeld]

If I have a column labelled, say, temperature, that is often being added
to, how do I add up the last 10 cells. I want excel to do it for me
automatically. I want a formula that will find the last filled-in (non
blank) cell in a column and then add up the 10 entries above that.

Well, here's another way that will add the last ten numeric entries in column
A. It will skip non-numeric entries (and blanks)

Array-enter:

=SUM(TRANSPOSE(OFFSET(A1,(LARGE(ROW(A1:A65535)*ISNUMBER(A1:A65535),ROW(INDIRECT("1:10"))))-1,0)))

To array-enter a formula, hold down <ctrl><shift> while hitting <enter>. XL
will place braces {...} around the formula.



--ron

 

[ianbon]

thanks heaps guys! I'm overwhelmed by the work you've put into it. Ian