Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default 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   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up Answer: Average formula where blank cells are counted as zeros

  1. In a new column, say column B, enter the following formula in cell B1: =IF(ISBLANK(A1),0,A1)
  2. Drag the formula down to fill the rest of the cells in column B.
  3. Now, use the AVERAGE function on the range B1:B6 instead of A1:A6. This will treat the blank cells as zeros and give you the correct average.

Alternatively, you can use the AVERAGEIF function to achieve the same result without having to create a new column:
  1. Use the following formula: =AVERAGEIF(A1:A6,"<")
  2. This formula will only consider cells that are not blank in the range A1:A6 and treat the blank cells as zeros.
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 35,218
Default 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 non-numeric 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 216
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Help! Need to ignore 0s and blank cells in average formula! sandres74 Excel Discussion (Misc queries) 2 January 7th 10 11:34 AM
How do I set an average to not count blank cells as zeros? athenia_1999 Excel Worksheet Functions 5 January 12th 08 04:34 AM
Average formula Counting zero's How do I get just the numers counted. [email protected] Excel Discussion (Misc queries) 1 March 2nd 07 03:56 PM
Average formula Counting zero's How do I get just the numers counted. [email protected] Excel Discussion (Misc queries) 2 March 2nd 07 04:11 AM
Average Function (include Blank Cells and Zeros) [email protected] Excel Discussion (Misc queries) 17 June 27th 06 01:33 PM


All times are GMT +1. The time now is 11:16 PM.

Powered by vBulletin® Copyright ©2000 - 2023, Jelsoft Enterprises Ltd.
Copyright 2004-2023 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"