1

I'm analyzing logs where I have the times where a certain event occurs, example:

t=1.05s
t=2.25s
t=2.26s
t=2.35s
t=3.40s

I want to be able to plot the rate of occurrences/second in order to see how often this event occurs over time. How do I accomplish this?

Edit: I would like a scatter plot over time, with the Y axis being (occurences/sec) and the X axis being (time). If there is no better way, then I would like to calculate it every X interval (let's say 2 seconds).

Improve this question
3
  • You're going to have to be way more specific. What kind of plot are you thinking of? Line graph over time? If so, how are you defining your data points? Is there a point on the plot for each occurrence or do you want to calculate the rate at set intervals (e.g. every 2 seconds). Please edit your question to include a better description of what you want.
    – Excellll
    May 28, 2015 at 22:01
  • How are the logs stored? How do you count occurences / second?
    – eirikdaude
    May 29, 2015 at 12:38
  • Logs are stored as a text file but for the purposes of this question, assume they are a single column of floating point values that represent time of occurence in seconds that I can directly paste into an excel spreadsheet. As for the second question, I'm not so sure. That's the part I would like help with!
    – Lanaru
    May 29, 2015 at 13:30

2 Answers 2

Reset to default
0

This can also be done using COUNTIFS. You need to build a range of numbers to be the "buckets". If you just want a quick answer, you can just drag down far enough to capture all your data. If you want a more robust solution, you can add a conditional to the category to see if it is larger than the MAX of the data and #N/A it out so it does not plot.

Picture of results and ranges

picture of ranges

Formulas, are in cells D4 and copied down and E3 and copied down. There is one extra category than counts since they form a boundary. You could also have the extra cell at the top. The value in E1 returns the size of buckets. I copied these down far enough to capture all the data.

D4=D3+$E$1
E3=COUNTIFS($B$3:$B$27,">="&D3,$B$3:$B$27,"<"&D4)
Improve this answer
0
0

Quick way to do this with just Excel features (no formulas/VBA) is via a PivotTable. You can specifically make good use of the Group Field feature. It allows you to count occurences within a given range of values.

Picture of results, the dialog that is displayed came from PivotTable->Analyze->Group Field up in the Ribbon. You need to have one of the times selected in order for this menu to appear.

PivotTable with grouping

The one downside of this approach is that you do not have much (really any) control of the formatting on the chart labels.

The upside of this approach is that you can rapidly change the group interval (open the menu again) without having to touch the data or do much work.

Improve this answer
2
  • Great approach. Only problem I have: it's not showing time slices where no events occur. Let's say nothing happens between 17s and 20s, I would like to see that in the graph with Y = 0. Right now that time range is completely ommited from the graph. Any way of fixing this?
    – Lanaru
    May 29, 2015 at 20:20
  • I don't think so. If you want the 0s I think you will need to go with a COUNTIF approach. The downside is that you will have to define the groups but it's not hard. I'll post that approach.
    – Byron Wall
    May 29, 2015 at 20:25

You must log in to answer this question.

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