Timescale display in Excel

August 25, 2008 12:01 PM

Excel/Graphing - Time vs. usage (y/n) graph

Oh great mefites. How do I create a graph that has on the X axis discrete items, such as "LIGHT A, LIGHT B, LIGHT C", and on the Y axis a timescale.

Basically I would like to be able to visually share usage patterns of various devices that are either being used, or not used.

The result would look something like this.

This would be relevant for many Y/N items, such as "what do the usage patterns of my 40 workstations at my business look like", or "what lights are on", or "what ports are on/off on my switch during the day".

I specifically want to be able to individually review items as show, I do not want an additive line graph/bar graph that one cannot make assumptions by looking horizontally.

Any tool is fair game.

posted by SirStan to Computers & Internet (6 answers total)

The short answer is "Make a PivotTable". Without knowing what your raw data looks like, it is tough to say how easy or hard that might be. I suspect that it would not be difficult assuming that the raw data is somewhat column-based.

So, what does your data look like?
posted by milqman at 12:21 PM on August 25, 2008

Any tool is fair game.

Whiteboard and markers?

But seriously, from the looks of your screen shot it seems like generating an HTML table might be easier than using a spreadsheet, particularly if it's going to be generated automatically. On Windows people could subscribe to it as a web desktop item.
posted by XMLicious at 12:28 PM on August 25, 2008

You can use conditional formatting to a make a graph like this.

Suppose you have the following data:

.......A…………………..B………………..C
1:..Date……………....Jan 1…………Jan 2
2:..Apartment_1…..Yes……………No
3:……………………….

In cell B3, you would go to format --> conditional formatting:
Click the box so it says condition 1, “Formula is” instead of “Cell value is” and then for the formula type in =B2=”Yes” and then click the format button and choose the pattern tab and select a colour. Cell B3 should now change colour. Copy cell B3, and the paste special --> format into cell C3. If you change C2 to “Yes”, cell C3 should change colour.
posted by Jasper Friendly Bear at 12:31 PM on August 25, 2008

Response by poster: The data is in a database, I can format it in anyway. I would prefer a tool that can make up for my lack of any artistic talent, and be a general purpose tool to use in the future. An HTML table really isnt my cup of tea.
posted by SirStan at 1:43 PM on August 25, 2008

That is the best possible incoming format for generating a PivotTable.

Query for all of the records you desire (making whatever time you seek sufficiently coarse in scale to group with).

Copy and paste the result into excel
Label the columns appropriately
Highlight all of the data
From the "Data" menu, select "PivotTable and PivotChart Report ..."
Click "Layout" after "Next"ing the first couple of dialog boxes
Drag your "date/time" field to the top/"Column" portion of the layout
Drag your "light/port/apartment" field to the side/"Row" portion of the layout
Then drag whatever the "status" field is to the middle/"Data" portion of the layout

Try this for the first go, and see how you want it to be different. You can use a number of different aspects of the data term, if they are relevant (sum, count, average . . .). Also, you may find that you want the time axis to be a little more or less coarse to best convey the information you want to convey.

Take a crack at it and see how it goes.
posted by milqman at 5:59 PM on August 25, 2008

Building on what milqman gave you there you could do some scripting to pull the data straight from the database in lieu of cutting and pasting, so that the spreadsheet is updated automatically. Here's the book that article came from.
posted by XMLicious at 10:44 AM on August 26, 2008