Share via

Return values between time range

Anonymous
2024-03-20T20:58:38+00:00

Hi, I'm struggling to produce an idea of how to return value and then run certain calculations.

In the example below, I have two columns. Time and numbers

  • first, I would like to return values between 11:00:00 and 12:00:00, all the way down (the Tiem column represents 5-minute intervals of 24 hours),

To be more specific I would like to return values for each day, between 10:00:00 and 11:00:00

  • second, out of the returned values, I would like to return to the next columns only values that are greater than the top cell.

(the table below is an example of a few thousand data points that represent days)

10:50:00 2369.2
10:55:00 2368.3
11:00:00 2370
11:05:00 2369.2
11:10:00 2368.2
11:15:00 2370.9
11:20:00 2370.2
11:25:00 2366.3
11:30:00 2367
11:35:00 2367.8
11:40:00 2371
11:45:00 2368.7
11:50:00 2367.3
11:55:00 2374.2
12:00:00 2373.3
12:05:00 2367.8
12:10:00 2367.8
12:15:00 2364.4
12:20:00 2361.9
Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

46 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2024-03-21T12:29:23+00:00

    Is this OK:?

    Please, link to a workbook with this in; a picture is very hard work and we don't know what's behind or in (apparently) blank cells.

    Also what version of Excel are you using?

    Was this answer helpful?

    0 comments
  2. Anonymous
    2024-03-21T06:05:53+00:00

    I apologize for not specifying my question accurately.

    For the sake of example, I have changed the time intervals to 1 hour and the time range to 1–10 hours.

    I also attached the calculations I would like for each time frame.

    The whole purpose of this exercise is to return the highest value if the criteria from columns F and G are met for each period.

    The final results are in columns K and L.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2024-03-21T00:34:39+00:00

    re:

    only values that are greater than the top cell

    I can get you something like this which is values greater than the top cell of each hour,

    Image

    but did you really want to see only values greater than the topmost value of 2369.2? :

    Image

    or perhaps this last in pivot form:

    Image

    Attach a workbook with your realistic data in, and a few expected results, then you'll get a better answer and we won't need to guess (probably wrongly) the layout of your setup.

    By the way, there is no day data in your data above!

    Was this answer helpful?

    0 comments
  4. Anonymous
    2024-03-20T23:05:09+00:00

    Hi,

    Based on the data that you have shared, show the exact result you are expecting.

    Was this answer helpful?

    0 comments
  5. Anonymous
    2024-03-20T22:40:08+00:00

    Hello Wojciech,

    I'm Ibhadighi and I'd happily help you with your question. In this forum, we are Microsoft consumers just like yourself.

    To extract and calculate values between 11:00:00 and 12:00:00 that are greater than the top cell (the first value in the range), I filtered the data accordingly. Here are the values that meet your criteria:

    • 11:15:00 - 2370.9
    • 11:20:00 - 2370.2
    • 11:40:00 - 2371.0
    • 11:55:00 - 2374.2
    • 12:00:00 - 2373.3

    These are the values from each day within the specified time range that are greater than the value at 11:00:00.

    I hope this helps.

    Best Regards, Ibhadighi

    Was this answer helpful?

    0 comments