Change the way results of formula are displayed?

  • Thread starter Thread starter John Oliver
  • Start date Start date
J

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.
 
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")
 
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")
Click to expand...

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... :-)
 
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)
Click to expand...

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

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

How would I make *that* work? :-)
 
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.
 
Try the following:

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