Nested if formula?

[Bob32103]

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

IF order # is between 100-199 and receiving location is one of the ten locations then - freight in
IF order # is between 300-390 and shipping location is one of the ten locations then - freight out
IF order # is between 800-899 and receiving lcoation is one of the ten locations then - freight transfer

<colgroup><col width="64" span="10" style="width: 48pt;"></colgroup><tbody>
</tbody>

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".

	A	B	C	D	E
1	Order #	Location	10 Location List	Test Case	Formula
2	105	1	1	Freight In	=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")))
3	305	5	5	Freight Out	=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")))
4	805	8	8	Transfer	=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")))
5	50	17	10	Unidentified	=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")))
6			15
7			16
8			22
9			25
10			30
11			35
12

<tbody>
</tbody>

 

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

IF order # is between 100-199 and receiving location is one of the ten locations then - freight in
IF order # is between 300-390 and shipping location is one of the ten locations then - freight out
IF order # is between 800-899 and receiving lcoation is one of the ten locations then - freight transfer

<colgroup><col width="64" span="10" style="width: 48pt;"></colgroup><tbody>
</tbody>

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