# calculating shipping costs from multiple variables?

#### bfodius

##### New Member
Hi, I am somewhat of a newb when it comes to excel but i can usually find my way around with help from the amazing google. this one however has me stumped. I have a transportation company that transports various items to and from multiple cities. I want to make a spredsheet with drop down menus: 1 for the item type 1 for pickup city and 1 for city to be delivered to. that part i got no problem.

the part i cant figure out is if its possible to have a fourth cel auto calculate shipping cost based on variables from those three menus. for example: the item could be a large box picked up from city 1 and delivered to city 2 and when those three are selected a fourth cel would say \$58.00?

is this way too complicated for excel? I can try to explain better if needed. i Have 100 diff items and about 115 diff cities. A lot of variables and i know building the initial data will be time consuming but it will hopefully save a ton of time in the long run.

Any help or advice will be extremely appreciated.

Thank You!!

### Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
It is possible. We need to know the actual variables to assist you.

Will each city have a cost associated with it's shipping?
Will there be some kind of mileage criteria the numbers will be based on?

Just need to see some data posted in a table.

ok cool. the cost is based on the type of item and then from location to location. so its basically three variables. for example a large delivered locally in any city would be 58 dollars but delivered to a nearby city it would be 64 and delivered to a further city would be 74 and so on. a smaller item would be 24 dollars locally 32 to nearby city and 42 to a further one.

I have pricing set already for about 100 diff items and 100 diff cities, its not by mileage but by difficulty and time of travel. I dont need to figure the pricing but rather a quick simple way to find the pricing ive already set up. and make it user friendly for my associates to use, thats why i was thinking of three drop down menus that would be referenced to find the price.

Im not sure how to post a data table on here, will look into it, but im also not sure what data would be helpful.

Thanks again

A little more info. I have a seperate spreadsheet already for each item containing the price for shipping to and from each city.

Phoenix Tucson Flagstaff
Phoenix \$54 \$64 \$74
Tucson \$64 \$54 \$84
Flagstaff \$74 \$84 \$54

Small example of what each spreadsheet contains x 115 different ones for each item. each item however has only 5 possible prices. Prices are determined by a predetermined zone. For example phoenix to phoenix is zone 0, phoenix to tucson is zone 1, phoenix to flagstaff is zone 2, tucson to tucson would be zone 0 just like phoenix to phoenix.
Hope this extra info can help.

I was reading about dynamic dependant tables to make a drop down menu...is that the route i should be going down for this?

B-Fo

Phoenix Tucson Flagstaff
Phoenix \$54 \$64 \$74
Tucson \$64 \$54 \$84
Flagstaff \$74 \$84 \$54

..............Phoenix...Tucson...Flagstaff
Phoenix..... \$54....... \$64........ \$74
Tucson..... \$64........ \$54....... \$84
Flagstaff ...\$74........ \$84....... \$54

didnt know it would delete the spaces, and my brain seems too fried to figure out how to edit :-/

This is how I'd do it:
Have two tables. One for the Items & Shipping prices and the other one for the distances / zones between the cities.
In my example I've done this. I've named the colored ranges so that the formulas are easier to write / understand.

First you need one INDEX / MATCH to return the distance / zone between the cities. I've changed your Zone 0 to 1 so that I don't have to use another lookup table (or the MATCH +1) to know the column in the price table. You're also going to need to know the row and the MATCH for Item returns that. And now that you know the row and column INDEX returns the price you're looking for from the price table.
Excel Workbook
ABCDEFGHIJ
1ItemFromToPriceNamed Ranges:
2Item 1PhoenixTucson\$32,00Items
3Item3FlagstaffPhoenix\$175,00PriceTable
4Item 1FlagstaffFlagstaff\$24,00City
5LargeTucsonPhoenix\$185,00Zones
6SmallTucsonFlagstaff\$34,00
7
8ITEMSZone 1Zone 2Zone 3Zone 4CityPhoenixTucsonFlagstaff
9Item 1\$24,00\$32,00\$42,00\$55,00Phoenix123
10Item 2\$30,00\$40,00\$50,00\$60,00Tucson214
11Item3\$50,00\$100,00\$175,00\$218,00Flagstaff341
12Small\$10,00\$15,00\$20,00\$34,00
13Large\$115,00\$185,00\$299,00\$408,00
Sheet

