Thanks:  0
Likes:  0

# Thread: Help editing formula to remove #VALUE error

1. ## Help editing formula to remove #VALUE error

Hi all -

I have a formula which I recently had to change and now when some of the fields aren't filled it is giving me a #VALUE error. I suspect that is because in some cases it is trying to divide by nothing because a user hasn't added that value in there.

Can someone help me integrate an additional (ISERROR) function into the formula I have below so that if there is an error it will just leave the cell blank? I just can't seem to get it right. Obviously the field it is dividing by is in column K

2. ## Re: Help editing formula to remove #VALUE error

I am not going to try to make heads or tails over what your formula is trying to do, but will show you a simple example of how you can avoid the division by zero error.

Let's say you simply want to divide two numbers (a1/b1) but if the divisor (b1) is zero/blank, you want to return nothing instead of an error. You can do that like this:

=IF(b1=0,"",a1/b1)

You should be able to incorporate the same sort of logic in your formula.

3. ## Re: Help editing formula to remove #VALUE error

Originally Posted by Joe4
I am not going to try to make heads or tails over what your formula is trying to do, but will show you a simple example of how you can avoid the division by zero error.

Let's say you simply want to divide two numbers (a1/b1) but if the divisor (b1) is zero/blank, you want to return nothing instead of an error. You can do that like this:

=IF(b1=0,"",a1/b1)

You should be able to incorporate the same sort of logic in your formula.
Thanks Joe. I think what I am struggling with is incorporating that IF function inside my formula since it already contains another IF (in the form of the IF(ISERROR)... can you help with putting it in there?

Try:

5. ## Re: Help editing formula to remove #VALUE error

Originally Posted by Robert Mika
Try:
Boom goes the dynamite! Thanks Buddy!

6. ## Re: Help editing formula to remove #VALUE error

I have a formula which when no data is input in the row gives me a #VALUE error.

Can anyone help me integrate an additional (ISERROR) function into the formula I have below so that if there is an error it will just leave the cell blank? I just can't seem to get it right. the cell is blank when there is nothing to lookup in that row to our master sheet with all the content info.

=IF(B11="","",VLOOKUP(B11,'K:\Excel\[Catnic Master File.XLS]WS'!\$B\$15:\$R\$5000,6,FALSE))*C11

thanks so much for reading.

Kind regards,
Grant

7. ## Re: Help editing formula to remove #VALUE error

Grant,
I see you also asked the question over here and got it answered: http://www.mrexcel.com/forum/showthread.php?t=624752

Please do not post the same question multiple times. Per forum rule #9 here, posts of a duplicate nature will typically be locked or deleted: http://www.mrexcel.com/forum/showthread.php?t=99490

Thanks

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•