Best way for multiple condition to be meet for answer

[OzDude]

All,

I am trying to find the best way to get the correct answer from the below table. Basically the answer will depend on 2 things:

• The percentage range in A & B
• The value the product is between.

I have two cells in another sheet, one states the percentage, and the other the amount. What I need is an easy way to get if cell F17 falls into one if the below percentage ranges, and cell F16 is between any of those amounts, then calculate the answer.

EG. F17 = 83.4% & F16 = 570,000. The answer would be 570,000*0.801= 4,565.70

<tbody>
[TD="class: xl75"][/TD]
[TD="class: xl76"][/TD]
[TD="class: xl69, width: 85"] 500,000[/TD]
[TD="class: xl69, width: 67"] 600,000[/TD]
[TD="class: xl70, width: 67"] 750,000[/TD]

[TD="class: xl65, align: right"]80.01%[/TD]
[TD="class: xl66, align: right"]81.00%[/TD]
[TD="class: xl71, align: right"]0.540%[/TD]
[TD="class: xl71, align: right"]0.894%[/TD]
[TD="class: xl72, align: right"]0.931%[/TD]

[TD="class: xl65, align: right"]81.01%[/TD]
[TD="class: xl66, align: right"]82.00%[/TD]
[TD="class: xl71, align: right"]0.540%[/TD]
[TD="class: xl71, align: right"]0.894%[/TD]
[TD="class: xl72, align: right"]0.931%[/TD]

[TD="class: xl65, align: right"]82.01%[/TD]
[TD="class: xl66, align: right"]83.00%[/TD]
[TD="class: xl71, align: right"]0.568%[/TD]
[TD="class: xl71, align: right"]0.922%[/TD]
[TD="class: xl72, align: right"]1.071%[/TD]

[TD="class: xl65, align: right"]83.01%[/TD]
[TD="class: xl66, align: right"]84.00%[/TD]
[TD="class: xl71, align: right"]0.801%[/TD]
[TD="class: xl71, align: right"]0.922%[/TD]
[TD="class: xl72, align: right"]1.071%[/TD]

[TD="class: xl65, align: right"]84.01%[/TD]
[TD="class: xl66, align: right"]85.00%[/TD]
[TD="class: xl71, align: right"]0.857%[/TD]
[TD="class: xl71, align: right"]1.146%[/TD]
[TD="class: xl72, align: right"]1.323%[/TD]

[TD="class: xl65, align: right"]85.01%[/TD]
[TD="class: xl66, align: right"]86.00%[/TD]
[TD="class: xl71, align: right"]0.997%[/TD]
[TD="class: xl71, align: right"]1.146%[/TD]
[TD="class: xl72, align: right"]1.323%[/TD]

[TD="class: xl65, align: right"]86.01%[/TD]
[TD="class: xl66, align: right"]87.00%[/TD]
[TD="class: xl71, align: right"]1.006%[/TD]
[TD="class: xl71, align: right"]1.388%[/TD]
[TD="class: xl72, align: right"]1.602%[/TD]

[TD="class: xl65, align: right"]87.01%[/TD]
[TD="class: xl66, align: right"]88.00%[/TD]
[TD="class: xl71, align: right"]1.267%[/TD]
[TD="class: xl71, align: right"]1.388%[/TD]
[TD="class: xl72, align: right"]1.602%[/TD]

[TD="class: xl65, align: right"]88.01%[/TD]
[TD="class: xl66, align: right"]89.00%[/TD]
[TD="class: xl71, align: right"]1.369%[/TD]
[TD="class: xl71, align: right"]1.919%[/TD]
[TD="class: xl72, align: right"]2.180%[/TD]

[TD="class: xl65, align: right"]89.01%[/TD]
[TD="class: xl66, align: right"]90.00%[/TD]
[TD="class: xl71, align: right"]1.723%[/TD]
[TD="class: xl71, align: right"]1.919%[/TD]
[TD="class: xl72, align: right"]2.180%[/TD]

[TD="class: xl65, align: right"]90.01%[/TD]
[TD="class: xl66, align: right"]91.00%[/TD]
[TD="class: xl71, align: right"]2.581%[/TD]
[TD="class: xl71, align: right"]3.028%[/TD]
[TD="class: xl72, align: right"]3.578%[/TD]

[TD="class: xl65, align: right"]91.01%[/TD]
[TD="class: xl66, align: right"]92.00%[/TD]
[TD="class: xl71, align: right"]2.637%[/TD]
[TD="class: xl71, align: right"]3.028%[/TD]
[TD="class: xl72, align: right"]3.578%[/TD]

[TD="class: xl65, align: right"]92.01%[/TD]
[TD="class: xl66, align: right"]93.00%[/TD]
[TD="class: xl71, align: right"]2.981%[/TD]
[TD="class: xl71, align: right"]3.401%[/TD]
[TD="class: xl72, align: right"]3.783%[/TD]

[TD="class: xl65, align: right"]93.01%[/TD]
[TD="class: xl66, align: right"]94.00%[/TD]
[TD="class: xl71, align: right"]2.981%[/TD]
[TD="class: xl71, align: right"]3.401%[/TD]
[TD="class: xl72, align: right"]4.025%[/TD]

