If statement problem - #div/0!

[jgold20]

[TABLE="width: 598"]
<colgroup><col width="91" style="width: 68pt;"><col width="99" style="width: 74pt;"><col width="89" style="width: 67pt;"><col width="99" style="width: 74pt;"><col width="123" style="width: 92pt;"><col width="97" style="width: 73pt;"></colgroup><tbody>[TR]
[TD="class: xl68, width: 91, align: center"]Cubic feet[/TD]
[TD="class: xl73, width: 99, align: center"] Filing or Scheduling & CC Fees[/TD]
[TD="class: xl71, width: 89, align: center"]Minimum Move[/TD]
[TD="class: xl73, width: 99, align: center"]Company Code[/TD]
[TD="class: xl73, width: 123, align: center"]Enter Minimum Move Amount [/TD]
[TD="class: xl75, width: 97, align: center"]Minimum [FONT=&quot]Price [/FONT]per[FONT=&quot]CF [/FONT][/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ACB9CA]#ACB9CA[/URL] , align: center"] [/TD]
[TD="class: xl70, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ACB9CA]#ACB9CA[/URL] , align: center"]YES[/TD]
[TD="class: xl72, width: 89, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ACB9CA]#ACB9CA[/URL] , align: center"]YES[/TD]
[TD="class: xl76, width: 99, align: center"]L[/TD]
[TD="class: xl74, width: 123, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ACB9CA]#ACB9CA[/URL] , align: center"]500[/TD]
[TD="class: xl77, align: center"]#DIV/0![/TD]
[/TR]
[TR]
[TD="class: xl66, align: center"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl66, align: center"][/TD]
[TD="class: xl76, width: 99, align: center"]N[/TD]
[TD="class: xl74, width: 123, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ACB9CA]#ACB9CA[/URL] , align: center"] [/TD]
[TD="class: xl77, align: center"]#DIV/0![/TD]
[/TR]
[TR]
[TD="class: xl65"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl66, align: center"][/TD]
[TD="class: xl76, width: 99, align: center"]NO[/TD]
[TD="class: xl74, width: 123, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ACB9CA]#ACB9CA[/URL] , align: center"] [/TD]
[TD="class: xl77, align: center"]#DIV/0![/TD]
[/TR]
[TR]
[TD="class: xl65"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl66, align: center"][/TD]
[TD="class: xl76, width: 99, align: center"] [/TD]
[TD="class: xl74, width: 123, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ACB9CA]#ACB9CA[/URL] , align: center"] [/TD]
[TD="class: xl77, align: center"]#DIV/0![/TD]
[/TR]
</tbody>[/TABLE]
In the above example, when cubic feet is 0, it returns a #div/0! error. The statement I am using is =IF(G3="YES",(I3/E3),0) for minimum move. If I use the drop down and change minimum move to NO, it returns a 0. I have tried everything I could think of and still receive the #div/0!. Is there any way to avoid the error. I understand why it is happening

 

[AlanY]

you can use iferror() function like this

=iferror(IF(G3="YES",(I3/E3),0),"")

 

[jgold20]

1)It works for the first occurrence. It does not work for the next 3
2)Is there a way to keep the accounting format instead of the cell being blank?
3) It does not work for the next 3 cells, It returns a blank cell:
=IFERROR(IF(G3="YES",(I3/E4),0),"")
=IFERROR(IF(G3="YES",(I3/E5),0),"")
=IFERROR(IF(G3="YES",(I3/E6),0),"")

 

[AlanY]

this is what I got from your data in post#1

Excel 2013/2016
	E	F	G	H	I	J
2	Cubic feet	Filing or Scheduling & CC Fees	Minimum Move	Company Code	Enter Minimum Move Amount	MinimumPriceperCF
3		YES	YES	L	500
4				N		0
5				NO		0
6						0
Sheet1

Cell Formulas
Range	Formula
J3	=IFERROR(IF(G3="YES",(I3/E3),0),"")

would you post your data/formula produced blanks in J4 to J6 so that we can have a look

 

[jgold20]

