Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to create scatter plot
I am using Excel 2000.
I would like to create a scatter plot of Types of Calls received throughout the day. The end result is for me to see at what time of day are the various types of calls concentrated in. Thus, the X-axis would be the 24-hour range, starting from 00:00hr on left-end to 00:00hr on right-end. I don't know what should be on Y-axis. but I do want my scatter dots to be differentiated by Call Types. What is the best way for me to structure my data table, i.e. what columns do I need? I suppose at the minimum, I need these columns: 1. Call Type (e.g. Defect; Query) 2. Time of Call 3. Date of Call I do not require the date of the call to be displayed in the scatter plot but I do want to sum up all the calls received over the week for each hour of the day. Thanks. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to create scatter plot
I would have thought that a stacked bar chart would be more
appropriate than an XY scatter. On the Y axis you would have the count (number) and the X axis would be the time slots. So in text format it would look like this: d x xdxd dxx xxxxx xxdxxdd xxxxxxxxxxxxxxxxxx where the x might be for query and d for defect (not sure how this will look when I post it). Hope this helps. Pete On Dec 16, 9:56*am, Melissa wrote: I am using Excel 2000. I would like to create a scatter plot of Types of Calls received throughout the day. *The end result is for me to see at what time of day are the various types of calls concentrated in. Thus, the X-axis would be the 24-hour range, starting from 00:00hr on left-end to 00:00hr on right-end. * I don't know what should be on Y-axis. * but I do want my scatter dots to be differentiated by Call Types. * What is the best way for me to structure my data table, i.e. what columns do I need? I suppose at the minimum, I need these columns: 1. Call Type (e.g. Defect; Query) 2. Time of Call 3. Date of Call I do not require the date of the call to be displayed in the scatter plot but I do want to sum up all the calls received over the week for each hour of the day. Thanks. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to create scatter plot
Yeah, I was thinking about that after posting the comment. I think it would
be better if I have: X-axis: Call Type Y-axis: Time of day. So it would look something like this: d x d x d x x --------------x-axis Def Que Question is: how do I structure my data table to get this? "Pete_UK" wrote: I would have thought that a stacked bar chart would be more appropriate than an XY scatter. On the Y axis you would have the count (number) and the X axis would be the time slots. So in text format it would look like this: d x xdxd dxx xxxxx xxdxxdd xxxxxxxxxxxxxxxxxx where the x might be for query and d for defect (not sure how this will look when I post it). Hope this helps. Pete On Dec 16, 9:56 am, Melissa wrote: I am using Excel 2000. I would like to create a scatter plot of Types of Calls received throughout the day. The end result is for me to see at what time of day are the various types of calls concentrated in. Thus, the X-axis would be the 24-hour range, starting from 00:00hr on left-end to 00:00hr on right-end. I don't know what should be on Y-axis. but I do want my scatter dots to be differentiated by Call Types. What is the best way for me to structure my data table, i.e. what columns do I need? I suppose at the minimum, I need these columns: 1. Call Type (e.g. Defect; Query) 2. Time of Call 3. Date of Call I do not require the date of the call to be displayed in the scatter plot but I do want to sum up all the calls received over the week for each hour of the day. Thanks. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to create scatter plot
Well, if your raw data is organised as:
Column A - Call Type (e.g. D or Q) Column B - Time of Call Column C - Date of Call then you would need another table elsewhere in the sheet to summarise this data for each week - you would need 3 columns, one for time (in 1 hour intervals), one for the Ds and another for the Qs, and then you could have a SUMPRODUCT formula to count the number of calls within each interval for each type. From this you can produce your graph. Hope this helps. Pete On Dec 16, 10:32*am, Melissa wrote: Yeah, I was thinking about that after posting the comment. *I think it would be better if I have: X-axis: Call Type Y-axis: Time of day. So it would look something like this: * d * * *x * d * * *x * d * * * * * x * * * * * x --------------x-axis *Def *Que Question is: how do I structure my data table to get this? * "Pete_UK" wrote: I would have thought that a stacked bar chart would be more appropriate than an XY scatter. On the Y axis you would have the count (number) and the X axis would be the time slots. So in text format it would look like this: * * * * * *d * * * * * *x * * * * *xdxd * * * dxx * * * * xxxxx * xxdxxdd * * *xxxxxxxxxxxxxxxxxx where the x might be for query and d for defect (not sure how this will look when I post it). Hope this helps. Pete On Dec 16, 9:56 am, Melissa wrote: I am using Excel 2000. I would like to create a scatter plot of Types of Calls received throughout the day. *The end result is for me to see at what time of day are the various types of calls concentrated in. Thus, the X-axis would be the 24-hour range, starting from 00:00hr on left-end to 00:00hr on right-end. * I don't know what should be on Y-axis. * but I do want my scatter dots to be differentiated by Call Types. * What is the best way for me to structure my data table, i.e. what columns do I need? I suppose at the minimum, I need these columns: 1. Call Type (e.g. Defect; Query) 2. Time of Call 3. Date of Call I do not require the date of the call to be displayed in the scatter plot but I do want to sum up all the calls received over the week for each hour of the day. Thanks.- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to create scatter plot
Thanks but can you explain how I then create the graph? Which ranges should
be X-axis and which should be Y-axis? "Pete_UK" wrote: Well, if your raw data is organised as: Column A - Call Type (e.g. D or Q) Column B - Time of Call Column C - Date of Call then you would need another table elsewhere in the sheet to summarise this data for each week - you would need 3 columns, one for time (in 1 hour intervals), one for the Ds and another for the Qs, and then you could have a SUMPRODUCT formula to count the number of calls within each interval for each type. From this you can produce your graph. Hope this helps. Pete On Dec 16, 10:32 am, Melissa wrote: Yeah, I was thinking about that after posting the comment. I think it would be better if I have: X-axis: Call Type Y-axis: Time of day. So it would look something like this: d x d x d x x --------------x-axis Def Que Question is: how do I structure my data table to get this? "Pete_UK" wrote: I would have thought that a stacked bar chart would be more appropriate than an XY scatter. On the Y axis you would have the count (number) and the X axis would be the time slots. So in text format it would look like this: d x xdxd dxx xxxxx xxdxxdd xxxxxxxxxxxxxxxxxx where the x might be for query and d for defect (not sure how this will look when I post it). Hope this helps. Pete On Dec 16, 9:56 am, Melissa wrote: I am using Excel 2000. I would like to create a scatter plot of Types of Calls received throughout the day. The end result is for me to see at what time of day are the various types of calls concentrated in. Thus, the X-axis would be the 24-hour range, starting from 00:00hr on left-end to 00:00hr on right-end. I don't know what should be on Y-axis. but I do want my scatter dots to be differentiated by Call Types. What is the best way for me to structure my data table, i.e. what columns do I need? I suppose at the minimum, I need these columns: 1. Call Type (e.g. Defect; Query) 2. Time of Call 3. Date of Call I do not require the date of the call to be displayed in the scatter plot but I do want to sum up all the calls received over the week for each hour of the day. Thanks.- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to create scatter plot
Highlight the data for the times, D and Q calls and click on the Chart
Wizard icon. In this you can click a Chart type (eg Bar (horizontal) or Column (Vertical)) then you can click on the little picture for the sub-type (eg stacked rather than side-by-side). At this point you can click the Press and Hold to View Sample button to see what your graph will look like. If you are happy with this then click the Next button and the Wizard will take you through different settings on several panels (eg headings for the graph, labels for the axes etc). Note that Excel will automatically take the first column (times) as the X axis (although you could change this). Hope this helps. Pete On Dec 16, 2:51*pm, Melissa wrote: Thanks but can you explain how I then create the graph? *Which ranges should be X-axis and which should be Y-axis? "Pete_UK" wrote: Well, if your raw data is organised as: Column A - *Call Type (e.g. D or Q) Column B - *Time of Call Column C - *Date of Call then you would need another table elsewhere in the sheet to summarise this data for each week - you would need 3 columns, one for time (in 1 hour intervals), one for the Ds and another for the Qs, and then you could have a SUMPRODUCT formula to count the number of calls within each interval for each type. From this you can produce your graph. Hope this helps. Pete On Dec 16, 10:32 am, Melissa wrote: Yeah, I was thinking about that after posting the comment. *I think it would be better if I have: X-axis: Call Type Y-axis: Time of day. So it would look something like this: * d * * *x * d * * *x * d * * * * * x * * * * * x --------------x-axis *Def *Que Question is: how do I structure my data table to get this? * "Pete_UK" wrote: I would have thought that a stacked bar chart would be more appropriate than an XY scatter. On the Y axis you would have the count (number) and the X axis would be the time slots. So in text format it would look like this: * * * * * *d * * * * * *x * * * * *xdxd * * * dxx * * * * xxxxx * xxdxxdd * * *xxxxxxxxxxxxxxxxxx where the x might be for query and d for defect (not sure how this will look when I post it). Hope this helps. Pete On Dec 16, 9:56 am, Melissa wrote: I am using Excel 2000. I would like to create a scatter plot of Types of Calls received throughout the day. *The end result is for me to see at what time of day are the various types of calls concentrated in. Thus, the X-axis would be the 24-hour range, starting from 00:00hr on left-end to 00:00hr on right-end. * I don't know what should be on Y-axis. * but I do want my scatter dots to be differentiated by Call Types. * What is the best way for me to structure my data table, i.e. what columns do I need? I suppose at the minimum, I need these columns: 1. Call Type (e.g. Defect; Query) 2. Time of Call 3. Date of Call I do not require the date of the call to be displayed in the scatter plot but I do want to sum up all the calls received over the week for each hour of the day. Thanks.- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to create scatter plot
Figured it out myself and posting this in case it's useful to other folks:
This is my data table: No. Date of Call Time of Call Type of Call 1 1 Jan 2am 1 2 1 Jan 3am 2 3 2 Jan 1am 1 4 2 Jan 4am 2 Where Type of Call 1 = Defect 2 = Query I've had to use numbers in column "Type of Call" in order to get a scatter diagram which showed the calls spread over the 24-hours for each Type of Call. So, X Values = C1:C4 Y Values = D1:D4 and the resulting graph looks something like this 2 | * * * 1 | * * * * * * 0 | -------------------------------------- 0:00h 1:00h 2:00h 3:00h "Melissa" wrote: I am using Excel 2000. I would like to create a scatter plot of Types of Calls received throughout the day. The end result is for me to see at what time of day are the various types of calls concentrated in. Thus, the X-axis would be the 24-hour range, starting from 00:00hr on left-end to 00:00hr on right-end. I don't know what should be on Y-axis. but I do want my scatter dots to be differentiated by Call Types. What is the best way for me to structure my data table, i.e. what columns do I need? I suppose at the minimum, I need these columns: 1. Call Type (e.g. Defect; Query) 2. Time of Call 3. Date of Call I do not require the date of the call to be displayed in the scatter plot but I do want to sum up all the calls received over the week for each hour of the day. Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
XY scatter plot | Charts and Charting in Excel | |||
How to Automatically Create Scatter Plot Charts | Charts and Charting in Excel | |||
3D Scatter plot | Excel Worksheet Functions | |||
Can I copy x-y scatter plot data direct from one plot to another? | Charts and Charting in Excel | |||
how do I create a 3 dimensional plot, for example XYZ scatter plo. | Charts and Charting in Excel |