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.

Calculate min and maxMinAndMaxValues.xlsx
Dale LoganConsultantAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mark DamenERP 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
FlysterCommented:
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
DavisroBudget AnalystCommented:
No points here...Flyster is correct
0
Exploring ASP.NET Core: Fundamentals

Learn to build web apps and services, IoT apps, and mobile backends by covering the fundamentals of ASP.NET Core and  exploring the core foundations for app libraries.

Dale LoganConsultantAuthor 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
Rory ArchibaldCommented:
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
DavisroBudget 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
DavisroBudget AnalystCommented:
Oh, and calculated fields never seem to work well...don't do it
0
Rory ArchibaldCommented:
Calculated fields work fine as long as you understand their primary limitation - namely that they only operate on the SUM of a field.
0
Dale LoganConsultantAuthor 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
FlysterCommented:
Glad to help. Thanks.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.