Averages that include cells with IF statements
 

Is there no way around this? I have five numbers I want the average for.
But they are not always greater than zero. To avoid DIV/0 errors those
numbers have relatively long IF arguments. But that makes the Average -which
also has IF arguments- exclude values of zero. I see that they are not
actually values but not sure what to do about it. I am new to such complex
authoring of functions and am at my whit's end on this. Someone please
help!!!

Some info:
Cells I want to average contain:
=IF(ISERROR(IF(RC[-2]<=0,"0",RC[-1]/RC[-2])),"0",RC[-1]/RC[-2])
Formula I am using to find the average:
=IF(ISERROR(AVERAGE(R[-5]C:R[-1]C)),"0",AVERAGE(R[-5]C:R[-1]C))

For individual numbers you are using zero's to avoid the error message.
However AVERAGE() has been programmed to include zeros in its computation.
AVERAGE() will exclude blanks. Try to have the IF() insert blanks rather
than "0".

Good Luck
--
Gary''s Student


"hollenbaker" wrote:

Is there no way around this? I have five numbers I want the average for.
But they are not always greater than zero. To avoid DIV/0 errors those
numbers have relatively long IF arguments. But that makes the Average -which
also has IF arguments- exclude values of zero. I see that they are not
actually values but not sure what to do about it. I am new to such complex
authoring of functions and am at my whit's end on this. Someone please
help!!!

Some info:
Cells I want to average contain:
=IF(ISERROR(IF(RC[-2]<=0,"0",RC[-1]/RC[-2])),"0",RC[-1]/RC[-2])
Formula I am using to find the average:
=IF(ISERROR(AVERAGE(R[-5]C:R[-1]C)),"0",AVERAGE(R[-5]C:R[-1]C))

That is not going to work. I WANT the zeros included it the average. When I
view the spread sheet, I see zeros in the cells, however it seems that the
formula sees the other formula rather than the value that is displayed.

These are production numbers and time spent with no production is still time
on the clock.

"Gary''s Student" wrote:

For individual numbers you are using zero's to avoid the error message.
However AVERAGE() has been programmed to include zeros in its computation.
AVERAGE() will exclude blanks. Try to have the IF() insert blanks rather
than "0".

Good Luck
--
Gary''s Student


"hollenbaker" wrote:

Is there no way around this? I have five numbers I want the average for.
But they are not always greater than zero. To avoid DIV/0 errors those
numbers have relatively long IF arguments. But that makes the Average -which
also has IF arguments- exclude values of zero. I see that they are not
actually values but not sure what to do about it. I am new to such complex
authoring of functions and am at my whit's end on this. Someone please
help!!!

Some info:
Cells I want to average contain:
=IF(ISERROR(IF(RC[-2]<=0,"0",RC[-1]/RC[-2])),"0",RC[-1]/RC[-2])
Formula I am using to find the average:
=IF(ISERROR(AVERAGE(R[-5]C:R[-1]C)),"0",AVERAGE(R[-5]C:R[-1]C))

See if this helps. Drop the quotes from around the 0's (...,0,...).
With the quotes in place, the IF function returns the TEXT "0" which
AVERAGE ignores because it's text. Without the quotes, the IF function
will return the number 0 which AVERAGE will acknowledge and include in
your average.


--
MrShorty
------------------------------------------------------------------------
MrShorty's Profile: http://www.excelforum.com/member.php...o&userid=22181
View this thread: http://www.excelforum.com/showthread...hreadid=468863

YOU ROCK!!

So simple yet sooo far from reach. Days saved. Thank you.

"MrShorty" wrote:


See if this helps. Drop the quotes from around the 0's (...,0,...).
With the quotes in place, the IF function returns the TEXT "0" which
AVERAGE ignores because it's text. Without the quotes, the IF function
will return the number 0 which AVERAGE will acknowledge and include in
your average.


--
MrShorty
------------------------------------------------------------------------
MrShorty's Profile: http://www.excelforum.com/member.php...o&userid=22181
View this thread: http://www.excelforum.com/showthread...hreadid=468863

I have a similiar problem whereas the last part of my formula is not
averaging but summing my formula: I have
{=average)if(iserr(j10:l10<0),0,average(j10:l10)) )}

what was working was the {=average(if(j10:l10<0,j10:l10,""))} but what i
get in cells with 0 is the #div/ error.

"MrShorty" wrote:


See if this helps. Drop the quotes from around the 0's (...,0,...).
With the quotes in place, the IF function returns the TEXT "0" which
AVERAGE ignores because it's text. Without the quotes, the IF function
will return the number 0 which AVERAGE will acknowledge and include in
your average.


--
MrShorty
------------------------------------------------------------------------
MrShorty's Profile: http://www.excelforum.com/member.php...o&userid=22181
View this thread: http://www.excelforum.com/showthread...hreadid=468863