Remember Me?

#1
Posted to microsoft.public.excel.worksheet.functions
 external usenet poster Posts: 1
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
 Excel Super Guru Posts: 1,867
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
Posted to microsoft.public.excel.worksheet.functions
 external usenet poster Posts: 35,218
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
Posted to microsoft.public.excel.worksheet.functions
 external usenet poster Posts: 216
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
 external usenet poster Posts: 15,768
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

...
=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
 external usenet poster Posts: 15,768
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

...
=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?

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post sandres74 Excel Discussion (Misc queries) 2 January 7th 10 10:34 AM athenia_1999 Excel Worksheet Functions 5 January 12th 08 03:34 AM [email protected] Excel Discussion (Misc queries) 1 March 2nd 07 02:56 PM [email protected] Excel Discussion (Misc queries) 2 March 2nd 07 03:11 AM [email protected] Excel Discussion (Misc queries) 17 June 27th 06 01:33 PM

All times are GMT +1. The time now is 08:57 PM.