Google-sheets – How to work out the price of shipping an item in Google Spreadsheets

google sheets

I'm currently trying to build a formula to work out a problem I have.

What is the problem: Right now before I can send an item out, I need to work out how much that will cost me.

This is done by measuring the: Length, Width, Height and Weight of a product.

I then have each category divided up – So if the length width or height exceed say 10, 10, 10 then it goes to the next category which is 20, 20, 20. Once none of the dimensions of the product exceed the category I then see what the most appropriate weight is: 1-499G = $0.99, 500-1000G = $2.99, 1001-2000 = $3.99. And so on.

Now what I want to be able to do is be able to put the length, width, height and weight into their own cells and then be able to work out how much it cost. However I'm not to sure on how to do that. The main issue being that each product dimension needs to be tested for each category dimension, this is because the length, width, height will vary depending on how the person measures the item.

So say we have a product with dimension of 11cm, 10cm, 15cm and a category dimension of 12cm, 16cm, 9cm it needs to check each of the product dimensions 3 times and then assign it to the most appropriate column.

Does anyone know how I would go about doing? I'm thinking about a multiple conditioned IF statement, but I can't seem to figure out how.

Best Answer

A rectangular box A fits inside of box B exactly when all of the following apply:

  1. The largest dimension of A <= the largest dimension of B
  2. The middle dimension of A <= the middle dimension of B
  3. The smallest dimension of A <= the smallest dimension of B

I used MAX, MIN, and SMALL below to implement this check. 

=IF(MIN(E1:G1)<=MIN(A1:C1),IF(MAX(E1:G1)<=MAX(A1:C1),IF(SMALL(E1:G1,2)<=SMALL(A1:C1,2),"fits",""),""),"")

This assumes that the category limits are in A1:C1, and the box size is in E1:G1. The output is "fits" or empty string if it doesn't fit.

The rest depends on how your spreadsheet is organized. For example, you can replace "fits" by the number of the category (1,2,3,etc). Then, if the box fits multiple categories, the MIN will find the smallest one. It will be a pretty long formula if you put it all together, so I suggest having one cell with the result of testing against each category (category number of empty string) and another cell which is just the MIN of those.

Related Topic