Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 . |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 . |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 . . |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 . . |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 . |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 . . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel: match two cells in one sheet to two cells in another and return a third cells value | Excel Worksheet Functions | |||
join data in multiple cells when adjoining cells match criteria ?? | Excel Worksheet Functions | |||
Cells won't change font color or show hi-lighted cells in document | Excel Discussion (Misc queries) | |||
Setting of input cells as blue font and formula cells as black fon | Excel Discussion (Misc queries) | |||
trying to create an (almost) circular formula between cells and data validated cells with lists | Excel Worksheet Functions |