Shirt Sizing with Two Conditions

[conlintr1]

Help!

I'm trying to predict what shirts to order using the following parameters:
[TABLE="width: 100"]
<COLGROUP><COL style="WIDTH: 41pt; mso-width-source: userset; mso-width-alt: 2011" width=55><COL style="WIDTH: 28pt; mso-width-source: userset; mso-width-alt: 1353" width=37><COL style="WIDTH: 31pt; mso-width-source: userset; mso-width-alt: 1499" width=41><TBODY>[TR]
[TD="class: xl109, width: 133, bgcolor: transparent, colspan: 3"]Short Sleeve Shirt[/TD]
[/TR]
[TR]
[TD="class: xl108, bgcolor: #d8d8d8"]Order #[/TD]
[TD="class: xl108, bgcolor: #d8d8d8"]Chest[/TD]
[TD="class: xl108, bgcolor: #d8d8d8"]Height[/TD]
[/TR]
[TR]
[TD="class: xl107, width: 55, bgcolor: transparent, align: right"]1452[/TD]
[TD="class: xl107, width: 37, bgcolor: transparent, align: right"]38[/TD]
[TD="class: xl107, width: 41, bgcolor: transparent, align: right"]71[/TD]
[/TR]
[TR]
[TD="class: xl107, width: 55, bgcolor: transparent, align: right"]1552[/TD]
[TD="class: xl107, width: 37, bgcolor: transparent, align: right"]42[/TD]
[TD="class: xl107, width: 41, bgcolor: transparent, align: right"]71[/TD]
[/TR]
[TR]
[TD="class: xl107, width: 55, bgcolor: transparent, align: right"]1652[/TD]
[TD="class: xl107, width: 37, bgcolor: transparent, align: right"]46[/TD]
[TD="class: xl107, width: 41, bgcolor: transparent, align: right"]71[/TD]
[/TR]
[TR]
[TD="class: xl107, width: 55, bgcolor: transparent, align: right"]1653[/TD]
[TD="class: xl107, width: 37, bgcolor: transparent, align: right"]46[/TD]
[TD="class: xl107, width: 41, bgcolor: transparent, align: right"]80[/TD]
[/TR]
[TR]
[TD="class: xl107, width: 55, bgcolor: transparent, align: right"]1752[/TD]
[TD="class: xl107, width: 37, bgcolor: transparent, align: right"]50[/TD]
[TD="class: xl107, width: 41, bgcolor: transparent, align: right"]71[/TD]
[/TR]
[TR]
[TD="class: xl107, width: 55, bgcolor: transparent, align: right"]1753[/TD]
[TD="class: xl107, width: 37, bgcolor: transparent, align: right"]50[/TD]
[TD="class: xl107, width: 41, bgcolor: transparent, align: right"]80[/TD]
[/TR]
[TR]
[TD="class: xl107, width: 55, bgcolor: transparent, align: right"]1852[/TD]
[TD="class: xl107, width: 37, bgcolor: transparent, align: right"]54[/TD]
[TD="class: xl107, width: 41, bgcolor: transparent, align: right"]71[/TD]
[/TR]
[TR]
[TD="class: xl107, width: 55, bgcolor: transparent, align: right"]1853[/TD]
[TD="class: xl107, width: 37, bgcolor: transparent, align: right"]54[/TD]
[TD="class: xl107, width: 41, bgcolor: transparent, align: right"]80[/TD]
[/TR]
[TR]
[TD="class: xl107, width: 55, bgcolor: transparent, align: right"]1952[/TD]
[TD="class: xl107, width: 37, bgcolor: transparent, align: right"]58[/TD]
[TD="class: xl107, width: 41, bgcolor: transparent, align: right"]71[/TD]
[/TR]
</TBODY>[/TABLE]

The order # is dependant upon both the chest and height measurements.


