3

How do I find the peak value within a 5, 10 or 15 second time period if I have a range of data like the one below? In other words, which 5, 10 or 15 second period has the highest utilization and what was the average utilization during that period.

Time stamp  % Processor Time
10:27:12    18,74988
10:27:13    4,68747
10:27:14    20,31237
10:27:15    99,99936
10:27:16    37,49976
10:27:17    78,1245
10:27:18    78,1245
10:27:19    98,43687
10:27:20    71,87454
10:27:21    67,18707
10:27:22    60,93711
10:27:23    35,93727
10:27:24    0
10:27:25    37,49976
10:27:26    60,93711
10:27:27    54,68715
10:27:28    57,81213
10:27:29    67,18707
10:27:30    81,24948
10:27:31    76,56201
10:27:32    92,18691
10:27:33    82,81197
10:27:34    78,1245
10:27:35    73,43703
10:27:36    84,37446
10:27:37    89,06193
10:27:38    81,24948
10:27:39    84,37446
10:27:40    39,06225
10:27:41    20,31237
10:27:42    46,87449376

The list can be anywhere from 30 to several 1000 of lines.

Thanks in advance.

Improve this question
3
  • 1
    Do you want to be able to enter a single time and get those results, or would you want to have extra columns in the data table ( or a new table) which calculates for each 5 second block?
    – Pynner
    Jul 4, 2013 at 11:24
  • I'm only interested in the overall result as in 1 value for the e.g. 5 second period that had the highest utilization.
    – Michael G
    Jul 4, 2013 at 11:36
  • 1
    Doing this with worksheet functions will require intermediate calculations of the type Pynner is talking about. I am assuming that's okay with you, but that no special effort to put those calculation in presentable form is required. (You could get a one-shot answer with VBA code, but that's a question more suited to StackOver--and they would want to see what code you've tried to get your answer.)
    – chuff
    Jul 4, 2013 at 18:20

1 Answer 1

Reset to default
0

Assuming data range is A2:B32, try formulas below for average and peak period for 15s period, confirmed with CTRL+SHIFT+ENTER:

D2: =MAX(SUMIFS(B2:B32,A2:A32,">="&FLOOR(A2:A32,"0:0:15"),A2:A32,"<"&CEILING(A2:A32,"0:0:15")))/15

D3: =INDEX(A2:A32,MATCH(D2,SUMIFS(B2:B32,A2:A32,">="&FLOOR(A2:A32,"0:0:15"),A2:A32,"<"&CEILING(A2:A32,"0:0:15"))/15,0)-1)

Replace 15 by 5 or 10 for other time periods.

Improve this answer
2
  • 1
    Could you please edit your post to explain what this code does/how it works? It's helpful for future visitors to not only see what the solution is, but why it works that way.
    – nhinkle
    Jul 6, 2013 at 1:21
  • Sorry for the very late response. I'm trying to validate your response, but I get an "The formula you typed contains an error" message, and the "B32,A2" part of the D2 formula is then highlighted. In the D3 formula, it's the "A32,MATCH" part that is highlighted.
    – Michael G
    Feb 25, 2014 at 13:37

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .