If statement problem - #div/0!

[jgold20]

Cubic feet	Filing or Scheduling & CC Fees	Minimum Move	Company Code	Enter Minimum Move Amount	Minimum [FONT=&quot]Price [/FONT]per[FONT=&quot]CF [/FONT]
	YES	YES	L	500	#DIV/0!
			N		#DIV/0!
			NO		#DIV/0!
					#DIV/0!

<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>
</tbody>

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]

Cubic feet	Filing or Scheduling & CC Fees	Minimum Move	Company Code	Enter Minimum Move Amount	Minimum [FONT=&quot]Price [/FONT]per[FONT=&quot] CF [/FONT]
200	YES	YES	H	$ 400.00	$ 2.00	=IFERROR(IF(O3="YES",(Q3/M3),0),"")
			L	$ 1,200.00		=IFERROR(IF(O3="YES",(Q3/M4),0),"")
			N	$ 1,111.00		=IFERROR(IF(O3="YES",(Q3/M5),0),"")
			R	$ 1,200.00		=IFERROR(IF(O3="YES",(Q3/M6),0),"")

<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>
</tbody>

 

[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?