Find Average w/ #DIV/0! in Cell
 

I am trying to find the average of cells if one or more of the cells has
#DIV/0!
Example: =average(Page1B1:Page2B1:Page3B1) Page 2B1 has #DIV/0! in the cell
because nothing has been entered in another cell where it captures its
information. So all I would need is the average of (Page1B1 and Page3B1).
The cell which shows #DIV/0! is subject to change all the time. Does this
make sense??!!

Find Average w/ #DIV/0! in Cell
 

Any chance you could change the formulas in the referenced cells to have an
if(dividend = 0,"",divisor/dividend)?

That would remove the #div/0 errors and allow the average formula to skip
your errored cell... Not sure the formula wil calc otherwise.

"Jacob" wrote:

I am trying to find the average of cells if one or more of the cells has
#DIV/0!
Example: =average(Page1B1:Page2B1:Page3B1) Page 2B1 has #DIV/0! in the cell
because nothing has been entered in another cell where it captures its
information. So all I would need is the average of (Page1B1 and Page3B1).
The cell which shows #DIV/0! is subject to change all the time. Does this
make sense??!!

Find Average w/ #DIV/0! in Cell
 

I am trying to do the same thing here is my formula how would i add yours to
it?
=SUM(B38:O38)/(COUNT(B38:O38)-COUNTIF(B38:O38,0)

"Sean Timmons" wrote:

Any chance you could change the formulas in the referenced cells to have an
if(dividend = 0,"",divisor/dividend)?

That would remove the #div/0 errors and allow the average formula to skip
your errored cell... Not sure the formula wil calc otherwise.

"Jacob" wrote:

I am trying to find the average of cells if one or more of the cells has
#DIV/0!
Example: =average(Page1B1:Page2B1:Page3B1) Page 2B1 has #DIV/0! in the cell
because nothing has been entered in another cell where it captures its
information. So all I would need is the average of (Page1B1 and Page3B1).
The cell which shows #DIV/0! is subject to change all the time. Does this
make sense??!!

Find Average w/ #DIV/0! in Cell
 

If I understand you correctly you are attempting to perform a mathematical
function on cells that are in themselves outputs of other formulas. In some
instances those formulas output a #DIV/0.

Use the ISERROR function combined with an IF statement to erase #DVI/0's.
They won't display anymore and formulas that include cells that formerly
evaluated to #DIV/0 will now calculate correctly...

"Jacob" wrote:

I am trying to find the average of cells if one or more of the cells has
#DIV/0!
Example: =average(Page1B1:Page2B1:Page3B1) Page 2B1 has #DIV/0! in the cell
because nothing has been entered in another cell where it captures its
information. So all I would need is the average of (Page1B1 and Page3B1).
The cell which shows #DIV/0! is subject to change all the time. Does this
make sense??!!

Find Average w/ #DIV/0! in Cell
 

Like so...

=IF(ISERROR(AVERAGE(Page1B1:Page2B1:Page3B1)),"",( AVERAGE(Page1B1:Page2B1:Page3B1)))

"UlvaZell" wrote:

If I understand you correctly you are attempting to perform a mathematical
function on cells that are in themselves outputs of other formulas. In some
instances those formulas output a #DIV/0.

Use the ISERROR function combined with an IF statement to erase #DVI/0's.
They won't display anymore and formulas that include cells that formerly
evaluated to #DIV/0 will now calculate correctly...

"Jacob" wrote:

I am trying to find the average of cells if one or more of the cells has
#DIV/0!
Example: =average(Page1B1:Page2B1:Page3B1) Page 2B1 has #DIV/0! in the cell
because nothing has been entered in another cell where it captures its
information. So all I would need is the average of (Page1B1 and Page3B1).
The cell which shows #DIV/0! is subject to change all the time. Does this
make sense??!!

Find Average w/ #DIV/0! in Cell
 

As pointed out in your other thread, you need to sort out your mismatched
parentheses before you go any further.
--
David Biddulph

"Matt" wrote in message
...
I am trying to do the same thing here is my formula how would i add yours
to
it?
=SUM(B38:O38)/(COUNT(B38:O38)-COUNTIF(B38:O38,0)

"Sean Timmons" wrote:

Any chance you could change the formulas in the referenced cells to have
an
if(dividend = 0,"",divisor/dividend)?

That would remove the #div/0 errors and allow the average formula to skip
your errored cell... Not sure the formula wil calc otherwise.

"Jacob" wrote:

I am trying to find the average of cells if one or more of the cells
has
#DIV/0!
Example: =average(Page1B1:Page2B1:Page3B1) Page 2B1 has #DIV/0! in the
cell
because nothing has been entered in another cell where it captures its
information. So all I would need is the average of (Page1B1 and
Page3B1).
The cell which shows #DIV/0! is subject to change all the time. Does
this
make sense??!!

Find Average w/ #DIV/0! in Cell
 

The cell is now blank
Here is my formula
=IF(ISERROR(AVERAGE(Monday!B2:Tuesday!B2:Wednesday !B2:Thursday!B2:Friday!B2:Saturday!B2)),"",(AVERAG E(Monday!B2:Tuesday!B2:Wednesday!B2:Thursday!B2:Fr iday!B2:Saturday!B2)))

The cells will show #DIV/0! if we have not gotten to that day in the week

"UlvaZell" wrote:

Like so...

=IF(ISERROR(AVERAGE(Page1B1:Page2B1:Page3B1)),"",( AVERAGE(Page1B1:Page2B1:Page3B1)))

"UlvaZell" wrote:

If I understand you correctly you are attempting to perform a mathematical
function on cells that are in themselves outputs of other formulas. In some
instances those formulas output a #DIV/0.

Use the ISERROR function combined with an IF statement to erase #DVI/0's.
They won't display anymore and formulas that include cells that formerly
evaluated to #DIV/0 will now calculate correctly...

"Jacob" wrote:

I am trying to find the average of cells if one or more of the cells has
#DIV/0!
Example: =average(Page1B1:Page2B1:Page3B1) Page 2B1 has #DIV/0! in the cell
because nothing has been entered in another cell where it captures its
information. So all I would need is the average of (Page1B1 and Page3B1).
The cell which shows #DIV/0! is subject to change all the time. Does this
make sense??!!