Graphing occurances of conditions over time

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi there

I attempted to post this yesterday, but the post doesn't seem to have been made. Apologies if I'm being stupid and missed it

I'm attempting to chart a scatter graph on data in excel 2000. The data is organised in a database style table. Each record (in rows) covers the occurance of an event. There are many different pieces of information regarding each event but the two I'm focussing on are the date of the event (in column H) and the category of the event, which can be either C, I or E (recorded in column C). There may be more than one C, I or E event on a date, the dataset is not sequential (i.e data does not exist for every date) but is ordered chronologically

What I'm trying to do is to plot a scatter graph (or similar) to highligh the frequency of occurances of C, I and/or E events throughout the time period covered for the dataset (1/1/03 -31/12/2003.

Can anybody advise me if there is a way in which Excel will do this for me now without having to change the organisation of my data, or is there an easy way to re-organise the data to produce the desired output

Much obliged for any assistance

Jo
 
Joe -

Are there multiple occurrences within one date for each category (would
you be interested in a Count of events on a certain date?) Or would you
just want a marker representing a category on the dates when an event in
that category occurred?

- Jon
 
Hi Jon, thanks for the respons

Multiple occurances do exist, ie there could be, say, three records which have a category I indicated in column C and all sharing the same date

I realise now, following a bit of playing that a pivot table/ chart may be able to achive this. However, in this manner the pivot table only plots on the graph, or in the table, those dates for which data is input. Ideally, I want to see included in the visual representation those dates where no data is included (i.e I have a number of records for 5/1/04, a number of records for 7/1/04 but no records for 6/1/04) - I want Excel to interpret the days for which no records exist as 0 so that I can see the frequency of occurances of the categories as a part of the month/ year etc

Thanks for your help on this
Jo
 
Joe -

I only have Excel 97 here at work, so I can't check on the pivot chart
behavior. In a pivot chart of line chart type, can you coerce a time
scale axis? Given the lack of options in a pivot chart, I suspect you
can't, other wise this would give you the dates you want.

It's possible to make a regular chart from pivot table data, although it
won't update so nicely if the table gains or loses rows and columns.
Start the chart wizard after selecting a blank cell away from the pivot
table. On step 2, select data using the Series tab, and continue with
the rest of the wizard.

- Jon
 
Jon

Using the regular chart idea you posited seems to do the trick for these purposes. I'm suitably impressed! Thanks very much for your help

Joe
 
Back
Top