Finding Min of values in a Data Table

[kankhajoora]

Discount = 30%

B 9.48
A 0.67
B 0.59
A 3.66
B 9.07
A 2.65

I am trying to make a Data Table that changes the Discount % (10 - 100) and finds the Minimum amongst A. With every change in Discount, the Values of A and B changes, and some Bs can become As and so on. They are linked to an Equation.

It looks something like this ..

=(should find Min of all As)
10%
20%
.
.
.
100%


My question is what formula should I use to find the Min and the Max of variable A

 

[Derek Brown]

Hello and welcome to The Board.
If your example values are in columns A & B, rows 1 - 6, perhaps you are looking for something like:
=MIN(IF(A1:A6="A",B1:B6))
However, when you enter the formula do so by pressing:
Ctrl + Shift + Enter
to make it an array formula. This will put curly braces "{}" around the formula so that it will look like:
{=MIN(IF(A1:A6="A",B1:B6))}
You can do the same using MAX.

 

[kankhajoora]

Hey Derek,

Thanks a ton for your help. It worked perfectly. Can you kindly explain what exactly are the curly brackets meant for in this formula ?

Best Regards

 

[Derek Brown]

First of all note that with the IF statement in the example, there was no third argument (the 'false' action).
Perhaps it would be better to point you at a description of array-formulas:
The one from Microsoft: http://office.microsoft.com/en-us/e...d-examples-of-array-formulas-HA010228458.aspx
And from the Expert, with some very good examples and a workbook to download:
http://www.cpearson.com/excel/ArrayFormulas.aspx
After reading these, use "Evaluate Formula" on the solution given to see how Excel calculates the result.