Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Averaging columns
Frank, do you know if there's a way to average a column and make the average
exclude the zero values? "Frank Kabel" wrote: Hi use AVERAGE for this. It ignores empty cells -- Regards Frank Kabel Frankfurt, Germany "GarrettSocling" schrieb im Newsbeitrag ... Hello! I made a spreadsheet where data can be entered into 8 columns. In each column for each day, this data has a formula run on it (=(E16-E$15)/E$15, for example). Then sum of these calculations are taken and averaged (=SUM(C17:J17)/8). The problem is, towards the bottom of this spreadsheet there won't be 8 columns of data anymore, but I still want a valid average generated. Maybe after a week or two, there is only 7 columns of data, and after three weeks only 6 columns of data available. The amount of rows into the spreadsheet that this lack of data happens is constantly different. I have the framework for the entire spreadsheet created as a template so that only the data needs to be entered. Is there a command which will dynamically average whatever data is available? (=SUM(C17:J17)/8) doesn't cut it :( TIA! G -- GarrettSocling --------------------------------------------------------------------- --- GarrettSocling's Profile: http://www.excelforum.com/member.php...o&userid=16226 View this thread: http://www.excelforum.com/showthread...hreadid=276391 |
#2
|
|||
|
|||
Hi
use the array formula (entered with CTRL+SHIFT+ENTER): =AVERAGE(IF(A1:A100<0,A1:A100)) -- Regards Frank Kabel Frankfurt, Germany rhodesv wrote: Frank, do you know if there's a way to average a column and make the average exclude the zero values? "Frank Kabel" wrote: Hi use AVERAGE for this. It ignores empty cells -- Regards Frank Kabel Frankfurt, Germany "GarrettSocling" schrieb im Newsbeitrag ... Hello! I made a spreadsheet where data can be entered into 8 columns. In each column for each day, this data has a formula run on it (=(E16-E$15)/E$15, for example). Then sum of these calculations are taken and averaged (=SUM(C17:J17)/8). The problem is, towards the bottom of this spreadsheet there won't be 8 columns of data anymore, but I still want a valid average generated. Maybe after a week or two, there is only 7 columns of data, and after three weeks only 6 columns of data available. The amount of rows into the spreadsheet that this lack of data happens is constantly different. I have the framework for the entire spreadsheet created as a template so that only the data needs to be entered. Is there a command which will dynamically average whatever data is available? (=SUM(C17:J17)/8) doesn't cut it :( TIA! G -- GarrettSocling --------------------------------------------------------------------- --- GarrettSocling's Profile: http://www.excelforum.com/member.php...o&userid=16226 View this thread: http://www.excelforum.com/showthread...hreadid=276391 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to increase maximum number of columns in excel 2003 | Excel Discussion (Misc queries) | |||
Columns in Excel will not allow user to click in them | Excel Discussion (Misc queries) | |||
Counting the Contents of Two Columns | Excel Discussion (Misc queries) | |||
Pivot Table Creating New Columns that Subtract Two Existing Columns | Excel Discussion (Misc queries) | |||
interchange columns with rows | Excel Discussion (Misc queries) |