#Value! How do I get rid of it?

[Roger]

I have a formula in a column of cells, but when I drag it down I get
#value! in the cells where nothing is computed. I know there is a way
through conditional formatting or whatever to get rid of this, but
I've tried everything I can think of. Maybe it's iserror something or
other, but I just can't remember.

Sometimes this works in front of the formula:

=If(a1="","", [then rest of formula]

But this isn't working either.

Any suggestions,

Thanks

 

[RobW]

The #value! error appears when a formula tries to use inputs including
non-value items.
e.g. =a2+b2, where a2 is "hi there".

Can you check and fix the 'input' cells to remove non-value items?
This will flow through and fix the #value errors.

Otherwise, try =if(ISERROR(your formula),"",your formula).

 

[Roger]

The #value! error appears when a formula tries to use inputs including
non-value items.
e.g. =a2+b2, where a2 is "hi there".

Can you check and fix the 'input' cells to remove non-value items?
This will flow through and fix the #value errors.

Otherwise, try =if(ISERROR(your formula),"",your formula).

This is the formula:

=(($A$1*660)-K8*10)/L8

where a1=8.5
k8=nothing (not the word "nothing" but no value)
L8=nothing

When I insert =if(iserror into the formula I get #name?

=IF(iserror,(($A$1*660)-K8*10)/L8)

Thank you.

 

[Roger]

This is the formula:

=(($A$1*660)-K8*10)/L8

where a1=8.5
k8=nothing (not the word "nothing" but no value)
L8=nothing

When I insert =if(iserror into the formula I get #name?

=IF(iserror,(($A$1*660)-K8*10)/L8)

Thank you.

OK, good news. I just fixed it by doing the following:

=IF(L8="","",(($A$1*660)-K8*10)/L8)

I'd still be interested in knowing if you can ascertain why I got the
#value! error.

Thanks, again.

 

[Pete_UK]

You were trying to divide by L8, which you said was nothing. Your
amended formula has detected that. If you wanted the ISERROR function
to work, you should have done this:

=IF(ISERROR((($A$1*660)-K8*10)/L8),"",(($A$1*660)-K8*10)/L8)

Hope this helps.

Pete

 

[Dave Peterson]

If you select L8 and hit the delete key, does your original formula work?

I'm guessing that the cell wasn't really empty.

 

[Roger]

You were trying to divide by L8, which you said was nothing. Your
amended formula has detected that. If you wanted the ISERROR function
to work, you should have done this:

=IF(ISERROR((($A$1*660)-K8*10)/L8),"",(($A$1*660)-K8*10)/L8)

Hope this helps.

Pete

So you're saying that if there is a formula in the cell L8 then that
isn't really nothing? Or are you saying that you can't divide by 0?

If you select L8 and hit the delete key, does your original formula work?

I'm guessing that the cell wasn't really empty.

Same question I guess. There was a formula in L8, but there wasn't a
value because it was on a blank line.

Just trying to understand. Thanks for the help.

Roger