# Finding a specific set of data, and then the min value within this set

1. ## Finding a specific set of data, and then the min value within this set

Dear all,

I am trying to create a formula that will find a set of numbers within a column, and then find the 'min' value of that specific set. For example, in the data below, I want to find a min amplitude between each time interval written across the top. So an example of this would be the 'min' amplitude between time intervals 0 and 10 would be -33.26416, and the min amplitude between times 11 and 20 would be 0.610.

I need to do this with about 100,000 amplitude data points which are between about 600 time intervals. I have already tried pulling out the amplitudes within a specific time interval to the columns underneath the time intervals, and then getting a min value for each column, but I have way too much data for this to realistically work.

Could anyone please help me with a formula that does not need to be dragged across an area of 100,000 by 600?

Any help on this would be very much appreciated.

Timothy

2. ## Re: Finding a specific set of data, and then the min value within this set

Try this array formula

=MIN(IF((\$A\$4:\$A\$25>=0)*(\$A\$4:\$A\$25<=10),\$B\$4:\$B\$25))

Then change the 0 to 11, and 10 to 20 for the next formula, etc..

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

3. ## Re: Finding a specific set of data, and then the min value within this set

Thanks for the answer, it works perfectly.

I now have another problem I was wondering if you could help me with. When I pull out the amplitude values, do you know of a way that I can also get the correspondings time values which they are adjacent to?

I would use an index function (=INDEX(\$A\$4:\$A\$25,MATCH(MAX(C\$3),\$B\$4:\$B\$25,0))), but this is not specific enough. It uses the harnesed amplitude values to find the first match in column B and get the time next to this. In reality, I have mulitple repeats of amplitudes throughout column B, and therefore I will just get the first time value instead of the specific adjacent time.

I hope this message is not too confusing. I have attached another excel file with the new and improved formulas.

Thanks again

Tim

4. ## Re: Finding a specific set of data, and then the min value within this set

look this workbook and give the answer
min with varible amplitude.xlsx

5. ## Re: Finding a specific set of data, and then the min value within this set

this is my workbook
keep last lettets.xlsx

6. ## Re: Finding a specific set of data, and then the min value within this set

downlood and see

7. ## Re: Finding a specific set of data, and then the min value within this set

=INDEX(\$A:\$A,MIN(IF((\$A\$4:\$A\$25>C2)*(\$A\$4:\$A\$25<=D2)*(\$B\$4:\$B\$25=C3),ROW(\$B\$4:\$B\$25))))
TRY THIS ARRAY FORMULA IN C4 and copy towards right

8. ## Re: Finding a specific set of data, and then the min value within this set

Thank you for all your help. The formula works perfectly.

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1