If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Graphing occurances of conditions over time
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. Joe |
#2
|
|||
|
|||
Graphing occurances of conditions over time
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 ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ Joe Miller wrote: 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. Joe |
#3
|
|||
|
|||
Graphing occurances of conditions over time
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 Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ Joe Miller wrote: Hi Jon, thanks for the response 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! Joe |
#4
|
|||
|
|||
Graphing occurances of conditions over time
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 |
#5
|
|||
|
|||
Graphing occurances of conditions over time
Joe -
My pleasure! - Jon Joe Miller wrote: 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 |
Thread Tools | |
Display Modes | |
|
|