Determine postal price for individual products using dimensions and weight

[MrsBucket]

Hi there,

I am trying to make an excel calculator that automatically tells me the postal shipping price for individual products, according to the product dimensions and weights in the table I have created.

e.g. Product 1 is 15cm x 10xm x 1cm, and weighs 50g.

To determine how much it costs to post the item, I need excel to classify what postal size format that product is (e.g. envelope, large envelope, packet). Each postal size format has maximum allowed dimensions and a maximum allowed weight, and if any of them are exceeded then I need to use a different (i.e. more expensive) size format. For example, if the length, width and weight of a product are within the allowance, but the height is too much, then I cannot use that size format.

In addition to classifying the postal size format, I need excel to classify the weight tier for the specific product. For example, the maximum weight of a large envelope might be 500g, however there can be different weight band pricing, e.g. 0-100g, 101-200, 201-300, 301,400, and 401-500.

I would much appreciate any help!

 

[manu197a]

You can use a customized function combining SELECT CASE and IF statements. With a code like the example below, you can select the values for each shippment and calculate how much it should cost.

Let's say that you have the information of each shippment in rows with the following columns:
Column A contains the Type of Product (Envelope, Large envelope, etc.)
Column B contains the First dimension in units (e.g. Height in cms: 15)
Column C contains the Second dimension in units (e.g. 10)
Column D contains the Third dimension in units (e.g. 20)
Column E contains the Weight in units (e.g. 10 in pounds or kilos)

For a given shippment, for instance in row 2, you can write the following formula in F2:
=Post_Calculation(A2,B2,C2,D2,E2)

The customized funtion will check each parameter and give you the appropiate value. You can define a different caluculation for Envelopes, Large envelopes, etc. And for each type you can insert a sub SELECT CASE statement to make a different calculation.

Function Post_Calculation(Type_of_Product As String, _
    Dimension1 As Double, Dimension2 As Double, Dimension3 As Double, Weight As Double)

Select Case Type_of_Product

Case "Envelope":
    If Weight > 50 Then
        Post_Calculation = 10
    Else
        If Dimension1 > 10 Or Dimension2 > 10 Or Dimension3 > 10 Then
            Post_Calculation = 15
        Else
            Post_Calculation = 12
        End If
    End If

Case "Packet":
    If Weight > 10 Then
        If Dimension1 > 13 and Dimension2 > 5 and Dimension3 > 40 Then
            Post_Calculation = 20
        ElseIf Dimension1 > 5 and Dimension2 > 10 and Dimension3 > 10 Then 
            Post_Calculation = 9
        End If
    Else
        If Dimension1 > 4 Then
            Post_Calculation = 20
        Else
            Post_Calculation = 22
        End If
    End If

Case Else:
    Post_Calculation = "Type of product not identified"

End Select

End Function