Since all the MATCH functions are used with text make sure you're using the exact match. Also make sure the cities are in same order in your distance / zone table. This way you can use the same range to return both the row and the column from the distance / zone table.

Playing off of Misca:

COST

 * A B C D 1 Item From To Price 2 * * * * 3 Pineapple Flagstaff Tucson \$ * 38.00 4 Pear Flagstaff Buckeye \$ * 30.00 5 Apple Phoenix Phoenix \$ * 25.00 6 Orange Tucson Tucson \$ * 42.00

<tbody>
</tbody>

 Cell Formula D3 =IFERROR(INDEX(Price,MATCH(A3,Item,0))+INDEX(Shipping,MATCH(B3,City,0),MATCH(C3,CityTop,0)),"")

<tbody>
</tbody>

<tbody>
</tbody>

 Cell Allow Datas Input 1 Input 2 A3 List * =Item * B3 List * =City * C3 List * =City *

<tbody>
</tbody>

<tbody>
</tbody>

DATA

 * A B C D E F G H I J 1 Item Price * City Phoenix Tucson Flagstaff Buckeye Gila Bend Sentinel 2 Banana \$ * 10.00 * Phoenix \$ * 10.00 \$ * 11.00 \$ * 12.00 \$ * 13.00 \$ * 14.00 \$ * 15.00 3 Apple \$ * 15.00 * Tucson \$ * 13.00 \$ * 12.00 \$ * 11.00 \$ * 10.00 \$ * * *9.00 \$ * * *8.00 4 Pear \$ * 20.00 * Flagstaff \$ * 16.00 \$ * 13.00 \$ * 10.00 \$ * 10.00 \$ * * *5.00 \$ * * *7.00 5 Pineapple \$ * 25.00 * Buckeye \$ * 19.00 \$ * 14.00 \$ * * *9.00 \$ * 10.00 \$ * * *5.00 \$ * * *5.00 6 Orange \$ * 30.00 * Gila Bend \$ * 22.00 \$ * 15.00 \$ * * *8.00 \$ * 10.00 \$ * * *5.00 \$ * * *4.00 7 Kiwi \$ * 35.00 * Sentinel \$ * 25.00 \$ * 16.00 \$ * * *7.00 \$ * 10.00 \$ * * *5.00 \$ * 20.00 8 Star Fruit \$ * 40.00 * * * * * * * *

<tbody>
</tbody>

 Cell Formula E1 =OFFSET(\$D\$2,COLUMN(E1)-5,)

<tbody>
</tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

I setup 5 different named ranges. All ranges are dynamic. Meaning the list will change when new product or cities are input. This will only work as long as you do not leave blanks in your data ex. (A2:A5 Fille/ A6 Empty/ A7:A12 filled).

Item =OFFSET(DATA!\$A\$2,,,COUNTA(DATA!\$A\$2:\$A\$1000),)
Price =OFFSET(DATA!\$B\$2,,,COUNTA(DATA!\$B\$2:\$B\$1000),)
Shipping =OFFSET(DATA!\$E\$2,,,COUNTA(City),COUNTA(City))
City =OFFSET(DATA!\$D\$2,,,COUNTA(DATA!\$D\$2:\$D\$1000),)
CityTop =OFFSET(DATA!\$E\$1,,,,COUNTA(City))

Creating a defined name in the Name Manager:

1. Press ctrl+F3
2. Click New
3. In Name: insert name given
4. In Refers to: insert formula given

Creating Validation drop downs:

1. Select range for validation
2. Open the Data Validation window
3. Select lists
4. Click in source
5. press F3
6. Select range you want the list to look at

Let me know if you have any questions.

Replies
1
Views
78
Replies
2
Views
176
Replies
1
Views
197
Replies
2
Views
397
Replies
1
Views
323

1,196,051
Messages
6,013,109
Members
441,748
Latest member
MrBigglesworth

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back