Hello,

I need the result of a cell to show zero or blank if the result of my
formula is an error.

The easy way to do it is like tihs, which generally works fine:

=IF(ISERROR(formula),"",formula)

My problem is that my "formulas" sometimes include the RAND() function, so
the evaluation of the first part is not necessarily the same as the second
part. Also, for long formulas, it gets cumbersome and leads to more errors
having to enter them twice.

Is there a simplified way to achieve this, without having to type the
formula twice, and without having to use an extra cell?

Thanks!

There is not an easier way that I know of, but what I do is type the formula
first to make sure it works, then add the if(iserror( and shift end ctl+c to
copy the formula i typed, add the ,"", and ctl+v to paste formula )) and
thats it, i NEVER retype known working code/functions too much oppurtunity
for error as you mentioned.
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Idoia" wrote:

Hello,

I need the result of a cell to show zero or blank if the result of my
formula is an error.

The easy way to do it is like tihs, which generally works fine:

=IF(ISERROR(formula),"",formula)

My problem is that my "formulas" sometimes include the RAND() function, so
the evaluation of the first part is not necessarily the same as the second
part. Also, for long formulas, it gets cumbersome and leads to more errors
having to enter them twice.

Is there a simplified way to achieve this, without having to type the
formula twice, and without having to use an extra cell?

Thanks!

Not sure there's a way around problems associated with having RAND or
RANDBETWEEN in a formula other than the other cell usage.

However, to you can save yourself some typing and reduce chance of typos by
using copy and paste. Short example, you want a formula to end up like this:
=IF(ISERR(VLOOKUP(A5,B1:D100,4,0)),"",VLOOKUP(A5,B 1:D100,4,0))
when you get to this point:
=IF(ISERR(VLOOKUP(A5,B1:D100,4,0))
highlight and copy VLOOKUP(A5,B1:D100,4,0)
then continue with the ,"", portion and at that point, paste the VLOOKUP in
it and finish up the formula with the closing )

You can use similar strategies on complex nested formulas. Lets say you had
something else to do in the False case of that formula, and maybe it's
another IF statement with more choices. I sometimes will just leave a place
holder there, as
,"false ops", and go into another cell and build up the formula for the
actions to take if that case. Once that's done and I know it's working, I'll
copy that formula without the = sign and paste it into the proper place in
the real formula. You can do this to whatever level of complexity is
required. It helps prevent confusion during the initial creation of the
long, complex formula. However, it doesn't guarantee you will understand
what you did when you return to try to alter it 6 months from now <g.
"Idoia" wrote:

Hello,

I need the result of a cell to show zero or blank if the result of my
formula is an error.

The easy way to do it is like tihs, which generally works fine:

=IF(ISERROR(formula),"",formula)

My problem is that my "formulas" sometimes include the RAND() function, so
the evaluation of the first part is not necessarily the same as the second
part. Also, for long formulas, it gets cumbersome and leads to more errors
having to enter them twice.

Is there a simplified way to achieve this, without having to type the
formula twice, and without having to use an extra cell?

Thanks!

Thanks to both of you. Copy and paste is what I usually do when creating the
formula, but I go mad when I have to review or change really long formulas.
It also does not solve the RAND() issue. I was hoping someone had come up
with a more straightforward way!!

"Idoia" wrote:

Hello,

I need the result of a cell to show zero or blank if the result of my
formula is an error.

The easy way to do it is like tihs, which generally works fine:

=IF(ISERROR(formula),"",formula)

My problem is that my "formulas" sometimes include the RAND() function, so
the evaluation of the first part is not necessarily the same as the second
part. Also, for long formulas, it gets cumbersome and leads to more errors
having to enter them twice.

Is there a simplified way to achieve this, without having to type the
formula twice, and without having to use an extra cell?

Thanks!