Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Get "AVERAGE" function to ignore empty cells
Excel 2007.
I have 3 columns of data where the daily amount over the month is posted by a formula relating to another sheet in the same book. Below each column I want to display the daily Average of the data in that column. When the data was typed into the columns (cells) the "AVERAGE" function worked okay. However, now that the data is called up via a formula, the "AVERAGE" calculates across all 31 cells in the column, including those with no data yet posted. Can someone tell me how I get the "AVERAGE" function to ignore the empty cells. TIA for any help. Ed |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Get "AVERAGE" function to ignore empty cells
Your issue will be that the formulas return zero and they are affecting your
calculation. There are a couple of formulas that will work. 1. =AVERAGE(IF(A1:A7<0, A1:A7)) This is an array formula and as such it must be commited with Shift+Ctrl+<Enter as opposed to just <Enter 2. =SUMIF(A1:A7, "<0")/COUNTIF(A1:A7, "<0") -- HTH... Jim Thomlinson "Ed O'Brien" wrote: Excel 2007. I have 3 columns of data where the daily amount over the month is posted by a formula relating to another sheet in the same book. Below each column I want to display the daily Average of the data in that column. When the data was typed into the columns (cells) the "AVERAGE" function worked okay. However, now that the data is called up via a formula, the "AVERAGE" calculates across all 31 cells in the column, including those with no data yet posted. Can someone tell me how I get the "AVERAGE" function to ignore the empty cells. TIA for any help. Ed |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Get "AVERAGE" function to ignore empty cells
how I get the "AVERAGE" function to
ignore the empty cells. Average ignores empty cells. You haven't said how these empty cells are affecting your average? Could it be that your feeder formulas return 0 but you have the cells set to not display 0 values? -- Biff Microsoft Excel MVP "Ed O'Brien" wrote in message ... Excel 2007. I have 3 columns of data where the daily amount over the month is posted by a formula relating to another sheet in the same book. Below each column I want to display the daily Average of the data in that column. When the data was typed into the columns (cells) the "AVERAGE" function worked okay. However, now that the data is called up via a formula, the "AVERAGE" calculates across all 31 cells in the column, including those with no data yet posted. Can someone tell me how I get the "AVERAGE" function to ignore the empty cells. TIA for any help. Ed |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Get "AVERAGE" function to ignore empty cells
The function is dividing by 31 regardless of how many cells have data. If
only 2 cells have data, say 30 in total, Average shows 0.97 while the real average is 15.0. "T. Valko" wrote in message ... how I get the "AVERAGE" function to ignore the empty cells. Average ignores empty cells. You haven't said how these empty cells are affecting your average? Could it be that your feeder formulas return 0 but you have the cells set to not display 0 values? -- Biff Microsoft Excel MVP "Ed O'Brien" wrote in message ... Excel 2007. I have 3 columns of data where the daily amount over the month is posted by a formula relating to another sheet in the same book. Below each column I want to display the daily Average of the data in that column. When the data was typed into the columns (cells) the "AVERAGE" function worked okay. However, now that the data is called up via a formula, the "AVERAGE" calculates across all 31 cells in the column, including those with no data yet posted. Can someone tell me how I get the "AVERAGE" function to ignore the empty cells. TIA for any help. Ed |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Get "AVERAGE" function to ignore empty cells
Hi, Jim.
I chose your formula number 2, being a little easier, I think, and it works fine. Thank you very much. I did not try your fomula number 1, although I do understand it. It's always good to know there is help out there! Best wishes, Ed "Jim Thomlinson" wrote in message ... Your issue will be that the formulas return zero and they are affecting your calculation. There are a couple of formulas that will work. 1. =AVERAGE(IF(A1:A7<0, A1:A7)) This is an array formula and as such it must be commited with Shift+Ctrl+<Enter as opposed to just <Enter 2. =SUMIF(A1:A7, "<0")/COUNTIF(A1:A7, "<0") -- HTH... Jim Thomlinson "Ed O'Brien" wrote: Excel 2007. I have 3 columns of data where the daily amount over the month is posted by a formula relating to another sheet in the same book. Below each column I want to display the daily Average of the data in that column. When the data was typed into the columns (cells) the "AVERAGE" function worked okay. However, now that the data is called up via a formula, the "AVERAGE" calculates across all 31 cells in the column, including those with no data yet posted. Can someone tell me how I get the "AVERAGE" function to ignore the empty cells. TIA for any help. Ed |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Get "AVERAGE" function to ignore empty cells
Ok, that means the cells aren't empty. They contain 0s.
I see that Jim gave you a suggestion that will account for that but, is 0 an otherwise valid entry? 5 0 3 2 0 0 0 0 Is the first 0 a valid entry? -- Biff Microsoft Excel MVP "Ed O'Brien" wrote in message ... The function is dividing by 31 regardless of how many cells have data. If only 2 cells have data, say 30 in total, Average shows 0.97 while the real average is 15.0. "T. Valko" wrote in message ... how I get the "AVERAGE" function to ignore the empty cells. Average ignores empty cells. You haven't said how these empty cells are affecting your average? Could it be that your feeder formulas return 0 but you have the cells set to not display 0 values? -- Biff Microsoft Excel MVP "Ed O'Brien" wrote in message ... Excel 2007. I have 3 columns of data where the daily amount over the month is posted by a formula relating to another sheet in the same book. Below each column I want to display the daily Average of the data in that column. When the data was typed into the columns (cells) the "AVERAGE" function worked okay. However, now that the data is called up via a formula, the "AVERAGE" calculates across all 31 cells in the column, including those with no data yet posted. Can someone tell me how I get the "AVERAGE" function to ignore the empty cells. TIA for any help. Ed |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Get "AVERAGE" function to ignore empty cells
The short answer is , no. The data called up by the formula in each cell is
always a positive - number higher than 0. Yet... even if a zero were a valid entry, while the denominator remains as the number of days past, it would make no difference. My aim was to have the denominator represent only the number of days past. I hope I make sense. Thanks for the input. Best wishes, Ed "T. Valko" wrote in message ... Ok, that means the cells aren't empty. They contain 0s. I see that Jim gave you a suggestion that will account for that but, is 0 an otherwise valid entry? 5 0 3 2 0 0 0 0 Is the first 0 a valid entry? -- Biff Microsoft Excel MVP "Ed O'Brien" wrote in message ... The function is dividing by 31 regardless of how many cells have data. If only 2 cells have data, say 30 in total, Average shows 0.97 while the real average is 15.0. "T. Valko" wrote in message ... how I get the "AVERAGE" function to ignore the empty cells. Average ignores empty cells. You haven't said how these empty cells are affecting your average? Could it be that your feeder formulas return 0 but you have the cells set to not display 0 values? -- Biff Microsoft Excel MVP "Ed O'Brien" wrote in message ... Excel 2007. I have 3 columns of data where the daily amount over the month is posted by a formula relating to another sheet in the same book. Below each column I want to display the daily Average of the data in that column. When the data was typed into the columns (cells) the "AVERAGE" function worked okay. However, now that the data is called up via a formula, the "AVERAGE" calculates across all 31 cells in the column, including those with no data yet posted. Can someone tell me how I get the "AVERAGE" function to ignore the empty cells. TIA for any help. Ed |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I get Excel to ignore "a", "an", "the" when sorting? | Excel Discussion (Misc queries) | |||
How do I ignore "#N/A" data in a column while calculating average | Excel Worksheet Functions | |||
Cannot find "Plot empty cells as" option in 2007 | Charts and Charting in Excel | |||
syntax for "IF" commend to check for multiple empty cells bf comma | Excel Worksheet Functions | |||
"Empty" values in holiday parameter for NETWORKDAYS() function | Excel Worksheet Functions |