Results 1 to 6 of 6

Thread: Commands in a changing interval

  1. #1

    Commands in a changing interval



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi there,

    I have got the following problem - it would be great if somebody could help me with this:

    I need to find the maximum values of different parts of a large excel column. Currently, the excel data is divided in columns with (simplified) the following headings:
    Time (min) Value (eg. Temperature)

    Now, I know the starttime and endtime of a certain interval (eg. INTERVAL 1: 09:00 - 10:00, so 60 minutes, INTERVAL 2: 10:00 - 10:20, so 20 minutes, etc.), and I want to find the corresponding maximum value IN that shift (so in this case, the maximum temperature from 9:00 until 10:00, from 10:00 until 10:15, etc).

    Explained in another way; I want to create a command ("give the maximum value", or "give the average" or whatever) which searches in a column that starts at a certain value (I got those values in another column) and searches until a certain value (idem).

    Would be great if somebody can help me here.

    Fidodido

  2. #2
    Here more about the data:
    I got two files:

    1) shows the start time and end time of intervals
    START TIME (hh:mm) END TIME (hh:mm)
    09:00 10:00
    10:00 10:20
    10:40 12:00
    etc etc


    2) shows the time (hh:mm) and the corresponding value at that time (temperature)
    TIME (hh:mm) Temperature (C)
    09:00 10
    09:01 10,2
    09:02 10,2
    09:03 10,3
    ... ...
    10:00 11
    etc etc


    I know that I can correlate the starttime and endtime with the Time column with a VLOOKUP function, which will give the temperature at the start time and end time. But I hope it is clear that I do not want only these values, but I want the maximum value between those timepoints.

  3. #3
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,429
    Articles
    0
    Excel Version
    Excel 2016
    Assuming your table 1 is in Sheet1, starting with headers at A1 and B1, and assuming table 2 is in Sheet2, A1:B8, try in Sheet1, C2:

    =MAX(IF(Sheet2!$A$2:$A$8>=A2,IF(Sheet2!$A$2:$A$8<=B2,Sheet2!$B$2:$B$8)))

    adjust the sheetnames and ranges to suit, then you must confirm this formula with CTRL+SHIFT+ENTER, not just ENTER, then copy down (this is an array formula)

    You can replace MAX with MIN or AVERAGE or almost any other similar function.

    Note if you have EXCEL 2007 or later, you can use AVERAGEIFS or COUNTIFS, or SUMIFS instead of the array formula.

    e.g.

    =AVERAGEIFS(Sheet2!$B$2:$B$8,Sheet2!$A$2:$A$8,">="&A2,Sheet2!$A$2:$A$8,"<="&B2)


  4. #4
    Quote Originally Posted by nbvc View Post
    assuming your table 1 is in sheet1, starting with headers at a1 and b1, and assuming table 2 is in sheet2, a1:b8, try in sheet1, c2:

    =max(if(sheet2!$a$2:$a$8>=a2,if(sheet2!$a$2:$a$8<=b2,sheet2!$b$2:$b$8)))

    adjust the sheetnames and ranges to suit, then you must confirm this formula with ctrl+shift+enter, not just enter, then copy down (this is an array formula)

    you can replace max with min or average or almost any other similar function.

    Note if you have excel 2007 or later, you can use averageifs or countifs, or sumifs instead of the array formula.

    E.g.

    =averageifs(sheet2!$b$2:$b$8,sheet2!$a$2:$a$8,">="&a2,sheet2!$a$2:$a$8,"<="&b2)
    great! It works! Thank you very much!

  5. #5
    Aaiiii, this additional question is for the enthusiast:

    Table 2 [time | temperature] is not complete; it does not contain a temperature for every minute.
    However, the intervals are starting or ending sometimes ON these missing data points (eg. interval 1. start = 9:00 end = 10:00, but there is no temperature for 9:00, but for 9:01 only).

    Several functions in Excel supply the possibility to approximate the input parameters (eg. vlookup-function) - is it possible to add this into the formula as well?

  6. #6
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,429
    Articles
    0
    Excel Version
    Excel 2016
    It should still work, shouldn't it? It will take the max of all the values that fall between 9:00 and 10:00 am.


Posting Permissions

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