Nesting ISERROR and ROUND

  • Thread starter Thread starter Roger
  • Start date Start date
R

Roger

I am attempting to use ISERROR and ROUND in the same
formula. I do not want the #DIV/0! error to show when I'm
dividing by zero because some cells will not always have a
value, and they are used in a sum statement lower in the
spreadsheet. My formula currently is: =IF(ISERROR((D9/
$D$20*$D$22)+D9),"-",((D9/$D$20*$D$22)+D9)). If I try to
add rounding to the formula, I get a message that there is
an error in the formula. I've tried: =IF(ISERROR(=ROUND
((D9/$D$20*$D$22)+D9,2)),"-",((D9/$D$20*$D$22)+D9)). Am I
doing something wrong, or can ISERROR and ROUND not be
combined? Thanks.
 
Hi
try
=IF($D$20+$D$22=0,"-",ROUND((D9/$D$20*$D$22)+D9,2))

note: Returning "-" could lead to problems in further calculation
depending on the used functions.
 
You can use ISERROR and ROUND together, but there's no reason to. You'll
only get the #DIV/0 error if $D$20 = 0, so try:

=IF($D$20=0,"-",ROUND(D9*($D$22/$D$20+1),2))
 
Hi Roger,

Try this

=IF(ISERROR((D9/$D$20*$D$22)+D9),"-",(ROUND((D9/$D$20*$D$22)+D9,2)))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Frank -

that still gives a #Div/0 error if $D$20 = 0 and $D$22<>0.

See my reply with a similar formula.
 
But that doesn't give what the OP asked for:

(D9/$D$20*$D$22)+D9

is equivalent to

((D9/$D$20)*$D$22)+D9

not

(D9/($D$20*$D$22))+D9

so

=IF($D$20*$D22=0,"-",ROUND((D9/$D$20*$D$22)+D9,2))

will produce "-" if $D22=0, when it should produce D9, rounded to 2
decimal places.

I am, of course, assuming that the OP got the original formula correct.
 
Hi JE
I see, beer+wine+posting to the NG is NOT a good combination :-)
Somehow I saw additional brackets in the OP's original formula. but
looking again at his posting - they've vanished....

Wish you a nice evening

Frank
 
Frank Kabel said:
I see, beer+wine+posting to the NG is NOT a good combination :-)
Somehow I saw additional brackets in the OP's original formula. but
looking again at his posting - they've vanished....
Click to expand...

Know how that goes - though I try not to mix the beer and the wine... :-)
 
Back
Top