How to calculate shipping, based on location

Bridget Sloane

Hi all,

I would like to calculate the shipping of a specific product based on the location of the purchaser.

Anyone who orders is in one of 5 geographical locations (North Central, Northeastern, South Central, Southeastern, or Western). Product shipping for anyone in the Western location is $30.00. Product shipping for anyone in any of the other areas is $25. How do I set up a formula to correctly calculate the shipping?

Thanks,

-Bridget

Jeff Reisman

@Bridget Sloane

Assuming you have a column that contains the geographical location for the order, yes? If so, then you can use a simple IF formula in your shipping cost column to determine shipping cost. Let's say your geographical location column is called 'Geo Location':

=IF([Geo Location]@row = "Western", 30, 25)

In English - If the Geo Location is "Western" set this cell value to 30, but if it's any other value, set it to 25.

Now, you might want this column to stay blank until the Geo Location is some value, right? to handle that, we'll evaluate if the Geo Location is a value first:

=IF(ISBLANK([Geo Location]@row), "", IF([Geo Location]@row = "Western", 30, 25))

In English, If the Geo Location is blank, leave this shipping cost cell blank, otherwise, If the Geo Location is "Western", set this cell value to 30, but if it's any other value, set it to 25.

Jeff Reisman

@Bridget Sloane

Assuming you have a column that contains the geographical location for the order, yes? If so, then you can use a simple IF formula in your shipping cost column to determine shipping cost. Let's say your geographical location column is called 'Geo Location':

=IF([Geo Location]@row = "Western", 30, 25)

In English - If the Geo Location is "Western" set this cell value to 30, but if it's any other value, set it to 25.

Now, you might want this column to stay blank until the Geo Location is some value, right? to handle that, we'll evaluate if the Geo Location is a value first:

=IF(ISBLANK([Geo Location]@row), "", IF([Geo Location]@row = "Western", 30, 25))

In English, If the Geo Location is blank, leave this shipping cost cell blank, otherwise, If the Geo Location is "Western", set this cell value to 30, but if it's any other value, set it to 25.

Rodolfo Garcia

@Bridget Sloane

First you need to create a Grid with those values

Then create another , using INDEX and MATCH

And using dropdown single select in column properties you can also add future locations and costs

If the answer works please vote Up to help others and mark insightful

Best regards

Rodolfo

Bridget Sloane

The formula =IF(ISBLANK([Geo Location]@row), "", IF([Geo Location]@row = "Western", 30, 25)) worked perfectly. Thank you so much!

-Bridget

Jeff Reisman

@Rodolfo Garcia That is undoubtedly the correct solution when there are a large number of geographical locations which all vary in shipping costs. Such a situation would entail a very long and complex series of IFs to calculate, and would be prone to error, so in a case like that your solution is elegant and clean.

For Bridget's situation, with only a handful on locations and only a price variation for one of the options, a more simple solution can do the trick.