Formula needed to calculate a rate based on 3 criterias

vnoreau · Jul 23, 2021

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 · Jul 23, 2021

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 · Jul 23, 2021

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 · Jul 23, 2021

Dave Patton said:
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 · Jul 23, 2021

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 · Jul 24, 2021

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 · Jul 26, 2021

James Clear said:
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

Formula needed to calculate a rate based on 3 criterias

vnoreau

New Member

Excel Facts

Fluff

MrExcel MVP, Moderator

Dave Patton

Well-known Member

James Clear

Board Regular

Dave Patton

Well-known Member

Dave Patton

Well-known Member

vnoreau

New Member

Forum statistics

Share this page

Formula needed to calculate a rate based on 3 criterias

vnoreau

New Member

Excel Facts

Fluff

MrExcel MVP, Moderator

Dave Patton

Well-known Member

James Clear

Board Regular

Dave Patton

Well-known Member

Dave Patton

Well-known Member

vnoreau

New Member

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock