trying to match charge based on weight break and zone

[steve400243]

Hello, Please see below, I am trying to get the correct total to cell O3 based on weight from L3, and Zone M3. Rate is per hundred pounds. and there is a MIN and MAX charge for each Zone. Thanks for any help.

[TABLE="width: 886"]
<colgroup><col><col><col><col span="2"><col><col><col><col><col><col><col span="2"><col></colgroup><tbody>[TR]
[TD]Area:[/TD]
[TD] 0 - 1000 [/TD]
[TD] 1001 - 2000 [/TD]
[TD] 2001-3000 [/TD]
[TD] 3001-5000 [/TD]
[TD] 5001-10000 [/TD]
[TD] 10001-20000 [/TD]
[TD] MIN [/TD]
[TD] MAX [/TD]
[TD][/TD]
[TD] Weight [/TD]
[TD] Zone [/TD]
[TD] [/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD] $     4.63[/TD]
[TD] $           4.53[/TD]
[TD] $         4.43[/TD]
[TD] $         4.33[/TD]
[TD] $           4.23[/TD]
[TD] $             4.13[/TD]
[TD] $ 40.00[/TD]
[TD] $ 200.00[/TD]
[TD][/TD]
[TD="align: right"]1,500[/TD]
[TD="align: right"]4[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD] $     4.78[/TD]
[TD] $           4.68[/TD]
[TD] $         4.58[/TD]
[TD] $         4.48[/TD]
[TD] $           4.38[/TD]
[TD] $             4.28[/TD]
[TD] $ 41.82[/TD]
[TD] $ 210.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD] $     4.93[/TD]
[TD] $           4.83[/TD]
[TD] $         4.73[/TD]
[TD] $         4.63[/TD]
[TD] $           4.53[/TD]
[TD] $             4.43[/TD]
[TD] $ 43.64[/TD]
[TD] $ 220.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD] $     5.08[/TD]
[TD] $           4.98[/TD]
[TD] $         4.88[/TD]
[TD] $         4.78[/TD]
[TD] $           4.68[/TD]
[TD] $             4.58[/TD]
[TD] $ 45.46[/TD]
[TD] $ 230.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD] $     5.23[/TD]
[TD] $           5.13[/TD]
[TD] $         5.03[/TD]
[TD] $         4.93[/TD]
[TD] $           4.83[/TD]
[TD] $             4.73[/TD]
[TD] $ 47.28[/TD]
[TD] $ 240.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD] $     5.38[/TD]
[TD] $           5.28[/TD]
[TD] $         5.18[/TD]
[TD] $         5.08[/TD]
[TD] $           4.98[/TD]
[TD] $             4.88[/TD]
[TD] $ 49.10[/TD]
[TD] $ 250.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD] $     5.53[/TD]
[TD] $           5.43[/TD]
[TD] $         5.33[/TD]
[TD] $         5.23[/TD]
[TD] $           5.13[/TD]
[TD] $             5.03[/TD]
[TD] $ 50.92[/TD]
[TD] $ 260.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD] $     5.68[/TD]
[TD] $           5.58[/TD]
[TD] $         5.48[/TD]
[TD] $         5.38[/TD]
[TD] $           5.28[/TD]
[TD] $             5.18[/TD]
[TD] $ 52.74[/TD]
[TD] $ 270.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD] $     5.83[/TD]
[TD] $           5.73[/TD]
[TD] $         5.63[/TD]
[TD] $         5.53[/TD]
[TD] $           5.43[/TD]
[TD] $             5.33[/TD]
[TD] $ 54.56[/TD]
[TD] $ 280.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD] $     5.98[/TD]
[TD] $           5.88[/TD]
[TD] $         5.78[/TD]
[TD] $         5.68[/TD]
[TD] $           5.58[/TD]
[TD] $             5.48[/TD]
[TD] $ 56.38[/TD]
[TD] $ 290.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[L. Howard]

Does this do it for you?

