Averages formula/0
 

I have an entire column with #DIV/0! because it is awaiting data from
adjacent cells. The sum of the formulas result in a %.
Example:
B2 10%
B3 15%
B4 #DIV/0!
B5 #DIV/0!
B6 25%

QUESTION: How can I get the % average of B2:B6?

Averages formula/0
 

Try this:

Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just
ENTER):

=AVERAGE(IF(ISNUMBER(B2:B6),B2:B6))

Format as %

You'd be better off fixing the errors so that those formulas return a blank
("") rather than the #DIV/0! error. Then a simple =AVERAGE(B2:B6) will work.

Biff

"rldjda" wrote in message
...
I have an entire column with #DIV/0! because it is awaiting data from
adjacent cells. The sum of the formulas result in a %.
Example:
B2 10%
B3 15%
B4 #DIV/0!
B5 #DIV/0!
B6 25%

QUESTION: How can I get the % average of B2:B6?

Averages formula/0
 

=SUM(B2:B6)/(COUNTA(B2:B6)-COUNTBLANKS(B2:B6))

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"rldjda" wrote:

I have an entire column with #DIV/0! because it is awaiting data from
adjacent cells. The sum of the formulas result in a %.
Example:
B2 10%
B3 15%
B4 #DIV/0!
B5 #DIV/0!
B6 25%

QUESTION: How can I get the % average of B2:B6?

Averages formula/0
 

Put your functions in this
=IF(ISERROR(function),"",function)
--
-John Northwest11
Please rate when your question is answered to help us and others know what
is helpful.


"rldjda" wrote:

I have an entire column with #DIV/0! because it is awaiting data from
adjacent cells. The sum of the formulas result in a %.
Example:
B2 10%
B3 15%
B4 #DIV/0!
B5 #DIV/0!
B6 25%

QUESTION: How can I get the % average of B2:B6?

Averages formula/0
 

You could do that by using the formula

=AVERAGE(IF(ISNUMBER(B2:B6),B2:B6))

confirmed with CTRL+SHIFT+ENTER

althought it might be better to replace your #DIV/0! error with a blank,
allowing you to use just

=ABVERAGE(B2:B6)

If your formula in B2 is something like

=X3/B1

change to

=IF(B1,X3/B1,"")


"rldjda" wrote:

I have an entire column with #DIV/0! because it is awaiting data from
adjacent cells. The sum of the formulas result in a %.
Example:
B2 10%
B3 15%
B4 #DIV/0!
B5 #DIV/0!
B6 25%

QUESTION: How can I get the % average of B2:B6?

Averages formula/0
 

I don't get an error but the formula results with 0% and there are % values
entered in the column.

"T. Valko" wrote:

Try this:

Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just
ENTER):

=AVERAGE(IF(ISNUMBER(B2:B6),B2:B6))

Format as %

You'd be better off fixing the errors so that those formulas return a blank
("") rather than the #DIV/0! error. Then a simple =AVERAGE(B2:B6) will work.

Biff

"rldjda" wrote in message
...
I have an entire column with #DIV/0! because it is awaiting data from
adjacent cells. The sum of the formulas result in a %.
Example:
B2 10%
B3 15%
B4 #DIV/0!
B5 #DIV/0!
B6 25%

QUESTION: How can I get the % average of B2:B6?

Averages formula/0
 

The only thing that I can see that might be causing this is if your values
are actually smaller than you think they are and you have the format set in
a certain way. See this screencap:

http://img126.imageshack.us/img126/7095/averagemp3.jpg

The result in B8 is based on your posted example. The numbers in column C
are formatted as GENERAL.

Biff

"rldjda" wrote in message
...
I don't get an error but the formula results with 0% and there are % values
entered in the column.

"T. Valko" wrote:

Try this:

Entered as an array using the key combination of CTRL,SHIFT,ENTER (not
just
ENTER):

=AVERAGE(IF(ISNUMBER(B2:B6),B2:B6))

Format as %

You'd be better off fixing the errors so that those formulas return a
blank
("") rather than the #DIV/0! error. Then a simple =AVERAGE(B2:B6) will
work.

Biff

"rldjda" wrote in message
...
I have an entire column with #DIV/0! because it is awaiting data from
adjacent cells. The sum of the formulas result in a %.
Example:
B2 10%
B3 15%
B4 #DIV/0!
B5 #DIV/0!
B6 25%

QUESTION: How can I get the % average of B2:B6?

Averages formula/0
 

Just wanted to thank you all and tell anyone else looking who is dealing with
#DIV/0 the formula below is the one that works. Be certain to press
CTRL+SHIFT+ENTER once you've entered the formula and format that cell for
percentage.

"daddylonglegs" wrote:

You could do that by using the formula

=AVERAGE(IF(ISNUMBER(B2:B6),B2:B6))

confirmed with CTRL+SHIFT+ENTER

althought it might be better to replace your #DIV/0! error with a blank,
allowing you to use just

=ABVERAGE(B2:B6)

If your formula in B2 is something like

=X3/B1

change to

=IF(B1,X3/B1,"")


"rldjda" wrote:

I have an entire column with #DIV/0! because it is awaiting data from
adjacent cells. The sum of the formulas result in a %.
Example:
B2 10%
B3 15%
B4 #DIV/0!
B5 #DIV/0!
B6 25%

QUESTION: How can I get the % average of B2:B6?