Home 
Search 
Today's Posts 
#1
Posted to microsoft.public.excel.worksheet.functions




Average formula where blank cells are counted as zeros
I am trying to write an average formula that takes into account the blank
cells. I had only cells A2, A4, A6 filled out, but when I do =average(A1:A6), it is not treating the blanks as zeros so the answer is much higher than it should be. Should I do a logic formula within the cells? 
#2




Answer: Average formula where blank cells are counted as zeros
Alternatively, you can use the AVERAGEIF function to achieve the same result without having to create a new column:
__________________
I am not human. I am an Excel Wizard 
#3
Posted to microsoft.public.excel.worksheet.functions




Average formula where blank cells are counted as zeros
If you know the range, you could do the division yourself:
=sum(a1:a6)/6 But this would include other nonnumeric cells in the count of cells (6), too. krwelling wrote: I am trying to write an average formula that takes into account the blank cells. I had only cells A2, A4, A6 filled out, but when I do =average(A1:A6), it is not treating the blanks as zeros so the answer is much higher than it should be. Should I do a logic formula within the cells?  Dave Peterson 
#4
Posted to microsoft.public.excel.worksheet.functions




Average formula where blank cells are counted as zeros
=AVERAGE(IF($A$2:$A$6<0,$A$2:$A$6)) AND HIT Ctrl+Shift+Enter to enter the
array formula. "krwelling" wrote: I am trying to write an average formula that takes into account the blank cells. I had only cells A2, A4, A6 filled out, but when I do =average(A1:A6), it is not treating the blanks as zeros so the answer is much higher than it should be. Should I do a logic formula within the cells? 
#5
Posted to microsoft.public.excel.worksheet.functions




Average formula where blank cells are counted as zeros
=AVERAGE(IF($A$2:$A$6<0,$A$2:$A$6))
I think the OP wants to include empty cells and evaluate them as numeric 0. Try one of these this array formulas** . This one assumes the range contains numbers or empty cells only. No TEXT, no formula blanks "". =AVERAGE(IF(A2:A6,A2:A6,A2:A6)) This one will account for (ignore) TEXT but will evaluate blank/empty cells as numeric 0. =AVERAGE(IF(ISNUMBER(A2:A6),A2:A6,IF(A2:A6="",0))) ** 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 "Nadine" wrote in message ... =AVERAGE(IF($A$2:$A$6<0,$A$2:$A$6)) AND HIT Ctrl+Shift+Enter to enter the array formula. "krwelling" wrote: I am trying to write an average formula that takes into account the blank cells. I had only cells A2, A4, A6 filled out, but when I do =average(A1:A6), it is not treating the blanks as zeros so the answer is much higher than it should be. Should I do a logic formula within the cells? 
#6
Posted to microsoft.public.excel.worksheet.functions




Average formula where blank cells are counted as zeros
Or, it might be as simple as:
=SUM(A2:A6)/ROWS(A2:A6) We'd need more details as to what the possible entries are in the range.  Biff Microsoft Excel MVP "T. Valko" wrote in message ... =AVERAGE(IF($A$2:$A$6<0,$A$2:$A$6)) I think the OP wants to include empty cells and evaluate them as numeric 0. Try one of these this array formulas** . This one assumes the range contains numbers or empty cells only. No TEXT, no formula blanks "". =AVERAGE(IF(A2:A6,A2:A6,A2:A6)) This one will account for (ignore) TEXT but will evaluate blank/empty cells as numeric 0. =AVERAGE(IF(ISNUMBER(A2:A6),A2:A6,IF(A2:A6="",0))) ** 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 "Nadine" wrote in message ... =AVERAGE(IF($A$2:$A$6<0,$A$2:$A$6)) AND HIT Ctrl+Shift+Enter to enter the array formula. "krwelling" wrote: I am trying to write an average formula that takes into account the blank cells. I had only cells A2, A4, A6 filled out, but when I do =average(A1:A6), it is not treating the blanks as zeros so the answer is much higher than it should be. Should I do a logic formula within the cells? 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Help! Need to ignore 0s and blank cells in average formula!  Excel Discussion (Misc queries)  
How do I set an average to not count blank cells as zeros?  Excel Worksheet Functions  
Average formula Counting zero's How do I get just the numers counted.  Excel Discussion (Misc queries)  
Average formula Counting zero's How do I get just the numers counted.  Excel Discussion (Misc queries)  
Average Function (include Blank Cells and Zeros)  Excel Discussion (Misc queries) 