Sum the last 21 cells
 

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

Sum the last 21 cells
 

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

--
Biff
Microsoft Excel MVP


"David" wrote in message
...
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

Sum the last 21 cells
 

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 numbers
=SUM(LOOKUP(LARGE(IF(ISNUMBER(A1:A100),ROW(A1:A100 )),ROW(1:5)),ROW(A1:A100),A1:A100))

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


If this post helps click Yes
---------------
Jacob Skaria


"David" wrote:

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

Sum the last 21 cells
 

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" wrote in message
...
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

Sum the last 21 cells
 

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


"Jacob Skaria" wrote:

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 numbers
=SUM(LOOKUP(LARGE(IF(ISNUMBER(A1:A100),ROW(A1:A100 )),ROW(1:5)),ROW(A1:A100),A1:A100))

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


If this post helps click Yes
---------------
Jacob Skaria


"David" wrote:

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

Sum the last 21 cells
 

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

"T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP


"David" wrote in message
...
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


.

Sum the last 21 cells
 

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

"Ashish Mathur" wrote:

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" wrote in message
...
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

Sum the last 21 cells
 

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)),RO W(1:5)),COLUMN(1:1),1:1))

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



If this post helps click Yes
---------------
Jacob Skaria


"David" wrote:

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


"Jacob Skaria" wrote:

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 numbers
=SUM(LOOKUP(LARGE(IF(ISNUMBER(A1:A100),ROW(A1:A100 )),ROW(1:5)),ROW(A1:A100),A1:A100))

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


If this post helps click Yes
---------------
Jacob Skaria


"David" wrote:

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

Sum the last 21 cells
 

Biff wasn't asking whether there will always be 21 *consecutive* cells with
a number in them, and it is obvious from your original question that there
won't.
What he was asking was whether there will always be 21 cells (not
necessarily consecutive) with a number in them.
He was presumably concerned that a potential solution might keep looking
back further, and that if there were not 21 cells in total with numbers his
solution might try to look further up beyond row 1, and thus get something
like a #REF! error, which would be my guess as to why he asked the question.
--
David Biddulph

David wrote:
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

"T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP


"David" wrote in message
...
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


.

Sum the last 21 cells
 

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(A I11:AI271)),ROW(1:5)),COLUMN(AI11:AI271),AI11:AI27 1))

It provides a value but it is incorrect?



"Jacob Skaria" wrote:

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)),RO W(1:5)),COLUMN(1:1),1:1))

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



If this post helps click Yes
---------------
Jacob Skaria


"David" wrote:

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


"Jacob Skaria" wrote:

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 numbers
=SUM(LOOKUP(LARGE(IF(ISNUMBER(A1:A100),ROW(A1:A100 )),ROW(1:5)),ROW(A1:A100),A1:A100))

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


If this post helps click Yes
---------------
Jacob Skaria


"David" wrote:

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

Sum the last 21 cells
 

Ahh, I see

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

Sorry for the misunderstanding.

Cheers

"David Biddulph" wrote:

Biff wasn't asking whether there will always be 21 *consecutive* cells with
a number in them, and it is obvious from your original question that there
won't.
What he was asking was whether there will always be 21 cells (not
necessarily consecutive) with a number in them.
He was presumably concerned that a potential solution might keep looking
back further, and that if there were not 21 cells in total with numbers his
solution might try to look further up beyond row 1, and thus get something
like a #REF! error, which would be my guess as to why he asked the question.
--
David Biddulph

David wrote:
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

"T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP


"David" wrote in message
...
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


.


.

Sum the last 21 cells
 

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
---------------
Jacob Skaria


"David" wrote:

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(A I11:AI271)),ROW(1:5)),COLUMN(AI11:AI271),AI11:AI27 1))

It provides a value but it is incorrect?



"Jacob Skaria" wrote:

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)),RO W(1:5)),COLUMN(1:1),1:1))

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



If this post helps click Yes
---------------
Jacob Skaria


"David" wrote:

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


"Jacob Skaria" wrote:

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 numbers
=SUM(LOOKUP(LARGE(IF(ISNUMBER(A1:A100),ROW(A1:A100 )),ROW(1:5)),ROW(A1:A100),A1:A100))

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


If this post helps click Yes
---------------
Jacob Skaria


"David" wrote:

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

Sum the last 21 cells
 

Thanks Jacob, that's brilliant!

Cheers

"Jacob Skaria" wrote:

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
---------------
Jacob Skaria


"David" wrote:

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(A I11:AI271)),ROW(1:5)),COLUMN(AI11:AI271),AI11:AI27 1))

It provides a value but it is incorrect?



"Jacob Skaria" wrote:

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)),RO W(1:5)),COLUMN(1:1),1:1))

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



If this post helps click Yes
---------------
Jacob Skaria


"David" wrote:

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


"Jacob Skaria" wrote:

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 numbers
=SUM(LOOKUP(LARGE(IF(ISNUMBER(A1:A100),ROW(A1:A100 )),ROW(1:5)),ROW(A1:A100),A1:A100))

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


If this post helps click Yes
---------------
Jacob Skaria


"David" wrote:

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

Sum the last 21 cells
 

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:AI2 71)*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" wrote in message
...
Ahh, I see

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

Sorry for the misunderstanding.

Cheers

"David Biddulph" wrote:

Biff wasn't asking whether there will always be 21 *consecutive* cells
with
a number in them, and it is obvious from your original question that
there
won't.
What he was asking was whether there will always be 21 cells (not
necessarily consecutive) with a number in them.
He was presumably concerned that a potential solution might keep looking
back further, and that if there were not 21 cells in total with numbers
his
solution might try to look further up beyond row 1, and thus get
something
like a #REF! error, which would be my guess as to why he asked the
question.
--
David Biddulph

David wrote:
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

"T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP


"David" wrote in message
...
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


.


.

Sum the last 21 cells
 

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

And you would be correct!

--
Biff
Microsoft Excel MVP


"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
Biff wasn't asking whether there will always be 21 *consecutive* cells
with a number in them, and it is obvious from your original question that
there won't.
What he was asking was whether there will always be 21 cells (not
necessarily consecutive) with a number in them.
He was presumably concerned that a potential solution might keep looking
back further, and that if there were not 21 cells in total with numbers
his solution might try to look further up beyond row 1, and thus get
something like a #REF! error, which would be my guess as to why he asked
the question.
--
David Biddulph

David wrote:
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

"T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP


"David" wrote in message
...
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


.

Sum the last 21 cells
 

Thanks Biff I will give it a go

Cheers

"T. Valko" wrote:

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:AI2 71)*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" wrote in message
...
Ahh, I see

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

Sorry for the misunderstanding.

Cheers

"David Biddulph" wrote:

Biff wasn't asking whether there will always be 21 *consecutive* cells
with
a number in them, and it is obvious from your original question that
there
won't.
What he was asking was whether there will always be 21 cells (not
necessarily consecutive) with a number in them.
He was presumably concerned that a potential solution might keep looking
back further, and that if there were not 21 cells in total with numbers
his
solution might try to look further up beyond row 1, and thus get
something
like a #REF! error, which would be my guess as to why he asked the
question.
--
David Biddulph

David wrote:
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

"T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP


"David" wrote in message
...
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


.


.



.