How to get rid of #Value!

I have this formula typed in for a cell. It gives me the result I
need, but when there's no input the #Value! shows up. How would I
change up the formula so the errors don't show?

=IF(B22<0,B22,IF(B23-B22,"$0.00",B23-B22))

cheers,

day

Hi, check to see if the values in B23 & B22 are text. I would also take
a look at your embeded if statement: 'IF(B23-B22'.

HTH--Lonnie M.

I think you'll only get that error if either of the cells is not blank or
numeric. If either cell has a space or an alphabetic character, you'll get
the error.

So check the contents first

Maybe:

=IF(AND(ISNUMBER(B22),ISNUMBER(B23)),IF(B22<0,B22,IF(B23-B22,"$0.00",B23-B22)),"Problem")

Regards

Trevor


"day" <[email protected]> wrote in message
news:[email protected]...
>I have this formula typed in for a cell. It gives me the result I
> need, but when there's no input the #Value! shows up. How would I
> change up the formula so the errors don't show?
>
> =IF(B22<0,B22,IF(B23-B22,"$0.00",B23-B22))
>
> cheers,
>
> day
>

I checked the cells and they are both set for currency. So, I tried
Trevor's formula and it worked great. Thanks guys... I never knew that
Excel can be so intriguing. I love it.

day

Whoops... now it won't subtract at all, it just shows"problem" It
seems like it's not reading the second nested problem. How can I
change it so it does?

cheers

day

It's because your nested IF doesn't make sense.

You have IF(B23-B22,"$0.00",B23-B22)

It's the first B23-B22 that's causing the problem

Did you intend something like: IF(B23<B22,"$0.00",B23-B22) ?

or IF(B23-B22<0,"$0.00",B23-B22) ?

So never mind.. I think I solved it for the mean time. I used the
following only b/c I don't know how to use the other ISNUM, AND OR....
=IF(B6=-(B9),B6,IF(B6<0, (B9+B6),IF(B6=0,"$0.00",(B9*B7)-B6)))

this is fun

If all that you want to do is remove the error from your vision then:

=IFERROR((your original formula here),"")

Whatever you would like to see if there is an error, be it 0 or nothing at all, type that between the quotations. This also works with #DIV/0! errors.