I am needing some help with how best to summarize and chart some raw data. I
extracted from an application we use that tracks appointments and dumped it
into excel. The columns I am interested in a
date
name
appt_start
appt_end

Each name is associated with 4-5 appointments for one date.
I am trying to see how much time is between appointments
An example of the data:

12/01/2004 Smith appt_start appt_end
12/01/2004 Smith appt_start appt_end

12/01/2004 Jones appt_start appt_end
12/01/2004 Jones appt_start appt_end

and so on. There can be up to 20 different names each with up to 4+
appointments for each day. The report covers a 3 month time frame so there is
lots of raw data, exactly 8300 rows.
I have sorted it 2 ways:
1. date/name/appt_start
2. name/date/appt_start

I thought I could put a formula in the next empty column to show the time
from the next appt_start - appt_end from the previous line. This of course
would not be in the first appointment of the day or the last.
What I am struggling with is how to best summarize all this and chart. Maybe
I can't, maybe it is best represented in raw form, but any ideas are truly
appreciated!

Leesa,

Have you tried to use a pivot table and/or pivot chart to group the data?
For as much data as you have, you could group and manipulate and chart data
by date, name, etc. very quickly using these tools.

----
Regards,
John Mansfield
http://www.pdbook.com

"Leesa" wrote:

I am needing some help with how best to summarize and chart some raw data. I
extracted from an application we use that tracks appointments and dumped it
into excel. The columns I am interested in a
date
name
appt_start
appt_end

Each name is associated with 4-5 appointments for one date.
I am trying to see how much time is between appointments
An example of the data:

12/01/2004 Smith appt_start appt_end
12/01/2004 Smith appt_start appt_end

12/01/2004 Jones appt_start appt_end
12/01/2004 Jones appt_start appt_end

and so on. There can be up to 20 different names each with up to 4+
appointments for each day. The report covers a 3 month time frame so there is
lots of raw data, exactly 8300 rows.
I have sorted it 2 ways:
1. date/name/appt_start
2. name/date/appt_start

I thought I could put a formula in the next empty column to show the time
from the next appt_start - appt_end from the previous line. This of course
would not be in the first appointment of the day or the last.
What I am struggling with is how to best summarize all this and chart. Maybe
I can't, maybe it is best represented in raw form, but any ideas are truly
appreciated!

I have little to no experience with them, although I had a feeling that was
going to be the way to go. Any tips as to how to do a pivot table?

"John Mansfield" wrote:

Leesa,

Have you tried to use a pivot table and/or pivot chart to group the data?
For as much data as you have, you could group and manipulate and chart data
by date, name, etc. very quickly using these tools.

----
Regards,
John Mansfield
http://www.pdbook.com

"Leesa" wrote:

I am needing some help with how best to summarize and chart some raw data. I
extracted from an application we use that tracks appointments and dumped it
into excel. The columns I am interested in a
date
name
appt_start
appt_end

Each name is associated with 4-5 appointments for one date.
I am trying to see how much time is between appointments
An example of the data:

12/01/2004 Smith appt_start appt_end
12/01/2004 Smith appt_start appt_end

12/01/2004 Jones appt_start appt_end
12/01/2004 Jones appt_start appt_end

and so on. There can be up to 20 different names each with up to 4+
appointments for each day. The report covers a 3 month time frame so there is
lots of raw data, exactly 8300 rows.
I have sorted it 2 ways:
1. date/name/appt_start
2. name/date/appt_start

I thought I could put a formula in the next empty column to show the time
from the next appt_start - appt_end from the previous line. This of course
would not be in the first appointment of the day or the last.
What I am struggling with is how to best summarize all this and chart. Maybe
I can't, maybe it is best represented in raw form, but any ideas are truly
appreciated!

On Sun, 27 Mar 2005, in microsoft.public.excel.charting,
Leesa said:

I am needing some help with how best to summarize and chart some raw data. I
extracted from an application we use that tracks appointments and dumped it
into excel. The columns I am interested in a
date
name
appt_start
appt_end

Each name is associated with 4-5 appointments for one date.
I am trying to see how much time is between appointments

May I suggest you add some extra columns

appt_num
appt_duration
next_appt

the first would be which appointment of the day that is for which
person, which you say is unlikely to be more than four appointments. You
can do it automatically with a combination of array formulas, IF(), and
RANK(), but it's probably better if you just add it by hand.

The second will be the difference between appt_start and appt_end. The
third is the time between two appointments, which is the difference
between appt_end of one appointment and appt_start of the next
appointment.

Finally, if you make a pivot chart to look like this (for one day)

++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++++++++++
+ Appt + 1 + 2 + 3 + 4 +
++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++++++++++
+ Name + dura + next + dura + next + dura + next + dura + next +
++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++++++++++
+ + + + + + + + + +
+ + + + + + + + + +

You should be able to make a stacked bar chart with the durations of
each appointment in a different colour, and the time to next appoint
with no colour at all, like a Gantt chart. Your database feeds the
pivot table and your pivot table feeds the chart, giving a visual
picture of the day with everyone's appointments lined up against each
other.

--
Del Cotter
Thanks to the recent increase in UBE, I will soon be ignoring email
sent to . Please send your email to del2 instead.