Help editing formula to remove #VALUE error

mick0005

Active Member
Joined
Feb 21, 2011
Messages
406
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

=IF(ISBLANK(I4),"",SUMIFS(Rest,Date,">="&C4,Date,"<="&D4,Product,E4,Adcoop,H4,DC,I4))/K4
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
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.
 
Upvote 0
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?
 
Upvote 0
Try:
=IF(ISBLANK(I4),"",IF(ISERROR(SUMIFS(Rest,Date,">="&C4,Date,"<="&D4,product,E4,Adcoop,H4,DC,I4)/K4),"",SUMIFS(Rest,Date,">="&C4,Date,"<="&D4,product,E4,Adcoop,H4,DC,I4)/K4))
 
Upvote 0
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 <!-- / message -->
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,288
Members
448,563
Latest member
MushtaqAli

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top