Formula needed to calculate a rate based on 3 criterias

[vnoreau]

Hello,

I need a formula to return a price based on 3 criteria's. Criteria 1 is the zone, criteria 2 is the weight category within that zone (The weight is in 3 separate categories within each zone) and then the total number of items.

So for example, if the price I need is in Zone 3, for 525 lbs and 7 of them. Then I would be $7,168.00

Hoping someone can help me, lost on this one.

Thank you!

MIN	MAX
0	499	A
500	999	B
1000	1500	C

Zone	Weight Category	1	2	3	4	5	6	7	8	9	10	11	12
1	A	$1.00	$2.00	$3.00	$4.00	$5.00	$6.00	$7.00	$8.00	$9.00	$10.00	$11.00	$12.00
1	B	$2.00	$4.00	$6.00	$8.00	$10.00	$12.00	$14.00	$16.00	$18.00	$20.00	$22.00	$24.00
1	C	$4.00	$8.00	$12.00	$16.00	$20.00	$24.00	$28.00	$32.00	$36.00	$40.00	$44.00	$48.00
1.5	A	$8.00	$16.00	$24.00	$32.00	$40.00	$48.00	$56.00	$64.00	$72.00	$80.00	$88.00	$96.00
1.5	B	$16.00	$32.00	$48.00	$64.00	$80.00	$96.00	$112.00	$128.00	$144.00	$160.00	$176.00	$192.00
1.5	C	$32.00	$64.00	$96.00	$128.00	$160.00	$192.00	$224.00	$256.00	$288.00	$320.00	$352.00	$384.00
2	A	$64.00	$128.00	$192.00	$256.00	$320.00	$384.00	$448.00	$512.00	$576.00	$640.00	$704.00	$768.00
2	B	$128.00	$256.00	$384.00	$512.00	$640.00	$768.00	$896.00	$1,024.00	$1,152.00	$1,280.00	$1,408.00	$1,536.00
2	C	$256.00	$512.00	$768.00	$1,024.00	$1,280.00	$1,536.00	$1,792.00	$2,048.00	$2,304.00	$2,560.00	$2,816.00	$3,072.00
3	A	$512.00	$1,024.00	$1,536.00	$2,048.00	$2,560.00	$3,072.00	$3,584.00	$4,096.00	$4,608.00	$5,120.00	$5,632.00	$6,144.00
3	B	$1,024.00	$2,048.00	$3,072.00	$4,096.00	$5,120.00	$6,144.00	$7,168.00	$8,192.00	$9,216.00	$10,240.00	$11,264.00	$12,288.00
3	C	$2,048.00	$4,096.00	$6,144.00	$8,192.00	$10,240.00	$12,288.00	$14,336.00	$16,384.00	$18,432.00	$20,480.00	$22,528.00	$24,576.00
4	A	$4,096.00	$8,192.00	$12,288.00	$16,384.00	$20,480.00	$24,576.00	$28,672.00	$32,768.00	$36,864.00	$40,960.00	$45,056.00	$49,152.00
4	B	$8,192.00	$16,384.00	$24,576.00	$32,768.00	$40,960.00	$49,152.00	$57,344.00	$65,536.00	$73,728.00	$81,920.00	$90,112.00	$98,304.00
4	C	$16,384.00	$32,768.00	$49,152.00	$65,536.00	$81,920.00	$98,304.00	$114,688.00	$131,072.00	$147,456.00	$163,840.00	$180,224.00	$196,608.00
5	A	$32,768.00	$65,536.00	$98,304.00	$131,072.00	$163,840.00	$196,608.00	$229,376.00	$262,144.00	$294,912.00	$327,680.00	$360,448.00	$393,216.00
5	B	$65,536.00	$131,072.00	$196,608.00	$262,144.00	$327,680.00	$393,216.00	$458,752.00	$524,288.00	$589,824.00	$655,360.00	$720,896.00	$786,432.00
5	C	$131,072.00	$262,144.00	$393,216.00	$524,288.00	$655,360.00	$786,432.00	$917,504.00	$1,048,576.00	$1,179,648.00	$1,310,720.00	$1,441,792.00	$1,572,864.00

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Dave Patton]

a quick thought with a helper column

