Minimum values from datasets in excel


I would be grateful if anyone could assist with the following excel problem

I have a spreadsheet (attached) which contains an extract of some sample data.

What I need to do is obtain minimum values.

 - Each test (MainTest in column A) produces multiple datasets which have values as (A1-A5 and B1-B5)

 - Each MainTest can have a "Subtest" (e.g. set1, set2, set3 etc).

I require to get minimum values for each Maintest and any subtest out of the datasets.

please see "desired result" tab.

I have tried pivot tables without any success but require a solution that does not need VBA as I need to use in in many different excel files. The number of Maintests, subsets and datasets can vary.

is this possible

thanks in advance
Who is Participating?
byundtConnect With a Mentor Commented:
Try array-entering a formula like:

To array-enter a formula:
1. Click in the formula bar
2.  Hold the Control and Shift keys down
3.  Hit Enter, then release all three keys
4.  Excel should respond by adding curly braces { } surrounding your formula. If not, try again.

The reference to row 2000 in the formula above is arbitrary, and may extend beyond your data. When I tried to make it an entire column, however, my install of Excel ran out of resources (and it would have taken an eon to recalculate if I did have the resources).
forever7Author Commented:
I should add that the number of datasets is usually around 10-15 (I have only included 4 as a sample).

The number of MainTests can be in the hundreds (possibly over 1000).
Dan CraciunIT ConsultantCommented:
What's wrong with using MIN()? Attached is a file where I modified columns B and C to take values from the "data" sheet and the D-M columns have as values min values from "data" sheet.

forever7Author Commented:
thanks for reply Dan,

take TestMain 370940 for example,

it has 7 rows. (4 set2's and 3 set1's)

I need the minimum values for

370940 set1
370940 set2

also values with a "---" are greater than 2000 and therefore have not been calculated. (sorry i didn't point this out earlier).
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.