Dimensional weight formula

[punter]

Hello all,

Thank you in advance for you help.

I have attached a sheet that contains a pretty simple formula that helps me figure out the dimensional weight of a shipment. It is just a simple vlookup that returns the proper rate (Column H) depending on DIM Weight and Zone. It works great.

The issue I'm running into is with various exceptions that I need to put into the formula. The criterias that I need to put into the rate formula (Column H) are:

1-If the one of the Length (Column A), Width (Column B), and Height (Column C) is greater than 60 I need to add 7.50 to the the returned lookup value in Column H.

2-The second longest side the Length (Column A), Width (Column B), and Height (Column C) is greater than 30 I need to add 7.50 to the the returned lookup value in Column H.

3-If the grith (2x Column C + 2x Column B) is greater than 130 I need to add 45 to the the returned lookup value in Column H.

I'm working with a few IF formulas but I can't seem to get all of them to work at the same time. Does anyone have any thoughts?

Thank you so much for all of your help.

Eddie.

[GuruWannaB]

You could just do something like

Please Login or Register  to view this content.

(adding an IF statement for each of your additional criteria (only the criteria 1 did I add for above...but you get the idea...)


But I have a question: Might there be a scenario where the package may meet both criterias of 1 and 2? If so, would you add 15.00 (7.50 + 7.50)? And obviously..the question is open for what would you do if it met all three criteria?

[BigBas]

I didn't test the numbers to confirm that the formula works, but the following formula in cell H2 and filled down SHOULD yield your results. Post back if it works.

Please Login or Register  to view this content.

[punter]

Thank you so very much for your quick reply.

We would only add 7.50 if criteria 1 and 2 were hit. We not have to add 7.50 twice to get 15.00

If all three were hit we would add 45. If criteria 3 is hit it would trump the 7.50.

Thanks

[Richard Buttrey]

Hi,

It would have helped immensely if you had told us the password to the sheet you protected which contains your lookup table, and which of your 3 tests took precedence.

One way would be to a couple of extra columns to your lookup table and add 7.5 and 45 to the original values.

Then use a helper column on your calculator sheet to hold IF() functions which determine which column of the VLOOKUP() table to use.

The helper column I cell I2 would be

Please Login or Register  to view this content.

and use G2+I2 in place of the G2

Untested for the actual VLOOKUP table since I can't see it, but I guess you can see the principle. Adjust the helper column as necessary.

HTH

[GuruWannaB]

Then ignore the formula i listed above as there is a primary order of the additions to be considered.

Please Login or Register  to view this content.

[/COLOR]

This would be the formula for H2 and puts has the criteria heiarchy of:

if the girth criteria is met...add 45, else if at least one dimension > 60 then add 7.50....else if the second largest side >30 add 7.50 - otherwise add nothing.

[punter]

Thank you all so much. I'm working on it right.

The password is punter. I didn't think anyone would need to look at it. I'm sorry for the oversight.

I'm working on the formula now. If anyone needs any other information please let me know.

Thanks

Eddie.

[BigBas]

It seems like there is a bit of a hierarchy there. If certain conditions are met, others do not apply. Maybe I'm just having a slow day, but can you specify what occurs in different instances?

Also, you don't necessarily need to use IF formulas if you understand that TRUE and FALSE can be treated as values. Take a look at my formula. One part states (MAX(A2:C2)>60)*7.5. Basically, this is a TRUE/FALSE formula. If any of the cells between A2:C2 are greater than 60, the value is TRUE, or 1. If none are greater, the value is FALSE, or 0. Incorporating that notion, I'm sure you can work out the variables to create a function that is less complex than a multi tiered IF formula

[punter]

That last one by GuruWannaB seems to be working great.

Thank you so much Richard and BigB. If there are any other issues I'll let you know. I'll try to give you some rep.

I'll close the thread in a few minutes. It seems to be working fine but I want to test it a few more times.

Thanks again so much.

Eddie.