T202107a.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Zone	3
2	Weight	525
3	Number	7
4	Amount	7168
5
6	Zone	Weight Category	Category	1	2	3	4	5	6	7	8	9	10	11	12
7	1	A	1A	$1.00	$2.00	$3.00	$4.00	$5.00	$6.00	$7.00	$8.00	$9.00	$10.00	$11.00	$12.00
8	1	B	1B	$2.00	$4.00	$6.00	$8.00	$10.00	$12.00	$14.00	$16.00	$18.00	$20.00	$22.00	$24.00
9	1	C	1C	$4.00	$8.00	$12.00	$16.00	$20.00	$24.00	$28.00	$32.00	$36.00	$40.00	$44.00	$48.00
10	1.5	A	1.5A	$8.00	$16.00	$24.00	$32.00	$40.00	$48.00	$56.00	$64.00	$72.00	$80.00	$88.00	$96.00
11	1.5	B	1.5B	$16.00	$32.00	$48.00	$64.00	$80.00	$96.00	$112.00	$128.00	$144.00	$160.00	$176.00	$192.00
12	1.5	C	1.5C	$32.00	$64.00	$96.00	$128.00	$160.00	$192.00	$224.00	$256.00	$288.00	$320.00	$352.00	$384.00
13	2	A	2A	$64.00	$128.00	$192.00	$256.00	$320.00	$384.00	$448.00	$512.00	$576.00	$640.00	$704.00	$768.00
14	2	B	2B	$128.00	$256.00	$384.00	$512.00	$640.00	$768.00	$896.00	$1,024.00	$1,152.00	$1,280.00	$1,408.00	$1,536.00
15	2	C	2C	$256.00	$512.00	$768.00	$1,024.00	$1,280.00	$1,536.00	$1,792.00	$2,048.00	$2,304.00	$2,560.00	$2,816.00	$3,072.00
16	3	A	3A	$512.00	$1,024.00	$1,536.00	$2,048.00	$2,560.00	$3,072.00	$3,584.00	$4,096.00	$4,608.00	$5,120.00	$5,632.00	$6,144.00
17	3	B	3B	$1,024.00	$2,048.00	$3,072.00	$4,096.00	$5,120.00	$6,144.00	$7,168.00	$8,192.00	$9,216.00	$10,240.00	$11,264.00	$12,288.00
18	3	C	3C	$2,048.00	$4,096.00	$6,144.00	$8,192.00	$10,240.00	$12,288.00	$14,336.00	$16,384.00	$18,432.00	$20,480.00	$22,528.00	$24,576.00
19	4	A	4A	$4,096.00	$8,192.00	$12,288.00	$16,384.00	$20,480.00	$24,576.00	$28,672.00	$32,768.00	$36,864.00	$40,960.00	$45,056.00	$49,152.00
20	4	B	4B	$8,192.00	$16,384.00	$24,576.00	$32,768.00	$40,960.00	$49,152.00	$57,344.00	$65,536.00	$73,728.00	$81,920.00	$90,112.00	$98,304.00
21	4	C	4C	$16,384.00	$32,768.00	$49,152.00	$65,536.00	$81,920.00	$98,304.00	$114,688.00	$131,072.00	$147,456.00	$163,840.00	$180,224.00	$196,608.00
22	5	A	5A	$32,768.00	$65,536.00	$98,304.00	$131,072.00	$163,840.00	$196,608.00	$229,376.00	$262,144.00	$294,912.00	$327,680.00	$360,448.00	$393,216.00
23	5	B	5B	$65,536.00	$131,072.00	$196,608.00	$262,144.00	$327,680.00	$393,216.00	$458,752.00	$524,288.00	$589,824.00	$655,360.00	$720,896.00	$786,432.00
24	5	C	5C	$131,072.00	$262,144.00	$393,216.00	$524,288.00	$655,360.00	$786,432.00	$917,504.00	$1,048,576.00	$1,179,648.00	$1,310,720.00	$1,441,792.00	$1,572,860.00
6c

Cell Formulas
Range	Formula
B4	B4	=INDEX(D7:O24,MATCH(B1&LOOKUP(B2,{0,"A";500,"B";1000,"C"}),C7:C24,0),B3)
C7:C24	C7	=A7&B7

 

[James Clear]

a quick thought with a helper column