Below are some of the actual measurements from my staff. I need a formula to populate the Order # column with the order numbers from the parameters above.
[TABLE="width: 116"]
<COLGROUP><COL style="WIDTH: 36pt; mso-width-source: userset; mso-width-alt: 1755" width=48><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 32pt; mso-width-source: userset; mso-width-alt: 1536" width=42><TBODY>[TR]
[TD="class: xl109, width: 48, bgcolor: #d8d8d8"]Order #[/TD]
[TD="class: xl109, width: 64, bgcolor: #d8d8d8"]Chest[/TD]
[TD="class: xl109, width: 42, bgcolor: #d8d8d8"]Height[/TD]
[/TR]
[TR]
[TD="class: xl107, bgcolor: transparent"]?[/TD]
[TD="class: xl108, bgcolor: transparent, align: right"]42.5[/TD]
[TD="class: xl108, bgcolor: transparent, align: right"]72[/TD]
[/TR]
[TR]
[TD="class: xl107, bgcolor: transparent"]?[/TD]
[TD="class: xl108, bgcolor: transparent, align: right"]45.5[/TD]
[TD="class: xl108, bgcolor: transparent, align: right"]69.5[/TD]
[/TR]
[TR]
[TD="class: xl107, bgcolor: transparent"]?[/TD]
[TD="class: xl108, bgcolor: transparent, align: right"]38.5[/TD]
[TD="class: xl108, bgcolor: transparent, align: right"]66[/TD]
[/TR]
[TR]
[TD="class: xl107, bgcolor: transparent"]?[/TD]
[TD="class: xl108, bgcolor: transparent, align: right"]37.75[/TD]
[TD="class: xl108, bgcolor: transparent, align: right"]67[/TD]
[/TR]
[TR]
[TD="class: xl107, bgcolor: transparent"]?[/TD]
[TD="class: xl108, bgcolor: transparent, align: right"]39[/TD]
[TD="class: xl108, bgcolor: transparent, align: right"]65[/TD]
[/TR]
[TR]
[TD="class: xl107, bgcolor: transparent"]?[/TD]
[TD="class: xl108, bgcolor: transparent, align: right"]42.5[/TD]
[TD="class: xl108, bgcolor: transparent, align: right"]70[/TD]
[/TR]
[TR]
[TD="class: xl107, bgcolor: transparent"]?[/TD]
[TD="class: xl108, bgcolor: transparent, align: right"]39[/TD]
[TD="class: xl108, bgcolor: transparent, align: right"]72[/TD]
[/TR]
</TBODY>[/TABLE]

 

[rilzniak]

Not sure where you have the tables in your spreadsheet, but here is my solution based on the Short Sleeve Shirt table in cells H1:J11 and Order # in cells L1:N8.

=IF(M2<38,$H$3,IF(M2>58,$H$11,VLOOKUP(M2,CHOOSE({1,2},$I$3:$I$11,$H$3:$H$11),2,TRUE)))

My solution assumes that any measurement smaller than 38" in the Chest size will return Order 1452, and anything larger than 58 will return Order 1952. I assumed that all orders can be based solely on Chest size and that Height is automatic. Hope that's correct.

 

[conlintr1]

No, it didn't work. The height affects the order number as much as the chest size. I tried this:

=IF($M2>58,"Unavailable",IF($M2>54,"1952",IF(AND($M2>50,$N2>71),"1853","1852",IF(AND($M2>46,$N2>71),"1753","1752",IF(AND($M2>42,$N2>71),"1653","1652",IF($M2>38,"1552","1452"))))))

but I can't figure out where my error is.

 

[Michael M]

Given your table what should the order numbers be for the staff measurements, if you did them manually ??
Also, is that the entire table of shirt sizes ?

 

[rilzniak]

That won't work, your IF statements are not correct. You have them set up as IF(x, then y, otherwise z, then a), when they should be IF(x, then y, otherwise Z). Try this:

=IF(m2>58,"unavailable",IF(m2=58,"1952",IF(and(m2=54,n2=80),"1853",IF(and(m2=54,n2=71),"1852",IF(and(m2=50,n2=71),"1752",IF(and(m2=50,n2=80),"1753",IF(and(m2=46,n2=71),"1652",IF(and(m2=46,n2=80),"1653",IF(m2=42,"1552","1452")))))))))

You would be better off building a code for the sizes and just using a VLOOKUP.

 

[shg]

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][td]

H​

[/td][td]

I​

[/td][/tr]
[tr][td]

1​

[/td][td]

Order #​

[/td][td]

Chest​

[/td][td]

Height​

[/td][td]

​

[/td][td]

Order #​

[/td][td]

Chest​

[/td][td]

Height​

[/td][td]

​

[/td][td]

​

[/td][/tr]

[tr][td]

2​

[/td][td]

1452​

[/td][td]

38​

[/td][td]

71​

[/td][td][/td][td]

1653​

[/td][td]

42.50​

[/td][td]

72.00​

[/td][td][/td][td]E2: {=INDEX($A$2:$A$10, MATCH(1, (B$2:$B$10 >= F2) * ($C$2:$C$10 >= G2), 0))}[/td][/tr]

[tr][td]

3​

[/td][td]

1552​

[/td][td]

42​

[/td][td]

71​

[/td][td][/td][td]

1652​

[/td][td]

45.50​

[/td][td]

69.50​

[/td][td][/td][td][/td][/tr]

[tr][td]

4​

[/td][td]

