Divide (or QUOTIENT) Function Nested in an IF Function Help - for Cubic Feet AND Weight

[littledobby]

Hi! I'm trying to create a formula to tell me which products should ship via UPS Ground (any product with dimensions of 1 cubic foot or more), which should ship via USPS Priority Mail (any other product with dimensions < 1 cubic foot but that WEIGHS 1 lb or more), and have anything weighing < 1 lb shipping via USPS First Class Mail no matter what.

I haven't yet figured out how to include the weight to find the USPS First Class ones, but I can tell you the Weight is found in Column V.

**Note: The formula to find Cubic Footage is: (L x W x H) / 1728

This is what I used to have when I didn't know the cubic footage was involved (so I was only going by weight (Column V):

=IF((V2095>1),"UPSGround",(IF(V2095>0.99,"USPSPriority","USPSFirstClass")))

This is what I now have, but I don't know how to allow for the < 1 lb weight to force those items to ship via USPS First Class):

=IF((QUOTIENT((AN2095*AO2095*AP2095),1728)>1),"UPSGround",(IF(QUOTIENT((AN2095*AO2095*AP2095),1728)>.99,"USPSPriority","USPSFirstClass")))

Any help with this would be GREATLY appreciated!!! Thank you!

Susie

 

[shg]

Maybe ...

=IF(Len * Wid * Hgt >= 1728, "UPS Gound", IF(Wgt >= 1, "USPS Priority", "USPS First Class"))

 

[littledobby]

Slight correction - anything that weighs 13 oz or less needs to ship via USPS First Class Mail. So it should be anything with Column V (Weight) < 0.82 rather than <.99

 

[littledobby]

Maybe something along those lines, but the formula to find the cubic footage is (Length x Width x Height) all divided by 1728, not >= 1728. The divide by 1728 is part of the formula needed to figure out the cubic footage. But thank you!

 

[littledobby]

Got it! Thanks for the idea, shg! Final working formula:

=IF((QUOTIENT((AN2095*AO2095*AP2095),1728)>=1),"UPSGround",(IF(V2095>0.99,"USPSPriority","USPSFirstClass")))

 

[littledobby]

Slight correction (for the 13 oz part = 0.81):

=IF((QUOTIENT((AN2095*AO2095*AP2095),1728)>=1),"UPSGround",(IF(V2095>0.81,"USPSPriority","USPSFirstClass")))

 

[shg]

The divide by 1728 is part of the formula needed to figure out the cubic footage.

Dividing by 1728 and comparing to 1 is the same as not dividing by 1728 and comparing to 1728.