T202107a.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Zone	3
2	Weight	525
3	Number	7
4	Amount	7168
5
6	Zone	Weight Category	Category	1	2	3	4	5	6	7	8	9	10	11	12
7	1	A	1A	$1.00	$2.00	$3.00	$4.00	$5.00	$6.00	$7.00	$8.00	$9.00	$10.00	$11.00	$12.00
8	1	B	1B	$2.00	$4.00	$6.00	$8.00	$10.00	$12.00	$14.00	$16.00	$18.00	$20.00	$22.00	$24.00
9	1	C	1C	$4.00	$8.00	$12.00	$16.00	$20.00	$24.00	$28.00	$32.00	$36.00	$40.00	$44.00	$48.00
10	1.5	A	1.5A	$8.00	$16.00	$24.00	$32.00	$40.00	$48.00	$56.00	$64.00	$72.00	$80.00	$88.00	$96.00
11	1.5	B	1.5B	$16.00	$32.00	$48.00	$64.00	$80.00	$96.00	$112.00	$128.00	$144.00	$160.00	$176.00	$192.00
12	1.5	C	1.5C	$32.00	$64.00	$96.00	$128.00	$160.00	$192.00	$224.00	$256.00	$288.00	$320.00	$352.00	$384.00
13	2	A	2A	$64.00	$128.00	$192.00	$256.00	$320.00	$384.00	$448.00	$512.00	$576.00	$640.00	$704.00	$768.00
14	2	B	2B	$128.00	$256.00	$384.00	$512.00	$640.00	$768.00	$896.00	$1,024.00	$1,152.00	$1,280.00	$1,408.00	$1,536.00
15	2	C	2C	$256.00	$512.00	$768.00	$1,024.00	$1,280.00	$1,536.00	$1,792.00	$2,048.00	$2,304.00	$2,560.00	$2,816.00	$3,072.00
16	3	A	3A	$512.00	$1,024.00	$1,536.00	$2,048.00	$2,560.00	$3,072.00	$3,584.00	$4,096.00	$4,608.00	$5,120.00	$5,632.00	$6,144.00
17	3	B	3B	$1,024.00	$2,048.00	$3,072.00	$4,096.00	$5,120.00	$6,144.00	$7,168.00	$8,192.00	$9,216.00	$10,240.00	$11,264.00	$12,288.00
18	3	C	3C	$2,048.00	$4,096.00	$6,144.00	$8,192.00	$10,240.00	$12,288.00	$14,336.00	$16,384.00	$18,432.00	$20,480.00	$22,528.00	$24,576.00
19	4	A	4A	$4,096.00	$8,192.00	$12,288.00	$16,384.00	$20,480.00	$24,576.00	$28,672.00	$32,768.00	$36,864.00	$40,960.00	$45,056.00	$49,152.00
20	4	B	4B	$8,192.00	$16,384.00	$24,576.00	$32,768.00	$40,960.00	$49,152.00	$57,344.00	$65,536.00	$73,728.00	$81,920.00	$90,112.00	$98,304.00
21	4	C	4C	$16,384.00	$32,768.00	$49,152.00	$65,536.00	$81,920.00	$98,304.00	$114,688.00	$131,072.00	$147,456.00	$163,840.00	$180,224.00	$196,608.00
22	5	A	5A	$32,768.00	$65,536.00	$98,304.00	$131,072.00	$163,840.00	$196,608.00	$229,376.00	$262,144.00	$294,912.00	$327,680.00	$360,448.00	$393,216.00
23	5	B	5B	$65,536.00	$131,072.00	$196,608.00	$262,144.00	$327,680.00	$393,216.00	$458,752.00	$524,288.00	$589,824.00	$655,360.00	$720,896.00	$786,432.00
24	5	C	5C	$131,072.00	$262,144.00	$393,216.00	$524,288.00	$655,360.00	$786,432.00	$917,504.00	$1,048,576.00	$1,179,648.00	$1,310,720.00	$1,441,792.00	$1,572,860.00
6c

Cell Formulas
Range	Formula
B4	B4	=INDEX(D7:O24,MATCH(B1&LOOKUP(B2,{0,"A";500,"B";1000,"C"}),C7:C24,0),B3)
C7:C24	C7	=A7&B7

Thank you. I could learn something new today. Really amazing. Hats off to you.
Action speaks louder than the words.??

 

[Dave Patton]

Without the helper column try

T202107a.xlsm
	A	B
1	Zone	3
2	Weight	525
3	Number	7
4	Amount	7168
5
6cc

Cell Formulas
Range	Formula
B4	B4	=INDEX(C7:N24,MATCH(1,(B1=A7:A24)*(LOOKUP(B2,A27:B29)=B7:B24),0),B3)
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Dave Patton]

With Excel 365, the formula does not require array enter.

T202107a.xlsm
	A	B	C
1	Zone	3
2	Weight	525
3	Number	7
4	Amount	7168
6cc

Cell Formulas
Range	Formula
B4	B4	=INDEX(C7:N24,MATCH(1,(B1=A7:A24)*(LOOKUP(B2,{0,"A";500,"B";1000,"C"})=B7:B24),0),B3)

 

[vnoreau]

Thank you. I could learn something new today. Really amazing. Hats off to you.
Action speaks louder than the words.??

Thank you so much! This worked perfectly! Now I need another one similar but a little different.

This one is is dependent on the city name, then by weight category. I have a drop down list to select the city name, then I need to determine what category the weight falls in. Then calculate the rate multiplied by the the weight divided by 100. BUT if the calculation falls below the MIN rate, then the MIN rate is applied.

Does any of that make any sense?

MIN	MAX	Category
0​	499​	A
500​	999​	B
1000​	1999​	C
2000​	4999​	D
5000​	9999​	E
10000​	19999​	F
20000​	29999​	G
30000​	49999​	H

Base Rate Amount $	#N/A
City	CITY NAME
TOTAL Weight	12000

			A	B	C	D	E	F	G
City	State	MIN	0-499	500-999	1000-1999	2000-4999	5000-9999	10000-19999	20000-29999
City Name	State	25.00​	9.00​	8.00​	7.00​	6.00​	5.00​	4.00​	3.00​

​

​

​

​

​

​

​

​