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

 

[Joe4]

Or in your later scenario:

=IF($M$3=0,"",IF(O3="YES",Q3/$M$3,0))

Personally, I like to zero in on the specific cause for the error (division by zero) instead of using IFERROR, when possible, as IFERROR will catch/sweep/hide other errors too, and you may want to know about those.

 

[jgold20]

Works perfectly. Ty

 

[AlanY]

lets start again, what are you expecting in R4 to R6?

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	?
5				N	$1,111.00	?
6				R	$1,200.00	?
Sheet4

 

[jgold20]

Thanks again #11

 

[jgold20]

My mistake #9 , I used your solution. I have another question, how can I populate a 2nd cell with the result of the if statement? I cannot put a formula in the 2nd cell, in case G3=NO, then I have to manually enter a number

 

[Joe4]

how can I populate a 2nd cell with the result of the if statement? I cannot put a formula in the 2nd cell, in case G3=NO, then I have to manually enter a number

Formulas can only return values to the cell they reside in. They cannot return values to other cells.
Any cell can either have a formula or a hard-coded value, but never BOTH at the same time.
What you want to do will require VBA. Are you open to a VBA solution?

 

[jgold20]

Yes, I am running VBA to clear all the cells

 

[Joe4]

We may be able to use a Worksheet_Change event procedure, which is code that runs automatically when a certain range is manually updated.

What cells is being manually updated to trigger this whole process to run?
What column is your formula in?
What is the second column that you would like the value copied into?

 

[jgold20]

1) Drop-down box in G3 if YES
2) Formula - J3 - J6
3) Second column - E9 - E12

 

[Joe4]

2) Formula - J3 - J6
3) Second column - E9 - E12

So, is the relationship between the two such J3 maps to E9, J4 mapes to E10, etc?