[TD="class: xl67, align: right"]94.01%[/TD]
[TD="class: xl68, align: right"]95.00%[/TD]
[TD="class: xl73, align: right"]3.298%[/TD]
[TD="class: xl73, align: right"]3.401%[/TD]
[TD="class: xl74, align: right"]4.370%[/TD]

</tbody>

So basically if have tried the IF/AND combination, and while it works, it's a huge to maintain, and if the boss changes the percentage amounts, or adds more by making the ranges smaller, it kills the formula.

Just seeing if there is a better way.

Regards
Oz

 

[Marcelo Branco]

Welcome to Mr Excel

Try something like this (using comma as decimal and dot as thousands separator)


[Table="class: grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][td]

H​

[/td][td]

I​

[/td][/tr]
[tr][td]

1​

[/td][td] [/td][td] [/td][td]

500.000​

[/td][td]

600.000​

[/td][td]

700.000​

[/td][td] [/td][td]

%​

[/td][td]

Amount​

[/td][td]

Result​

[/td][/tr]

[tr][td]

2​

[/td][td]

80,01%​

[/td][td]

81,00%​

[/td][td]

0,540%​

[/td][td]

0,894%​

[/td][td]

0,931%​

[/td][td] [/td][td]

83,40%​

[/td][td]

570.000​

[/td][td]

4.565,70​

[/td][/tr]

[tr][td]

3​

[/td][td]

81,01%​

[/td][td]

82,00%​

[/td][td]

0,540%​

[/td][td]

0,894%​

[/td][td]

0,931%​

[/td][td] [/td][td] [/td][td] [/td][td] [/td][/tr]

[tr][td]

4​

[/td][td]

82,01%​

[/td][td]

83,00%​

[/td][td]

0,568%​

[/td][td]

0,922%​

[/td][td]

1,071%​

[/td][td] [/td][td] [/td][td] [/td][td] [/td][/tr]

[tr][td]

5​

[/td][td]

83,01%​

[/td][td]

84,00%​

[/td][td]

0,801%​

[/td][td]

0,922%​

[/td][td]

1,071%​

[/td][td] [/td][td] [/td][td] [/td][td] [/td][/tr]

[tr][td]

6​

[/td][td]

84,01%​

[/td][td]

85,00%​

[/td][td]

0,857%​

[/td][td]

1,146%​

[/td][td]

1,323%​

[/td][td] [/td][td] [/td][td] [/td][td] [/td][/tr]

[tr][td]

7​

[/td][td]

85,01%​

[/td][td]

86,00%​

[/td][td]

0,997%​

[/td][td]

1,146%​

[/td][td]

1,323%​

[/td][td] [/td][td] [/td][td] [/td][td] [/td][/tr]

[tr][td]

8​

[/td][td]

86,01%​

[/td][td]

87,00%​

[/td][td]

1,006%​

[/td][td]

1,388%​

[/td][td]

1,602%​

[/td][td] [/td][td] [/td][td] [/td][td] [/td][/tr]

[tr][td]

9​

[/td][td]

87,01%​

[/td][td]

88,00%​

[/td][td]

1,267%​

[/td][td]

1,388%​

[/td][td]

1,602%​

[/td][td] [/td][td] [/td][td] [/td][td] [/td][/tr]

[tr][td]

10​

[/td][td]

88,01%​

[/td][td]

89,00%​

[/td][td]

1,369%​

[/td][td]

1,919%​

[/td][td]

2,180%​

[/td][td] [/td][td] [/td][td] [/td][td] [/td][/tr]

[tr][td]

11​

[/td][td]

89,01%​

[/td][td]

90,00%​

[/td][td]

1,723%​

[/td][td]

1,919%​

[/td][td]

2,180%​

[/td][td] [/td][td] [/td][td] [/td][td] [/td][/tr]

[tr][td]

12​

[/td][td]

90,01%​

[/td][td]

91,00%​

[/td][td]

2,581%​

[/td][td]

3,028%​

[/td][td]

3,578%​

[/td][td] [/td][td] [/td][td] [/td][td] [/td][/tr]

[tr][td]

13​

[/td][td]

91,01%​

[/td][td]

92,00%​

[/td][td]

2,637%​

[/td][td]

3,028%​

[/td][td]

3,578%​

[/td][td] [/td][td] [/td][td] [/td][td] [/td][/tr]

[tr][td]

14​

[/td][td]

92,01%​

[/td][td]

93,00%​

[/td][td]

2,981%​

[/td][td]

3,401%​

[/td][td]

3,783%​

[/td][td] [/td][td] [/td][td] [/td][td] [/td][/tr]

[tr][td]

15​

[/td][td]

93,01%​

[/td][td]

94,00%​

[/td][td]

2,981%​

[/td][td]

3,401%​

[/td][td]

4,025%​

[/td][td] [/td][td] [/td][td] [/td][td] [/td][/tr]

[tr][td]

16​

[/td][td]

94,01%​

[/td][td]

95,00%​

[/td][td]

3,298%​

[/td][td]

3,401%​

[/td][td]

4,370%​

[/td][td] [/td][td] [/td][td] [/td][td] [/td][/tr]
[/table]

Formula in I2
=H2*INDEX($C$2:$E$16,MATCH(G2,$A$2:$A$16),MATCH(H2,$C$1:$E$1))

Hope this helps

M.

 

[OzDude]

Thanks Marcelo,

Changed the formula a touch to match my setup, but the basics were exactly as you had them.

Regards
Oz