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 -

 

[njimack]

This would have been easier if you'd said what the expected result was for your example.

I think the below works with the example you gave but I haven't tested it with other values.

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:	48
Sheet1

#VALUE!

</td></tr></table></td></tr></table>

 

[excelbetter]

Thanks Neil. I think the result should be $44, not $48 (i.e. 2 boxes @ $17 each, and 1 box @ $10). Since there are 2 boxes going to Zone 3 and the weight of each box is 2 lbs, and there will be 1 box also going to zone 3 and that box is 1 lb.)

This would have been easier if you'd said what the expected result was for your example.

I think the below works with the example you gave but I haven't tested it with other values.

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:	48
Sheet1

#VALUE!

</td></tr></table></td></tr></table>

 

[njimack]

Yeah just realised that - formula corrected below:

=(ROUNDDOWN(Tot_wt/Max_wt,0)*INDEX(Data,MATCH(No_Boxes-(No_Boxes*Max_wt-Tot_wt),Weights,0),MATCH(Zone,Zones,0)))+IF(No_Boxes*Max_wt>Tot_wt,(No_Boxes*Max_wt-Tot_wt)*INDEX(Data,MATCH(No_Boxes*Max_wt-Tot_wt,Weights,0),MATCH(Zone,Zones,0)))

 

[zuslan]

Hi,

I also solved the same puzzle using different formulas:

The approach is the following:
#boxes = IF(MOD(H3;H4)<>0; ROUNDDOWN(H3/H4;0)+1;H3/H4)

b1=IF($H$5>1; $H$4; $H$3)
i.e. if weight of package < max weight of box, use weight of package
b2=IF($H$5>2; $H$4;$H$3-SUM(H7:$H$7))
i.e. if weight of package - weight of b1 < max weight of box, use weight of package - weight of b1. If number of boxes >2 then weight of 2 is maximal
b3=IF($H$5>3; $H$4;$H$3-SUM(H$7:$H8))
b4=IF($H$5>4; $H$4;$H$3-SUM(H$7:$H9))
b5=IF($H$5>5; $H$4;$H$3-SUM(H$7:$H10))

Revenue is computed via
=IF(ISNA(VLOOKUP(H7;$A$2:$E$6;RIGHT($H$2;1)+1));0;VLOOKUP(H7;$A$2:$E$6;RIGHT($H$2;1)+1))
(since z3 is in column 4, I found no better way then to do right(H2;1)=2 and add 1 to that)
I do it for all boxes and sum it up

The only thing this approach does not solve is the optimal allocation of weight to boxes

Best regards,
zuslan

 

[excelbetter]

Thanks zuslan. I don't see the image that should be appearing and am not sure how or where the formula's go (i.e. B1, b2, etc). Any suggestions? I appreciate the help as I am not that familiar with Excel.

Hi,

I also solved the same puzzle using different formulas:

The approach is the following:
#boxes = IF(MOD(H3;H4)<>0; ROUNDDOWN(H3/H4;0)+1;H3/H4)

b1=IF($H$5>1; $H$4; $H$3)
i.e. if weight of package < max weight of box, use weight of package
b2=IF($H$5>2; $H$4;$H$3-SUM(H7:$H$7))
i.e. if weight of package - weight of b1 < max weight of box, use weight of package - weight of b1. If number of boxes >2 then weight of 2 is maximal
b3=IF($H$5>3; $H$4;$H$3-SUM(H$7:$H8))
b4=IF($H$5>4; $H$4;$H$3-SUM(H$7:$H9))
b5=IF($H$5>5; $H$4;$H$3-SUM(H$7:$H10))

Revenue is computed via
=IF(ISNA(VLOOKUP(H7;$A$2:$E$6;RIGHT($H$2;1)+1));0;VLOOKUP(H7;$A$2:$E$6;RIGHT($H$2;1)+1))
(since z3 is in column 4, I found no better way then to do right(H2;1)=2 and add 1 to that)
I do it for all boxes and sum it up

The only thing this approach does not solve is the optimal allocation of weight to boxes

Best regards,
zuslan

 

[excelbetter]

Fantastic. Thank you!

Yeah just realised that - formula corrected below:

=(ROUNDDOWN(Tot_wt/Max_wt,0)*INDEX(Data,MATCH(No_Boxes-(No_Boxes*Max_wt-Tot_wt),Weights,0),MATCH(Zone,Zones,0)))+IF(No_Boxes*Max_wt>Tot_wt,(No_Boxes*Max_wt-Tot_wt)*INDEX(Data,MATCH(No_Boxes*Max_wt-Tot_wt,Weights,0),MATCH(Zone,Zones,0)))

 

[zuslan]

@excelbetter

Another attempt to upload the picture

 

[taurean]

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

 

[excelbetter]

Thanks taurean. Out of curiosity, what are you doing in the first statement where you take H5-1 ? I don't understand what that is for.

Thanks.

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