Nested if formula?

[Bob32103]

I’ve got an excel file containing freight detail and I’d like to construct the following formula:


[TABLE="width: 640"]
<colgroup><col width="64" span="10" style="width: 48pt;"></colgroup><tbody>[TR]
[TD="width: 576, colspan: 9"]IF order # is between 100-199 and receiving location is one of the ten locations then - freight in[/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 9"]IF order # is between 300-390 and shipping location is one of the ten locations then - freight out[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 10"]IF order # is between 800-899 and receiving lcoation is one of the ten locations then - freight transfer[/TD]
[/TR]
</tbody>[/TABLE]


How is can I construct so that it is only one statement?

Thanks.

Bob

 

[Yongle]

Formula in C2 copied down
=IF(OR(B2="A",B2="B",B2="C",B2="D",B2="E",B2="F",B2="G",B2="H",B2="i",B2="J"),IF(AND(A2>99,A2<200),"Freight IN",IF(AND(A2>299,A2<391),"Freight OUT",IF(AND(A2>799,A2<900),"Freight TRANSFER",""))),"")


where
Column A = Order #
Column B = Receving location
and the 10 receiving locations are "A","B", "C" ...."J"

 

[Yongle]

Or using 10 adjacent cells containing your 10 locations, create a named range TenLocations and use this shorter formula

=IF(COUNTIF(TenLocations,B2)>0,IF(AND(A2>99,A2<200),"Freight IN",IF(AND(A2>299,A2<391),"Freight OUT",IF(AND(A2>799,A2<900),"Freight TRANSFER",""))),"")

 

[Coding4Fun]

Assuming your Order # is in column A, Location in Column B and the List of 10 Locations it needs to be within is listed in Column C this formula in Column E will provide the results identified for all 3 test cases. If it does not meet one of the 3 identified criteria "Freight In, Freight Out or Transfer" then it defaults to "Unidentified".


[TABLE="class: grid, width: 1500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Order #[/TD]
[TD]Location[/TD]
[TD]10 Location List[/TD]
[TD]Test Case[/TD]
[TD]Formula[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]105[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]Freight In[/TD]
[TD]=IF(AND(A2>=100, A2<=199, COUNTIF(C:C,B2)>0), "Freight In", IF(AND(A2>=300, A2<=390, COUNTIF(C:C, B2)>0), "Freight Out", IF(AND(A2>=800, A2<=899, COUNTIF(C:C,B2)>0), "Transfer","Unidentified")))[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]305[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]Freight Out[/TD]
[TD]=IF(AND(A3>=100,A3<=199,COUNTIF(C:C,B3)>0),"Freight In",IF(AND(A3>=300,A3<=390,COUNTIF(C:C,B3)>0),"Freight Out",IF(AND(A3>=800,A3<=899, COUNTIF(C:C,B3)>0), "Transfer","Unidentified")))[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]805[/TD]
[TD]8[/TD]
[TD]8[/TD]
[TD]Transfer[/TD]
[TD]=IF(AND(A4>=100,A4<=199,COUNTIF(C:C,B4)>0),"Freight In",IF(AND(A4>=300,A4<=390,COUNTIF(C:C,B4)>0),"Freight Out",IF(AND(A4>=800,A4<=899, COUNTIF(C:C,B4)>0),"Transfer","Unidentified")))[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]50[/TD]
[TD]17[/TD]
[TD]10[/TD]
[TD]Unidentified[/TD]
[TD]=IF(AND(A5>=100,A5<=199,COUNTIF(C:C,B5)>0),"Freight In",IF(AND(A5>=300,A5<=390,COUNTIF(C:C,B5)>0),"Freight Out",IF(AND(A5>=800,A5<=899, COUNTIF(C:C,B5)>0),"Transfer","Unidentified")))[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD]15[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD]16[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD]22[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD]25[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD]30[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD]35[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Bob32103]

Thanks - the IF statement worked great!

 

[Yongle]

which one did you use?

 

[HighAndWilder]

I’ve got an excel file containing freight detail and I’d like to construct the following formula:


[TABLE="width: 640"]
<colgroup><col width="64" span="10" style="width: 48pt;"></colgroup><tbody>[TR]
[TD="width: 576, colspan: 9"]IF order # is between 100-199 and receiving location is one of the ten locations then - freight in[/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 9"]IF order # is between 300-390 and shipping location is one of the ten locations then - freight out[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 10"]IF order # is between 800-899 and receiving lcoation is one of the ten locations then - freight transfer[/TD]
[/TR]
</tbody>[/TABLE]


How is can I construct so that it is only one statement?

Thanks.

Bob

Create a range called 'Locations' into which you list the 10 locations. This range can be on a hidden sheet.

=IF((AND(E16>=100, E16<=199,COUNTIF(Locations,F16)>0)), "Freight In", IF((AND(E16>=300, E16<=390,COUNTIF(Locations,F16)>0)), "Freight Out", IF((AND(E16>=800, E16<=899,COUNTIF(Locations,F16)>0)), "Freight Transfer", "")))

I don't know how your sheet is formatted so you may need to change the cell references that should be highlighted in the formula above.

Any more complicated that that and I woule create a VBA function.

 

[Bob32103]

I tried the 2nd of the 3 options