Sum the last 21 cells

[David]

Hi

I am after the formula that will allow me to sum the values of the last 21
cells in a column that have a value. As some cells are blank, at times the
formula will have to count back more than 21 cells, sometimes only 21.

Is there a way other than manully changing the sum formula to increase the
range of cells covered whenever a blank cell occurs?

Thanks

 

[T. Valko]

Will there *always* be 21 cells with numbers in them? If not, what should
happen?

 

[Jacob Skaria]

Please note that this is an array formula. You create array formulas in the
same way that you create other formulas, except you press CTRL+SHIFT+ENTER to
enter the formula. If successful in 'Formula Bar' you can notice the curly
braces at both ends like "{=<formula>}"

'sum of last 5 number
=SUM(LOOKUP(LARGE(IF(ISNUMBER(A1:A100),ROW(A1:A100)),ROW(1:5)),ROW(A1:A100),A1:A100))

'sum of last 21 number
=SUM(LOOKUP(LARGE(IF(ISNUMBER(A1:A100),ROW(A1:A100)),ROW(1:21)),ROW(A1:A100),A1:A100))


If this post helps click Yes

 

[Ashish Mathur]

Hi,

You can also try this. Say you have 101-103 in A6:A8 and then 104-105 in
A11-A12. In cell B6, use =COUNTA($A$6:$A$12)-COUNT($A$5:A5) and copy down
till B12. In cell B15, use =SUMPRODUCT((B6:B12<=4)*(A6:A12)) to sum the
last 4 numbers

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[David]

Hi Jacob
For the formula bar is that F2 followed by CTRL+SHIFT+ENTER?

Also, am I correct is assuming that this formula seeks to sum the last 21
rows in rows A1:A100 that have a value in them - ie if rows80-100 all have
values then it would sum them all. If say row 99 had no value in it, then it
would sum rows 79:100, being 21 cells with a value?

If I wanted to do this in a column, would I simply replace the word Row in
the formula with column?

ta

 

[David]

Hi

No there will not always be 21 consecutive cells with a number in them. To
get the last 21 consecutive numbers you may need to count back more than 21
cells ie if 3 blank cells then will need to could back 24, 5 blank cells
then count back 26 and so on.

I am trying to sum the last 21 numeric values entered in cells of a column
where the column has some cells without any value entered. These cells should
be ignored.

What I am attempting to do is get the total of these 21 values and via the
count function, get an average ie. average value of last 21 occurrences,
excluding balnk cells

Cheers

 

[Jacob Skaria]

Hi David

Press F2. Copy formula to the cell. Instead of enter Ctrl+Shift+Enter

'to sum last 5 numbers in the first row
=SUM(LOOKUP(LARGE(IF(ISNUMBER(1:1),COLUMN(1:1)),ROW(1:5)),COLUMN(1:1),1:1))

'to sum last 5 numbers in the range A1:J
=SUM(LOOKUP(LARGE(IF(ISNUMBER(A1:J1),COLUMN(A1:J1)),ROW(1:5)),COLUMN(A1:J1),A1:J1))



If this post helps click Yes

 

[David]

Thanks Ashish

The column I am attempting to sum has data added to it daily, so I was after
a formula that was dynamic. That is, it would do the calculation without me
having to highlight what cells to reference. The formula would look back, see
a cell has no value, ignore it and go onto the next one.

Cheers

 

[David]

Hi Jacob
Can't seem to get this to work

Below is the formula with the actuial cell references in it that I am trying
to calualte, in case the error is with my transcribing (the column is Column
AI, the relevant rows 11 to 271, not all of which cells have numeric entries
). I am after the sum of the last 21 cells from row 271 back that have a
numeric value.

=SUM(LOOKUP(LARGE(IF(ISNUMBER(AI11:AI271),COLUMN(AI11:AI271)),ROW(1:5)),COLUMN(AI11:AI271),AI11:AI271))

It provides a value but it is incorrect?

 

[David]

