Minimum values from datasets in excel

Hello,

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
data.xlsx
forever7Asked:
Who is Participating?
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.

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).
0
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.

HTH,
Dan
try1.xlsx
0
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).
0
byundtMechanical EngineerCommented:
Try array-entering a formula like:
=MIN(IF((data!$A$3:$A$2000=$B5)*(data!$B$3:$B$2000=$C5)*(data!$C$2:$AP$2=D$4),data!$C$3:$AP$2000,""))

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).
dataQ28264166.xlsx
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
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.