A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Charts and Charting
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Graphing occurances of conditions over time



 
 
Thread Tools Display Modes
  #1  
Old March 9th, 2004, 11:16 AM
Joe Miller
external usenet poster
 
Posts: n/a
Default 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  
Old March 9th, 2004, 03:33 PM
Jon Peltier
external usenet poster
 
Posts: n/a
Default 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  
Old March 9th, 2004, 11:21 PM
Jon Peltier
external usenet poster
 
Posts: n/a
Default 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  
Old March 10th, 2004, 10:41 AM
Joe Miller
external usenet poster
 
Posts: n/a
Default 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  
Old March 10th, 2004, 01:56 PM
Jon Peltier
external usenet poster
 
Posts: n/a
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:07 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.