Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
sum to recognize new rows
I have a column that I average the sum (ignoring blank cells in the formula)
sum of column, divided by count of cells that are not blank. here is the formula that is in B10... =(SUM(B4:B9))/COUNTA(B4:B9) When I insert a row above B10 the formula does not change to incorporate that additional row. How can I get the formula to recognize new rows? should now be =(SUM(B4:B10))/COUNTA(B4:B10) Thanks -- deb |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
sum to recognize new rows
you could copy row 10 and paste to row 11, and then type your new values over
those in row 10 "deb" wrote: I have a column that I average the sum (ignoring blank cells in the formula) sum of column, divided by count of cells that are not blank. here is the formula that is in B10... =(SUM(B4:B9))/COUNTA(B4:B9) When I insert a row above B10 the formula does not change to incorporate that additional row. How can I get the formula to recognize new rows? should now be =(SUM(B4:B10))/COUNTA(B4:B10) Thanks -- deb |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
sum to recognize new rows
....forgot to mention you would need to also add $ signs to b4 (b$4)
"joemeshuggah" wrote: you could copy row 10 and paste to row 11, and then type your new values over those in row 10 "deb" wrote: I have a column that I average the sum (ignoring blank cells in the formula) sum of column, divided by count of cells that are not blank. here is the formula that is in B10... =(SUM(B4:B9))/COUNTA(B4:B9) When I insert a row above B10 the formula does not change to incorporate that additional row. How can I get the formula to recognize new rows? should now be =(SUM(B4:B10))/COUNTA(B4:B10) Thanks -- deb |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
sum to recognize new rows
hi
you could create a dynamic range. 2003 on the menu barInsertnamedefine in the name in workbook box, enter a name for the range say myname. then in the refers to box enter... =OFFSET($B$1,0,0,MATCH(99999999,$B:$B),1) click the add button the ok out. then on the sheet use this formula... =sum(myrange)/Counta(myrange) the range will adjust as you add rows (or delete rows) regards FSt1 "deb" wrote: I have a column that I average the sum (ignoring blank cells in the formula) sum of column, divided by count of cells that are not blank. here is the formula that is in B10... =(SUM(B4:B9))/COUNTA(B4:B9) When I insert a row above B10 the formula does not change to incorporate that additional row. How can I get the formula to recognize new rows? should now be =(SUM(B4:B10))/COUNTA(B4:B10) Thanks -- deb |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
need formula that recognize | Excel Discussion (Misc queries) | |||
Pivot Table Wizard Fails to Recognize New Rows of a Named Range | Excel Worksheet Functions | |||
Pivot Table Wizard Fails to Recognize New Rows of Database | Excel Discussion (Misc queries) | |||
Recognize #div/0! | Excel Discussion (Misc queries) | |||
recognize the cell | Excel Discussion (Misc queries) |