[TABLE="width: 1163"]
<colgroup><col width="93" style="width: 70pt;"><col width="103" style="width: 77pt;"><col width="100" style="width: 75pt;"><col width="105" style="width: 79pt;"><col width="107" style="width: 80pt;"><col width="312" style="width: 234pt;"><col width="343" style="width: 257pt;"></colgroup><tbody>[TR]
[TD="class: xl69, width: 93, align: center"]Cubic feet[/TD]
[TD="class: xl74, width: 103, align: center"] Filing or Scheduling & CC Fees[/TD]
[TD="class: xl72, width: 100, align: center"]Minimum Move[/TD]
[TD="class: xl74, width: 105, align: center"]Company Code[/TD]
[TD="class: xl74, width: 107, align: center"]Enter Minimum Move Amount [/TD]
[TD="class: xl75, width: 312, align: center"]Minimum [FONT=&quot]Price [/FONT]per[FONT=&quot] CF [/FONT][/TD]
[TD="width: 343"][/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ACB9CA]#ACB9CA[/URL] , align: center"]200[/TD]
[TD="class: xl71, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ACB9CA]#ACB9CA[/URL] , align: center"]YES[/TD]
[TD="class: xl73, width: 100, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ACB9CA]#ACB9CA[/URL] , align: center"]YES[/TD]
[TD="class: xl76, width: 105, align: center"]H[/TD]
[TD="class: xl78, width: 107, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ACB9CA]#ACB9CA[/URL] , align: center"] $ 400.00 [/TD]
[TD="class: xl77, align: center"] $ 2.00 [/TD]
[TD="class: xl77, align: center"] =IFERROR(IF(O3="YES",(Q3/M3),0),"") [/TD]
[/TR]
[TR]
[TD="class: xl67, align: center"][/TD]
[TD="class: xl68"][/TD]
[TD="class: xl67, align: center"][/TD]
[TD="class: xl76, width: 105, align: center"]L[/TD]
[TD="class: xl78, width: 107, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ACB9CA]#ACB9CA[/URL] , align: center"] $ 1,200.00 [/TD]
[TD="class: xl77, align: center"] [/TD]
[TD="class: xl77, align: center"] =IFERROR(IF(O3="YES",(Q3/M4),0),"") [/TD]
[/TR]
[TR]
[TD="class: xl66"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl67, align: center"][/TD]
[TD="class: xl76, width: 105, align: center"]N[/TD]
[TD="class: xl78, width: 107, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ACB9CA]#ACB9CA[/URL] , align: center"] $ 1,111.00 [/TD]
[TD="class: xl77, align: center"] [/TD]
[TD="class: xl77, align: center"] =IFERROR(IF(O3="YES",(Q3/M5),0),"") [/TD]
[/TR]
[TR]
[TD="class: xl66"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl67, align: center"][/TD]
[TD="class: xl76, width: 105, align: center"]R[/TD]
[TD="class: xl78, width: 107, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ACB9CA]#ACB9CA[/URL] , align: center"] $ 1,200.00 [/TD]
[TD="class: xl77, align: center"] [/TD]
[TD="class: xl77, align: center"] =IFERROR(IF(O3="YES",(Q3/M6),0),"") [/TD]
[/TR]
</tbody>[/TABLE]

 

[AlanY]

this is what I got with accounting format

Excel 2013/2016
	M	N	O	P	Q	R
2	Cubic feet	Filing or Scheduling & CC Fees	Minimum Move	Company Code	Enter Minimum Move Amount	MinimumPriceperCF
3	200	YES	YES	H	$400.00	$2.00
4				L	$1,200.00	$0.00
5				N	$1,111.00	$0.00
6				R	$1,200.00	$0.00
Sheet4

Cell Formulas
Range	Formula
R3	=IFERROR(IF(O3="YES",(Q3/M3),0),"")

 

[jgold20]

1)R3 - R6 are defined as accounting
2) as you can see from post #5 , I have a blank field. In addition, my main concern is that the calculation is not being done and the criteria is being met - there is a value in M & Q therefore R4 - R6 should return a dollar amount

 

[AlanY]

do you mean this?

Excel 2013/2016
	M	N	O	P	Q	R
2	Cubic feet	Filing or Scheduling & CC Fees	Minimum Move	Company Code	Enter Minimum Move Amount	MinimumPriceperCF
3	200	YES	YES	H	$400.00	$2.00
4				L	$1,200.00	$6.00
5				N	$1,111.00	$5.56
6				R	$1,200.00	$6.00
Sheet4

Cell Formulas
Range	Formula
R3	=IFERROR(IF($O$3="YES",(Q3/$M$3),0),"")

 

[Joe4]

Your issue is when E3 is zero.
So try this:

[COLOR=#333333]=IF(AND(G3="YES",E3<>0),I3/E3,0)[/COLOR]

 

[jgold20]

Exactly. What cell would the if statement go in?