Where the B1:I1 (row 1 headers) are 1000, 2000, 3000, 5000, 10000, 20000, MIN, MAX

Howard

Excel 2012
	K	L	M	N
1	Weight	Zone		Total
2	1,500	4		5.08
3			MIN	45.46
4			MAX	230
Sheet4

Cell Formulas
Range	Formula
N2	=INDEX($B$2:$G$11,MATCH($L$2,$A$2:$A$11,0),MATCH(K2,$B$1:$G$1,1))
N3	=INDEX($H$2:$I$11,MATCH($L$2,$A$2:$A$10,0),MATCH(M3,$H$1:$I$1,0))
N4	=INDEX($H$2:$I$11,MATCH($L$2,$A$2:$A$10,0),MATCH(M4,$H$1:$I$1,0))

 

[L. Howard]

Actually that is not a correct solution. Does not account for less than 1000 weights.

I will rework it.

Howard

 

[L. Howard]

This looks like it will work.

Where the B1:I1 (row 1 headers) are 0, 1001, 2001, 3001, 5001, 10001, MIN, MAX

Howard

Excel 2012
	K	L	M	N
1	Weight	Zone		Total
2	335	4		5.08
3			MIN	45.46
4			MAX	230
Sheet1

Cell Formulas
Range	Formula
N2	=INDEX($B$2:$G$11,MATCH($L$2,$A$2:$A$11,0),MATCH(K2,$B$1:$G$1,1))
N3	=INDEX($H$2:$I$11,MATCH($L$2,$A$2:$A$11,0),MATCH(M3,$H$1:$I$1,0))
N4	=INDEX($H$2:$I$11,MATCH($L$2,$A$2:$A$11,0),MATCH(M4,$H$1:$I$1,0))

 

[steve400243]

Hello L. Howard, Thanks for looking at this. The total should be $74.70, for 1,500 pounds Zone 4 ($4.98 * 1500 / 100). For 335 #, it would be the min charge of $45.46. Does that make more sense as to what total I am trying to get?

 

[L. Howard]

Okay, I see.

Here is a nifty little formula to solve that.

Excel 2012
	K	L	M
1	Weight	Zone	Total
2	335	9	54.56
Sheet1

Cell Formulas
Range	Formula
M2	=MAX(MIN(AA1,AA3),AA2)

And perhaps with this OFF SCREEN if you prefer.

Excel 2012
	Z	AA
1	100/wt $	19.53
2	MIN	54.56
3	MAX	280
Sheet1

Cell Formulas
Range	Formula
AA1	=INDEX($B$2:$G$11,MATCH($L$2,$A$2:$A$11,0),MATCH(K2,$B$1:$G$1,1))*(K2)/100
AA2	=INDEX($H$2:$I$11,MATCH($L$2,$A$2:$A$11,0),MATCH(Z2,$H$1:$I$1,0))
AA3	=INDEX($H$2:$I$11,MATCH($L$2,$A$2:$A$11,0),MATCH(Z3,$H$1:$I$1,0))

Howard

 

[steve400243]

Great thanks this will work for me. I have not seen the MAX(MIN formula so easy before, the one I tried to put together was very long,. Thanks for your help I appreciate it.

 

[L. Howard]

You're welcome, glad it works for you.

I have overlooked the MAX(MIN myself trying to rely on IF/AND/OR formulas. You are aware the MAX & MIN in the formula are not referring to the cells labels with the text of MAX or MIN, but rather to the AA column values on the sheet.

The cell labels values could be HI / LO or Mish / Mash etc.

Howard

 

[steve400243]

You're welcome, glad it works for you.

I have overlooked the MAX(MIN myself trying to rely on IF/AND/OR formulas. You are aware the MAX & MIN in the formula are not referring to the cells labels with the text of MAX or MIN, but rather to the AA column values on the sheet.

The cell labels values could be HI / LO or Mish / Mash etc.

Howard

Hi Howard, Yeah I saw that about the AA column values. thanks again I Appreciate your time.