Ahh, I see

Yes there will always be 21 cells with numeric references in them.

Sorry for the misunderstanding.

Cheers

 

[Jacob Skaria]

Should have been..
=SUM(LOOKUP(LARGE(IF(ISNUMBER(AI11:AI271),ROW(AI11:AI271)),ROW(1:5)),ROW(AI11:AI271),AI11:AI271))

If this post helps click Yes

 

[David]

Thanks Jacob, that's brilliant!

Cheers

Should have been...
=SUM(LOOKUP(LARGE(IF(ISNUMBER(AI11:AI271),ROW(AI11:AI271)),ROW(1:5)),ROW(AI11:AI271),AI11:AI271))

If this post helps click Yes

 

[T. Valko]

Yes there will always be 21 cells with numeric

references in them.
OK

What I am attempting to do is get the total of these
21 values and via the count function, get an average
ie. average value of last 21 occurrences, excluding
balnk cells

Ok, so you want to AVERAGE the last 21 numbers...

the column is Column AI, the relevant rows 11 to 271,

Array entered** :

=AVERAGE(AI271:INDEX(AI11:AI271,LARGE(ROW(AI11:AI271)*ISNUMBER(AI11:AI271),21)-ROW(AI11)+1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

 

[T. Valko]

which would be my guess as to why he asked the question.

And you would be correct!

 

[RagDyer]

Since the ultimate goal of your project is to *average* the last 21 cells
that contain a number, you might try this *array* formula which will
accomplish that task in *one fell swoop*:

=AVERAGE(INDEX(AI11:AI271,LARGE(ROW(1:261)*(AI11:AI271<>""),21)):AI271)
--
Array formulas must be entered with CSE, <Ctrl> <Shift > <Enter>, instead of
the regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, you must use CSE when
revising the formula.

FWIW, another comment on this formula:

If you have *less* then 21 numbers entered, it *will* average the numbers
that are present,
meaning it will work on 1 to 21 numbers, stopping at the *last* 21 numbers
entered.

ALSO, if the possibility exists that there might be text entries in addition
to numbers entered in Column AI,

use this *array* formula instead:

=AVERAGE(INDEX(AI11:AI271,LARGE(ROW(1:261)*(ISNUMBER(AI11:AI271)),21)):AI271)

Don't forget the CSE array entry !

 

[David]

Thanks Biff I will give it a go

Cheers

Ok, so you want to AVERAGE the last 21 numbers...


Array entered** :

=AVERAGE(AI271:INDEX(AI11:AI271,LARGE(ROW(AI11:AI271)*ISNUMBER(AI11:AI271),21)-ROW(AI11)+1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.


--
Biff
Microsoft Excel MVP





.

 

[David]

Thanks for thia

I will give it a try

Cheers

Since the ultimate goal of your project is to *average* the last 21 cells
that contain a number, you might try this *array* formula which will
accomplish that task in *one fell swoop*:

=AVERAGE(INDEX(AI11:AI271,LARGE(ROW(1:261)*(AI11:AI271<>""),21)):AI271)
--
Array formulas must be entered with CSE, <Ctrl> <Shift > <Enter>, instead of
the regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, you must use CSE when
revising the formula.

FWIW, another comment on this formula:

If you have *less* then 21 numbers entered, it *will* average the numbers
that are present,
meaning it will work on 1 to 21 numbers, stopping at the *last* 21 numbers
entered.

ALSO, if the possibility exists that there might be text entries in addition
to numbers entered in Column AI,

use this *array* formula instead:

=AVERAGE(INDEX(AI11:AI271,LARGE(ROW(1:261)*(ISNUMBER(AI11:AI271)),21)):AI271)

Don't forget the CSE array entry !

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------




.

 

[RagDyeR]

Didn't see Biff's formula when I posted mine, but they're both doing the
same thing, although their forms are slightly different.

Thanks for the feed-back.
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

Thanks for thia

I will give it a try

Cheers