[Question] Yet Another Excel Question

Bubble181

Bubble181

...And I don't even think it's a hard one. What I want to do seems crazy easy and obvious, but I can't quite figure it out. I'm sure I'm just missing something.
I have a list of about 150,000 records, of all kinds of alarms over the past months. One column is the date, oen column is the time (hh:mm:ss), one the client, one the ID number of the camera that created the alarm.
Now, I don't actually really need to know where the alarm's from. What *do* want to know is when the most alarms go off, by hour and day of the week. Basically, a graphic showing me when the peak moments are - do we need extra people to deal with alarms on week nights? Or weekend days? Or maybe the peaks are completely random and only dependant on the weather?
So, yeah. I'm fairly sure I have to extract the day-of-the-week info from the date and change the time into a number, but I'm sort of stuck on how to properly graph it out.
 
MindDetective

MindDetective

This is close to what you want, but not exactly: https://superuser.com/questions/541868/plot-date-and-time-of-an-occurrence/541935

The main difference is that you want days of the week instead of the date on the x-axis (which is very doable by making a copy of the column for dates and converting the format to DoW). You should see clusters of the plotted points at peak days and times.

I suspect there is a more friendly way to combine day/time on the x-axis and put "counts" on the y-axis, but I don't see an obvious solution to that at the moment.
 
Denbrought

Denbrought

Sounds like you can use ddd and a pivot table to handle the condensing by day of week (see reference). Similarly you should be able to format the time into just hours, and condense by that.
 
MindDetective

MindDetective

MindDetective said:
This is close to what you want, but not exactly: https://superuser.com/questions/541868/plot-date-and-time-of-an-occurrence/541935

The main difference is that you want days of the week instead of the date on the x-axis (which is very doable by making a copy of the column for dates and converting the format to DoW). You should see clusters of the plotted points at peak days and times.

I suspect there is a more friendly way to combine day/time on the x-axis and put "counts" on the y-axis, but I don't see an obvious solution to that at the moment.
Click to expand...
Oh, duh. You just need two X variables. One is DoW and the other is Time, which gets embedded in DoW from the legend. Then your Y is a count of incidents at that time/DoW.
 
evilmike

evilmike

I would make two new columns. One using the weekday() function on your date, and one using hour() on your time. Then I would use a series of countif() function on each of the new columns to pull out the different values.

EG:

Code: 
	 A			B
Jan 1, 2017  |  =weekday(a1)
Jan 2, 2017  |  =weekday(a2)
...
 
 
 
---------------------------------
Occurrence on day 1		   =countif(b1:b150000,"=1")
Occurrence on day 2		   =countif(b1:b150000,"=2")
...
 
PatrThom

PatrThom

Couldn't you add a new, invisible column for "day of week," populate it with some kind of modulus formula, then key off that for your graph?

--Patrick
 
Eriol

Eriol

Bubble181 said:
...And I don't even think it's a hard one. What I want to do seems crazy easy and obvious, but I can't quite figure it out. I'm sure I'm just missing something.
I have a list of about 150,000 records, of all kinds of alarms over the past months. One column is the date, oen column is the time (hh:mm:ss), one the client, one the ID number of the camera that created the alarm.
Now, I don't actually really need to know where the alarm's from. What *do* want to know is when the most alarms go off, by hour and day of the week. Basically, a graphic showing me when the peak moments are - do we need extra people to deal with alarms on week nights? Or weekend days? Or maybe the peaks are completely random and only dependant on the weather?
So, yeah. I'm fairly sure I have to extract the day-of-the-week info from the date and change the time into a number, but I'm sort of stuck on how to properly graph it out.
Click to expand...
Who's SCADA system and/or monitoring software are you using? I'm just curious. The brand is the main thing.
 
Bubble181

Bubble181

Eriol said:
Who's SCADA system and/or monitoring software are you using? I'm just curious. The brand is the main thing.
Click to expand...
Bwahahaha you think we have separate software for that. That's me taking a file dump from the alarm central's incoming traffic. We use something based on/in Filemaker.
I recently moved from Securitas, one of the biggest security companies in the world, to a relatively small and new start-up in the sector. The amount of data they/we don't have on their/our own core business is staggering.
 
You must log in or register to reply here.
Top