Change the way results of formula are displayed?

[John Oliver]

I have several pages in a spreadsheet that have values calculated by a
formula. Some of those cell contents are used in other places. My
problem is that, if there is no data for the formula to use, it displays
#DIV/0! I would like to have it display 12:00:00 AM in the cell, so I
see 0:00:00 in the synopsis.

 

[Mark]

Have a look at the ISERR function. It will return a value
of True if your formula is going to be #Div/0 or False if
it is going to return a normal value. You will also need
to combine this with an IF statement.

Example

=1/0 (result is #DIV/0)
=ISERR(1/0) )result is True
=IF(ISERR(1/0)=TRUE,"ERROR","OK") (result is "Error")
=IF(ISERR(1/1)=TRUE,"ERROR","OK") (result is "OK")

 

[John Oliver]

Have a look at the ISERR function. It will return a value
of True if your formula is going to be #Div/0 or False if
it is going to return a normal value. You will also need
to combine this with an IF statement.

Example

=1/0 (result is #DIV/0)
=ISERR(1/0) )result is True
=IF(ISERR(1/0)=TRUE,"ERROR","OK") (result is "Error")
=IF(ISERR(1/1)=TRUE,"ERROR","OK") (result is "OK")

Is the ISERR function a VBA thing? If so, does it matter where or how
it's entered? If not, is all of that put in one long string in the
cell? The help page for the IS functions is assuming quite a bit on my
part...

 

[John Oliver]

The final step is to use an IF statement, so that when an
error is detected by the ISERR function you can specify
the value to be displayed.

In Cell C1 enter a new formula
=IF(ISERR(A1/B1)=TRUE,0,A1/B1)

My value is an "array enter" (surrounded with { } )

=AVERAGE(IF(F4:F27<>0,F4:F27))

How would I make *that* work?

 

[Dave Peterson]

one way:

=AVERAGE(IF(ISERROR(F4:F27),"",IF((F4:F27)<>0,F4:F27)))
(still ctrl-shift-enter)

notice that I used iserror().

There are a few different built in error checks.

=iserror() is the most generic. (=iserr() skips #n/a).

You can read more about it in help.

 

[Laura Cook]

Try the following:

=IF(ISERROR(AVERAGE(IF(F4:F27<>0,F4:F27))),0,AVERAGE(
IF(F4:F27<>0,F4:F27)))

 

[John Oliver]

Try the following:

=IF(ISERROR(AVERAGE(IF(F4:F27<>0,F4:F27))),0,AVERAGE(
IF(F4:F27<>0,F4:F27)))

Worked like a charm! Thanks, Laura...

 

[Laura Cook]

You're welcome, glad I could help.