1652​

[/td][td]

46​

[/td][td]

71​

[/td][td][/td][td]

1552​

[/td][td]

38.50​

[/td][td]

66.00​

[/td][td][/td][td][/td][/tr]

[tr][td]

5​

[/td][td]

1653​

[/td][td]

46​

[/td][td]

80​

[/td][td][/td][td]

1452​

[/td][td]

37.75​

[/td][td]

67.00​

[/td][td][/td][td][/td][/tr]

[tr][td]

6​

[/td][td]

1752​

[/td][td]

50​

[/td][td]

71​

[/td][td][/td][td]

1552​

[/td][td]

39.00​

[/td][td]

65.00​

[/td][td][/td][td][/td][/tr]

[tr][td]

7​

[/td][td]

1753​

[/td][td]

50​

[/td][td]

80​

[/td][td][/td][td]

1652​

[/td][td]

42.50​

[/td][td]

70.00​

[/td][td][/td][td][/td][/tr]

[tr][td]

8​

[/td][td]

1852​

[/td][td]

54​

[/td][td]

71​

[/td][td][/td][td]

1653​

[/td][td]

39.00​

[/td][td]

72.00​

[/td][td][/td][td][/td][/tr]

[tr][td]

9​

[/td][td]

1853​

[/td][td]

54​

[/td][td]

80​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

10​

[/td][td]

1952​

[/td][td]

58​

[/td][td]

71​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

Note the sorting of columns A:C

 

[conlintr1]

That's the full range of shirt sizes. Anyone with a chest size 38" or smaller would be order code 1452 (because the smallest shirt would still be wearable if you were smaller than it). Anyone larger than 38" chest size up to and including 42" would be order code 1552. This trend continues up until code 1952. Any chest measurement over 58" would signify "Unavailable."

The second value, the height, is what messes me up. I've used this formula with clothing items based on a single size:

=IF($C3>57,"Unavailable",IF($C3>54,"8056",IF($C3>51,"8053",IF($C3>48,"8050",IF($C3>44.5,"8047",IF($C3>42.5,"8044",IF($C3>40.5,"8042",IF($C3>38.5,"8040","8038"))))))))

*Note: This is just an example of another item with different order numbers.

But for order numbers 1652/1653, 1752/1753, and 1852/1853 the chest sizes are the same and the height dictates which order number. Here are the measurements from the employees with the order numbers manually added:

[TABLE="width: 101"]
<COLGROUP><COL style="WIDTH: 40pt; mso-width-source: userset; mso-width-alt: 1938" width=53><COL style="WIDTH: 29pt; mso-width-source: userset; mso-width-alt: 1426" width=39><COL style="WIDTH: 32pt; mso-width-source: userset; mso-width-alt: 1572" width=43><TBODY>[TR]
[TD="class: xl65, width: 53, bgcolor: #d8d8d8"]Order #[/TD]
[TD="class: xl65, width: 39, bgcolor: #d8d8d8"]Chest[/TD]
[TD="class: xl65, width: 43, bgcolor: #d8d8d8"]Height[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 53, bgcolor: white"]1653[/TD]
[TD="class: xl66, width: 39, bgcolor: white"]42.5[/TD]
[TD="class: xl66, width: 43, bgcolor: white"]72[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 53, bgcolor: white"]1652[/TD]
[TD="class: xl66, width: 39, bgcolor: white"]45.5[/TD]
[TD="class: xl66, width: 43, bgcolor: white"]69.5[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 53, bgcolor: white"]1552[/TD]
[TD="class: xl66, width: 39, bgcolor: white"]38.5[/TD]
[TD="class: xl66, width: 43, bgcolor: white"]66[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 53, bgcolor: white"]1452[/TD]
[TD="class: xl66, width: 39, bgcolor: white"]37.8[/TD]
[TD="class: xl66, width: 43, bgcolor: white"]67[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 53, bgcolor: white"]1552[/TD]
[TD="class: xl66, width: 39, bgcolor: white"]39[/TD]
[TD="class: xl66, width: 43, bgcolor: white"]65[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 53, bgcolor: white"]1652[/TD]
[TD="class: xl66, width: 39, bgcolor: white"]42.5[/TD]
[TD="class: xl66, width: 43, bgcolor: white"]70[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 53, bgcolor: white"]1552[/TD]
[TD="class: xl66, width: 39, bgcolor: white"]39[/TD]
[TD="class: xl66, width: 43, bgcolor: white"]72[/TD]
[/TR]
</TBODY>[/TABLE]

 

[shg]

Those results are the same as my formula gives, with the exception of the last one. Your answer, 1552, doesn't meet the customer's height, no?