Find min and max value for a group of rows

Hi Experts,

I know how to do this in Access, but just can't figure it out in Excel. I hope it's possible. I need to be able to calculate the minimum and maximum values from column B and grouped by column A as shown below.

MinAndMaxValues.xlsx
Who is Participating?

ERP System ManagerCommented:
Pivot table would do it for you.  Create a pivot table from the insert menu for the whole set of data.  Add column A to the rows, then drag column B to values - then change it from Sum or Count (whichever it defaults to), to MAX.  The drag it into values again and change it to MIN.
0

Commented:
Try these array formulas:

=MIN(IF(A:A=A2,B:B))
=MAX(IF(A:A=A2,B:B))

After you enter the formula, press Ctrl+Shift+Enter

Flyster
0

Budget AnalystCommented:
No points here...Flyster is correct
0

ConsultantAuthor Commented:
Well, I am in a bit of a quandary here. The Pivot table option and the array formula option both work just fine for the question I asked. However, what I did not include in my question is that there are going to be thousands of rows in my data and then I need to calculate the difference between the min and max.

When using arrays I could then create a simple formula to calculate the difference between min and max. However, having thousands of rows with arrays causes a big time calculation process that never seems to finish.

When using the pivot table option, calculating the min and max values are very simple. However, I've tried using the "insert calculated field" option to figure the difference between min and max, but that does not seem possible.

Should I close this question out by splitting the points and then ask a new question?
0

Commented:
Assuming your data can be sorted on the TestID column as in your example, you can use these formulas in C2 and D2 respectively:
=IF(\$A2=\$A1,C1,MIN(INDEX(\$B2:\$B\$15,MATCH(\$A2,\$A2:\$A\$15,0)):INDEX(\$B2:\$B\$15,MATCH(\$A2,\$A2:\$A15,1))))
=IF(\$A2=\$A1,C1,MAX(INDEX(\$B2:\$B\$15,MATCH(\$A2,\$A2:\$A\$15,0)):INDEX(\$B2:\$B\$15,MATCH(\$A2,\$A2:\$A15,1))))

and copy down. (no array-entry)

Calculation time shouldn't be too bad. Note that when adjusting the ranges to match your real data, the references to row 2 are relative not absolute (no \$ before the 2) but the end row (15 here) is absolute.
1

Budget AnalystCommented:
There are three options: (1) The Excel database functions such as Dmin, Dmax, Dsum resolve the calculation bottlenecks created with array formulas or (2) Pivot Tables as an interim step in your process and (3) Formulaic solutions such as Rory's.

-The database functions are a bit tedious to set up, but they are very reliable and fast.
-Pivot tables work just as well. I wouldn't be concerned with breaking down your calculation process into multiple steps...if Pivot Tables get you there then I'd use them. And lastly,
-A good resource to understanding formulaic solutions is to get some background on Excel's calculation engine such as here http://www.decisionmodels.com/calcsecrets.htm
0

Budget AnalystCommented:
Oh, and calculated fields never seem to work well...don't do it
0

Commented:
Calculated fields work fine as long as you understand their primary limitation - namely that they only operate on the SUM of a field.
0

ConsultantAuthor Commented:
So sorry for not clearing this up sooner. I have been out of pocket for a while. Both solutions work just fine for the question I asked. Thanks for the help and again, sorry for not getting to this sooner.
0

Commented: