calculating shipping costs from multiple variables?

[bfodius]

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!!

 

[Stan_C]

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.

 

[bfodius]

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

 

[bfodius]

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.

Can I reference those spreadsheets somehow with my drop down menus?

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

I really appreciate your help



B-Fo

 

[bfodius]

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 :-/

 

[Misca]

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
	A	B	C	D	E	F	G	H	I	J
1	Item	From	To	Price			Named Ranges:
2	Item 1	Phoenix	Tucson	$32,00			Items
3	Item3	Flagstaff	Phoenix	$175,00			PriceTable
4	Item 1	Flagstaff	Flagstaff	$24,00			City
5	Large	Tucson	Phoenix	$185,00			Zones
6	Small	Tucson	Flagstaff	$34,00
7
8	ITEMS	Zone 1	Zone 2	Zone 3	Zone 4		City	Phoenix	Tucson	Flagstaff
9	Item 1	$24,00	$32,00	$42,00	$55,00		Phoenix	1	2	3
10	Item 2	$30,00	$40,00	$50,00	$60,00		Tucson	2	1	4
11	Item3	$50,00	$100,00	$175,00	$218,00		Flagstaff	3	4	1
12	Small	$10,00	$15,00	$20,00	$34,00
13	Large	$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.

 

[Stan_C]

Playing off of Misca:

COST

*	A	B	C	D
Item	From	To	Price
*	*	*	*
Pineapple	Flagstaff	Tucson
Pear	Flagstaff	Buckeye
Apple	Phoenix	Phoenix
Orange	Tucson	Tucson

<tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="align: right"] $ * 38.00[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="align: right"] $ * 30.00[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="align: right"] $ * 25.00[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="align: right"] $ * 42.00[/TD]

</tbody>

Spreadsheet Formulas

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

<tbody>
</tbody>

Data Validation in Spreadsheet

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
Item	Price	*	City	Phoenix	Tucson	Flagstaff	Buckeye	Gila Bend	Sentinel
Banana	*	Phoenix
Apple	*	Tucson
Pear	*	Flagstaff
Pineapple	*	Buckeye
Orange	*	Gila Bend
Kiwi	*	Sentinel
Star Fruit	*	*	*	*	*	*	*	*

<tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="align: right"] $ * 10.00[/TD]

[TD="align: right"] $ * 10.00[/TD]
[TD="align: right"] $ * 11.00[/TD]
[TD="align: right"] $ * 12.00[/TD]
[TD="align: right"] $ * 13.00[/TD]
[TD="align: right"] $ * 14.00[/TD]
[TD="align: right"] $ * 15.00[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="align: right"] $ * 15.00[/TD]

[TD="align: right"] $ * 13.00[/TD]
[TD="align: right"] $ * 12.00[/TD]
[TD="align: right"] $ * 11.00[/TD]
[TD="align: right"] $ * 10.00[/TD]
[TD="align: right"] $ * * *9.00[/TD]
[TD="align: right"] $ * * *8.00[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="align: right"] $ * 20.00[/TD]

[TD="align: right"] $ * 16.00[/TD]
[TD="align: right"] $ * 13.00[/TD]
[TD="align: right"] $ * 10.00[/TD]
[TD="align: right"] $ * 10.00[/TD]
[TD="align: right"] $ * * *5.00[/TD]
[TD="align: right"] $ * * *7.00[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="align: right"] $ * 25.00[/TD]

[TD="align: right"] $ * 19.00[/TD]
[TD="align: right"] $ * 14.00[/TD]
[TD="align: right"] $ * * *9.00[/TD]
[TD="align: right"] $ * 10.00[/TD]
[TD="align: right"] $ * * *5.00[/TD]
[TD="align: right"] $ * * *5.00[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="align: right"] $ * 30.00[/TD]

[TD="align: right"] $ * 22.00[/TD]
[TD="align: right"] $ * 15.00[/TD]
[TD="align: right"] $ * * *8.00[/TD]
[TD="align: right"] $ * 10.00[/TD]
[TD="align: right"] $ * * *5.00[/TD]
[TD="align: right"] $ * * *4.00[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="align: right"] $ * 35.00[/TD]

[TD="align: right"] $ * 25.00[/TD]
[TD="align: right"] $ * 16.00[/TD]
[TD="align: right"] $ * * *7.00[/TD]
[TD="align: right"] $ * 10.00[/TD]
[TD="align: right"] $ * * *5.00[/TD]
[TD="align: right"] $ * 20.00[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]

[TD="align: right"] $ * 40.00[/TD]

</tbody>

Spreadsheet Formulas

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.