Vlookup - tough one for sure!

[excelbetter]

Can anyone help?

I am looking to do a "lookup" whereby I find the total cost of shipping x# of boxes via UPS. Let me see if I can complicate this a bit further.

When we ship a bunch of stuff to our customers, the order will go in several boxes. UPS charge different rates per box shipped. So, 1 box of 5 lbs is cheaper than an order of 5 lbs total broken into 2 boxes of 2 lbs and 1 box of 1 lbs. (i.e the rates and zones shown in the image below)

Therefore, I'm trying to figure out how I can have a "total order weight" and a "max weight per box" and create a formula that does 3 things:

1) Figure out how many boxes there will be on the order (i.e. total order weight / max weight per box)

2) Figure out the exact weight of each box if the total order weight was 5 lbs and the max weight per box (i.e. a fixed variable) is 2 lbs, I can logically see there will be 2 boxes of 2 lbs each and 1 box of 1 lbs.

3) Use some lookup feature to find the combined total rate of the 3 boxes above (i.e. 2 @ 2 lbs each and 1 @ 1lb).

Any thoughts on how to do this? Thanks.

NOTE: The Total Order weight in the image should read 5 lbs, not 2 lbs as shown. The max weight per box is 2 lbs.

- Show quoted text -

 

[excelbetter]

Tauren,

When I tested your formula changing the max box weight to 4 lbs instead of 2, and then change the zones, I am not getting the correct results. I can't figure out what's wrong, but I don't ge the right results.

Un-NAME-ed approach, with the same layout will be:

Excel Workbook
	A	B	C	D	E	F	G	H
1		Zones
2	WEIGHTS	Z1	Z2	Z3	Z4		ZONE	Z3
3	1	39	14	10	40		Total Order Weight	5
4	2	1	35	17	8		Max Box weight	2
5	3	2	29	19	10		No boxes	3
6	4	48	15	42	14
7	5	8	30	32	34		Total Cost:	44
Sheet1

 

[taurean]

I should have tested it more. Sorry for the erroneous formula.
Formula in H7 for 2007+
Formula in H8 for 2003-

<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>H7</TD><TD>=IFERROR((ROUNDDOWN(H3/H4,0)*INDEX(A2:E7,MATCH(H4,A2:A7,0),MATCH(H2,A2:E2,0))+(H5-ROUNDDOWN(H3/H4,0))*INDEX(A2:E7,MATCH((H5-ROUNDDOWN(H3/H4,0)),A2:A7,0),MATCH(H2,A2:E2,0))),H5*INDEX(A2:E7,MATCH(H4,A2:A7,0),MATCH(H2,A2:E2,0)))</TD></TR><TR><TD>H8</TD><TD>=IF(ISERROR((ROUNDDOWN(H3/H4,0)*INDEX(A2:E7,MATCH(H4,A2:A7,0),MATCH(H2,A2:E2,0))+(H5-ROUNDDOWN(H3/H4,0))*INDEX(A2:E7,MATCH((H5-ROUNDDOWN(H3/H4,0)),A2:A7,0),MATCH(H2,A2:E2,0)))),H5*INDEX(A2:E7,MATCH(H4,A2:A7,0),MATCH(H2,A2:E2,0)),(ROUNDDOWN(H3/H4,0)*INDEX(A2:E7,MATCH(H4,A2:A7,0),MATCH(H2,A2:E2,0))+(H5-ROUNDDOWN(H3/H4,0))*INDEX(A2:E7,MATCH((H5-ROUNDDOWN(H3/H4,0)),A2:A7,0),MATCH(H2,A2:E2,0))))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>