+ Reply to Thread
Results 1 to 8 of 8

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

  1. #1
    Registered User
    Join Date
    04-15-2015
    Location
    Leicester, Englan
    MS-Off Ver
    7
    Posts
    11

    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
    Last edited by timothy040888; 04-15-2015 at 11:29 AM.

  2. #2
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    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. #3
    Registered User
    Join Date
    04-15-2015
    Location
    Leicester, Englan
    MS-Off Ver
    7
    Posts
    11

    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. #4
    Forum Contributor
    Join Date
    04-15-2015
    Location
    beirut
    MS-Off Ver
    2010
    Posts
    107

    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. #5
    Forum Contributor
    Join Date
    04-15-2015
    Location
    beirut
    MS-Off Ver
    2010
    Posts
    107

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

    this is my workbook
    keep last lettets.xlsx

  6. #6
    Forum Contributor
    Join Date
    04-15-2015
    Location
    beirut
    MS-Off Ver
    2010
    Posts
    107

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

    downlood and see

  7. #7
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    7,947

    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
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  8. #8
    Registered User
    Join Date
    04-15-2015
    Location
    Leicester, Englan
    MS-Off Ver
    7
    Posts
    11

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Finding specific data in cell comments and finding the line# in comments it appears on
    By tv69 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 11-11-2014, 04:26 PM
  2. [SOLVED] Finding a specific row and inserting data
    By RorschachDK in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 03-06-2014, 03:53 PM
  3. finding specific data in a row
    By fozzy9222 in forum Excel General
    Replies: 2
    Last Post: 05-26-2010, 04:42 PM
  4. finding specific data
    By steveori in forum Excel General
    Replies: 2
    Last Post: 08-12-2008, 05:40 AM
  5. [SOLVED] Finding Specific Data and modifying it
    By Reggie in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-20-2005, 01:05 PM

Bookmarks

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