Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.charting
|
|||
|
|||
![]()
My, the sign-on process was annoying! I had to click "allow this" 12 times
after I started counting, just so I could ask this question. In Excel 2007, how do I add multiple series of data to a scatter chart? It appears to only allow you to add one series at a time. I have a worksheet with data in columns. If I add some columns of data to the and want them to appear on the chart, the only way I've found to do this is to right click, choose "select data", "add" and then individually enter the X and Y ranges for each series, one at a time. It has no problem creating a chart with multiple series at one time, why is this different? This was much easier in Excel 2003. Sooooo annoying, but until I can dump this job my workplace makes me use Excel. |
#2
![]()
Posted to microsoft.public.excel.charting
|
|||
|
|||
![]()
1. click somewhere on your chart, but not on a specific object - the data
source of your chart will get highlighted 2. hover your mouse on the corner of the frame that delimitates the data range 3. adjust the size of the data range Now this only works, if the new columns and the previous ones build a range. "Steven K. Smith" wrote: My, the sign-on process was annoying! I had to click "allow this" 12 times after I started counting, just so I could ask this question. In Excel 2007, how do I add multiple series of data to a scatter chart? It appears to only allow you to add one series at a time. I have a worksheet with data in columns. If I add some columns of data to the and want them to appear on the chart, the only way I've found to do this is to right click, choose "select data", "add" and then individually enter the X and Y ranges for each series, one at a time. It has no problem creating a chart with multiple series at one time, why is this different? This was much easier in Excel 2003. Sooooo annoying, but until I can dump this job my workplace makes me use Excel. |
#4
![]()
Posted to microsoft.public.excel.charting
|
|||
|
|||
![]()
Most of the charts I make, and the specific one where the question came up,
are in a seperate sheet, not embeded in a worksheet. Also, the usual case seem to be that "The chart range is too complex to display..." on my charts. When I tried this on another spreadsheet with an embeded chart, it didn't highlight the data ranges. -- I speak for truth, enlightenment and justice, but not for the US Air Force. "Laurent" wrote: 1. click somewhere on your chart, but not on a specific object - the data source of your chart will get highlighted 2. hover your mouse on the corner of the frame that delimitates the data range 3. adjust the size of the data range Now this only works, if the new columns and the previous ones build a range. "Steven K. Smith" wrote: My, the sign-on process was annoying! I had to click "allow this" 12 times after I started counting, just so I could ask this question. In Excel 2007, how do I add multiple series of data to a scatter chart? It appears to only allow you to add one series at a time. I have a worksheet with data in columns. If I add some columns of data to the and want them to appear on the chart, the only way I've found to do this is to right click, choose "select data", "add" and then individually enter the X and Y ranges for each series, one at a time. It has no problem creating a chart with multiple series at one time, why is this different? This was much easier in Excel 2003. Sooooo annoying, but until I can dump this job my workplace makes me use Excel. |
#5
![]()
Posted to microsoft.public.excel.charting
|
|||
|
|||
![]()
Sorry, I thought at first it didn't work correctly, but then I realized I was
testing this on a "line" chart rather than an XY chart. Your suggestion works well for XY, but if you try it on a line chart, it simply puts the selected data at the beginning of the chart, regardless of where the data you'd selected occured in the original chart. Thanks for the help. -- I speak for truth, enlightenment and justice, but not for the US Air Force. "Jon Peltier" wrote: Copy the range of data you want to add, select the chart, and use Paste Special to add the data as new series. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Steven K. Smith" <Steven K. wrote in message ... My, the sign-on process was annoying! I had to click "allow this" 12 times after I started counting, just so I could ask this question. In Excel 2007, how do I add multiple series of data to a scatter chart? It appears to only allow you to add one series at a time. I have a worksheet with data in columns. If I add some columns of data to the and want them to appear on the chart, the only way I've found to do this is to right click, choose "select data", "add" and then individually enter the X and Y ranges for each series, one at a time. It has no problem creating a chart with multiple series at one time, why is this different? This was much easier in Excel 2003. Sooooo annoying, but until I can dump this job my workplace makes me use Excel. |
#6
![]()
Posted to microsoft.public.excel.charting
|
|||
|
|||
![]()
In a line chart, all series use the category values defined for the first
series. If you want two series to use different X values, you need to provide all X values, with gaps where either series has no corresponding Y values. The data looks like this, select the entire range, including blanks, and make the chart. X Y1 Y2 1 2 2 3 3 4 4 5 5 3 6 4 7 5 8 6 If the two series overlap, arrange like so, and go to Tools menu Options Chart Tab, and choose Interpolate for how Excel should deal with empty cells. X Y1 Y2 1 2 2 3 3 3 4 4 5 4 6 5 7 5 8 6 - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Steven K. Smith" wrote in message ... Sorry, I thought at first it didn't work correctly, but then I realized I was testing this on a "line" chart rather than an XY chart. Your suggestion works well for XY, but if you try it on a line chart, it simply puts the selected data at the beginning of the chart, regardless of where the data you'd selected occured in the original chart. Thanks for the help. -- I speak for truth, enlightenment and justice, but not for the US Air Force. "Jon Peltier" wrote: Copy the range of data you want to add, select the chart, and use Paste Special to add the data as new series. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Steven K. Smith" <Steven K. wrote in message ... My, the sign-on process was annoying! I had to click "allow this" 12 times after I started counting, just so I could ask this question. In Excel 2007, how do I add multiple series of data to a scatter chart? It appears to only allow you to add one series at a time. I have a worksheet with data in columns. If I add some columns of data to the and want them to appear on the chart, the only way I've found to do this is to right click, choose "select data", "add" and then individually enter the X and Y ranges for each series, one at a time. It has no problem creating a chart with multiple series at one time, why is this different? This was much easier in Excel 2003. Sooooo annoying, but until I can dump this job my workplace makes me use Excel. |
#7
![]()
Posted to microsoft.public.excel.charting
|
|||
|
|||
![]()
How do I add lots of series? I need to plot data for up to 1000 series in a
single chart, with each series representing a single customer. I'm basically trying to graph buying behavior of customers, where I map lots of customer purchases on a chart to see what trends pop out. So my data might look like this: Customer Days Widgets A 1 1000 A 300 3000 B 200 200 B 1000 10000 So customer A bought 1000 widgets on Day 1 and 3000 widgets on Day 300. Customer B bought 200 widgets on Day 200, and so on. The challenge is that I don't know how many customers I will be graphing, and each customer may buy on a different day. "Jon Peltier" wrote: In a line chart, all series use the category values defined for the first series. If you want two series to use different X values, you need to provide all X values, with gaps where either series has no corresponding Y values. The data looks like this, select the entire range, including blanks, and make the chart. X Y1 Y2 1 2 2 3 3 4 4 5 5 3 6 4 7 5 8 6 If the two series overlap, arrange like so, and go to Tools menu Options Chart Tab, and choose Interpolate for how Excel should deal with empty cells. X Y1 Y2 1 2 2 3 3 3 4 4 5 4 6 5 7 5 8 6 - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Steven K. Smith" wrote in message ... Sorry, I thought at first it didn't work correctly, but then I realized I was testing this on a "line" chart rather than an XY chart. Your suggestion works well for XY, but if you try it on a line chart, it simply puts the selected data at the beginning of the chart, regardless of where the data you'd selected occured in the original chart. Thanks for the help. -- I speak for truth, enlightenment and justice, but not for the US Air Force. "Jon Peltier" wrote: Copy the range of data you want to add, select the chart, and use Paste Special to add the data as new series. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Steven K. Smith" <Steven K. wrote in message ... My, the sign-on process was annoying! I had to click "allow this" 12 times after I started counting, just so I could ask this question. In Excel 2007, how do I add multiple series of data to a scatter chart? It appears to only allow you to add one series at a time. I have a worksheet with data in columns. If I add some columns of data to the and want them to appear on the chart, the only way I've found to do this is to right click, choose "select data", "add" and then individually enter the X and Y ranges for each series, one at a time. It has no problem creating a chart with multiple series at one time, why is this different? This was much easier in Excel 2003. Sooooo annoying, but until I can dump this job my workplace makes me use Excel. |
#8
![]()
Posted to microsoft.public.excel.charting
|
|||
|
|||
![]()
An Excel chart is limited to 255 series. You need to rethink what you want
to chart. Does each customer need its own series? Are you interested in widgets per day, cumulative widgets, or something else? - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "rparker" wrote in message ... How do I add lots of series? I need to plot data for up to 1000 series in a single chart, with each series representing a single customer. I'm basically trying to graph buying behavior of customers, where I map lots of customer purchases on a chart to see what trends pop out. So my data might look like this: Customer Days Widgets A 1 1000 A 300 3000 B 200 200 B 1000 10000 So customer A bought 1000 widgets on Day 1 and 3000 widgets on Day 300. Customer B bought 200 widgets on Day 200, and so on. The challenge is that I don't know how many customers I will be graphing, and each customer may buy on a different day. "Jon Peltier" wrote: In a line chart, all series use the category values defined for the first series. If you want two series to use different X values, you need to provide all X values, with gaps where either series has no corresponding Y values. The data looks like this, select the entire range, including blanks, and make the chart. X Y1 Y2 1 2 2 3 3 4 4 5 5 3 6 4 7 5 8 6 If the two series overlap, arrange like so, and go to Tools menu Options Chart Tab, and choose Interpolate for how Excel should deal with empty cells. X Y1 Y2 1 2 2 3 3 3 4 4 5 4 6 5 7 5 8 6 - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Steven K. Smith" wrote in message ... Sorry, I thought at first it didn't work correctly, but then I realized I was testing this on a "line" chart rather than an XY chart. Your suggestion works well for XY, but if you try it on a line chart, it simply puts the selected data at the beginning of the chart, regardless of where the data you'd selected occured in the original chart. Thanks for the help. -- I speak for truth, enlightenment and justice, but not for the US Air Force. "Jon Peltier" wrote: Copy the range of data you want to add, select the chart, and use Paste Special to add the data as new series. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Steven K. Smith" <Steven K. wrote in message ... My, the sign-on process was annoying! I had to click "allow this" 12 times after I started counting, just so I could ask this question. In Excel 2007, how do I add multiple series of data to a scatter chart? It appears to only allow you to add one series at a time. I have a worksheet with data in columns. If I add some columns of data to the and want them to appear on the chart, the only way I've found to do this is to right click, choose "select data", "add" and then individually enter the X and Y ranges for each series, one at a time. It has no problem creating a chart with multiple series at one time, why is this different? This was much easier in Excel 2003. Sooooo annoying, but until I can dump this job my workplace makes me use Excel. |
#9
![]()
Posted to microsoft.public.excel.charting
|
|||
|
|||
![]()
You raise some valid points. I can probably get each chart down to 255
stages by breaking out customers by geographic region, customer size, etc. If I can do this, then what is the most effective way to graph out all the customer series? I was looking on your Web site for an example but couldn't find anything that jumped out at me. Could I do this with a Series Formula? "Jon Peltier" wrote: An Excel chart is limited to 255 series. You need to rethink what you want to chart. Does each customer need its own series? Are you interested in widgets per day, cumulative widgets, or something else? - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "rparker" wrote in message ... How do I add lots of series? I need to plot data for up to 1000 series in a single chart, with each series representing a single customer. I'm basically trying to graph buying behavior of customers, where I map lots of customer purchases on a chart to see what trends pop out. So my data might look like this: Customer Days Widgets A 1 1000 A 300 3000 B 200 200 B 1000 10000 So customer A bought 1000 widgets on Day 1 and 3000 widgets on Day 300. Customer B bought 200 widgets on Day 200, and so on. The challenge is that I don't know how many customers I will be graphing, and each customer may buy on a different day. "Jon Peltier" wrote: In a line chart, all series use the category values defined for the first series. If you want two series to use different X values, you need to provide all X values, with gaps where either series has no corresponding Y values. The data looks like this, select the entire range, including blanks, and make the chart. X Y1 Y2 1 2 2 3 3 4 4 5 5 3 6 4 7 5 8 6 If the two series overlap, arrange like so, and go to Tools menu Options Chart Tab, and choose Interpolate for how Excel should deal with empty cells. X Y1 Y2 1 2 2 3 3 3 4 4 5 4 6 5 7 5 8 6 - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Steven K. Smith" wrote in message ... Sorry, I thought at first it didn't work correctly, but then I realized I was testing this on a "line" chart rather than an XY chart. Your suggestion works well for XY, but if you try it on a line chart, it simply puts the selected data at the beginning of the chart, regardless of where the data you'd selected occured in the original chart. Thanks for the help. -- I speak for truth, enlightenment and justice, but not for the US Air Force. "Jon Peltier" wrote: Copy the range of data you want to add, select the chart, and use Paste Special to add the data as new series. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Steven K. Smith" <Steven K. wrote in message ... My, the sign-on process was annoying! I had to click "allow this" 12 times after I started counting, just so I could ask this question. In Excel 2007, how do I add multiple series of data to a scatter chart? It appears to only allow you to add one series at a time. I have a worksheet with data in columns. If I add some columns of data to the and want them to appear on the chart, the only way I've found to do this is to right click, choose "select data", "add" and then individually enter the X and Y ranges for each series, one at a time. It has no problem creating a chart with multiple series at one time, why is this different? This was much easier in Excel 2003. Sooooo annoying, but until I can dump this job my workplace makes me use Excel. |
#10
![]()
Posted to microsoft.public.excel.charting
|
|||
|
|||
![]()
It depends on what you want to show. As I asked...
Does each customer need its own series? Are you interested in widgets per day, cumulative widgets, or something else? Another thing you need is legibility, but your posts don't indicate that you're too concerned with that. What you need to think about includes: * do I understand my client? - do I understand the data? - do I understand what they need to learn from the chart? - do I know how they currently display the data? * what are my plotted values? * how do I segment the data? - what are the categories? dates? parts? customer? something else? - how do I define my series? customer? part number? classifications of parts? - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "rparker" wrote in message ... You raise some valid points. I can probably get each chart down to 255 stages by breaking out customers by geographic region, customer size, etc. If I can do this, then what is the most effective way to graph out all the customer series? I was looking on your Web site for an example but couldn't find anything that jumped out at me. Could I do this with a Series Formula? "Jon Peltier" wrote: An Excel chart is limited to 255 series. You need to rethink what you want to chart. Does each customer need its own series? Are you interested in widgets per day, cumulative widgets, or something else? - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "rparker" wrote in message ... How do I add lots of series? I need to plot data for up to 1000 series in a single chart, with each series representing a single customer. I'm basically trying to graph buying behavior of customers, where I map lots of customer purchases on a chart to see what trends pop out. So my data might look like this: Customer Days Widgets A 1 1000 A 300 3000 B 200 200 B 1000 10000 So customer A bought 1000 widgets on Day 1 and 3000 widgets on Day 300. Customer B bought 200 widgets on Day 200, and so on. The challenge is that I don't know how many customers I will be graphing, and each customer may buy on a different day. "Jon Peltier" wrote: In a line chart, all series use the category values defined for the first series. If you want two series to use different X values, you need to provide all X values, with gaps where either series has no corresponding Y values. The data looks like this, select the entire range, including blanks, and make the chart. X Y1 Y2 1 2 2 3 3 4 4 5 5 3 6 4 7 5 8 6 If the two series overlap, arrange like so, and go to Tools menu Options Chart Tab, and choose Interpolate for how Excel should deal with empty cells. X Y1 Y2 1 2 2 3 3 3 4 4 5 4 6 5 7 5 8 6 - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Steven K. Smith" wrote in message ... Sorry, I thought at first it didn't work correctly, but then I realized I was testing this on a "line" chart rather than an XY chart. Your suggestion works well for XY, but if you try it on a line chart, it simply puts the selected data at the beginning of the chart, regardless of where the data you'd selected occured in the original chart. Thanks for the help. -- I speak for truth, enlightenment and justice, but not for the US Air Force. "Jon Peltier" wrote: Copy the range of data you want to add, select the chart, and use Paste Special to add the data as new series. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Steven K. Smith" <Steven K. wrote in message ... My, the sign-on process was annoying! I had to click "allow this" 12 times after I started counting, just so I could ask this question. In Excel 2007, how do I add multiple series of data to a scatter chart? It appears to only allow you to add one series at a time. I have a worksheet with data in columns. If I add some columns of data to the and want them to appear on the chart, the only way I've found to do this is to right click, choose "select data", "add" and then individually enter the X and Y ranges for each series, one at a time. It has no problem creating a chart with multiple series at one time, why is this different? This was much easier in Excel 2003. Sooooo annoying, but until I can dump this job my workplace makes me use Excel. |
#11
![]()
Posted to microsoft.public.excel.charting
|
|||
|
|||
![]()
I want to track the widget buying history of each customer. I already have
sales data on how many widgets have been purchased by a distinct customer on a specific date. There are other helpful fields such as geographic region, customer size, etc. I've already been able to massage the data a bit and use pivot tables so I can get statistics such as total widgets per region per quarter, widgets per quarter purchased by new vs. existing customers, etc. I'm trying to prove or disprove a hypothesis about the data; namely, are most widget purchases made simultaneously with the initial sale of the first widget or afterward? I was hoping that by graphing the widget buying history of a number of customers, this would become more apparent. This is why I planned to make each customer into its own series so I could graph out the customer's buying history over time. I plan on segmenting the customers by geographic region and customer size. If I still have more than 255 customers per segment then I was simply going to make each segment smaller by tweaking the customer size categories. I'm doing this analysis mostly for myself so I can draw some conclusions about our customers and their buying patterns, so you are correct in saying that legibility isn't the foremost concern. Hopefully my questions make more sense! I'm still fairly new to Excel charting but I'm doing my best to learn as much as I can to get the job done. "Jon Peltier" wrote: It depends on what you want to show. As I asked... Does each customer need its own series? Are you interested in widgets per day, cumulative widgets, or something else? Another thing you need is legibility, but your posts don't indicate that you're too concerned with that. What you need to think about includes: * do I understand my client? - do I understand the data? - do I understand what they need to learn from the chart? - do I know how they currently display the data? * what are my plotted values? * how do I segment the data? - what are the categories? dates? parts? customer? something else? - how do I define my series? customer? part number? classifications of parts? - Jon |
#12
![]()
Posted to microsoft.public.excel.charting
|
|||
|
|||
![]()
How are you going to differentiate from among 255 customers on a chart?
What type of chart is it? I wonder if using a set of sparklines might be what you need. These people have a good product: http://www.bonavistasystems.com/Prod..._Overview.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "rparker" wrote in message ... I want to track the widget buying history of each customer. I already have sales data on how many widgets have been purchased by a distinct customer on a specific date. There are other helpful fields such as geographic region, customer size, etc. I've already been able to massage the data a bit and use pivot tables so I can get statistics such as total widgets per region per quarter, widgets per quarter purchased by new vs. existing customers, etc. I'm trying to prove or disprove a hypothesis about the data; namely, are most widget purchases made simultaneously with the initial sale of the first widget or afterward? I was hoping that by graphing the widget buying history of a number of customers, this would become more apparent. This is why I planned to make each customer into its own series so I could graph out the customer's buying history over time. I plan on segmenting the customers by geographic region and customer size. If I still have more than 255 customers per segment then I was simply going to make each segment smaller by tweaking the customer size categories. I'm doing this analysis mostly for myself so I can draw some conclusions about our customers and their buying patterns, so you are correct in saying that legibility isn't the foremost concern. Hopefully my questions make more sense! I'm still fairly new to Excel charting but I'm doing my best to learn as much as I can to get the job done. "Jon Peltier" wrote: It depends on what you want to show. As I asked... Does each customer need its own series? Are you interested in widgets per day, cumulative widgets, or something else? Another thing you need is legibility, but your posts don't indicate that you're too concerned with that. What you need to think about includes: * do I understand my client? - do I understand the data? - do I understand what they need to learn from the chart? - do I know how they currently display the data? * what are my plotted values? * how do I segment the data? - what are the categories? dates? parts? customer? something else? - how do I define my series? customer? part number? classifications of parts? - Jon |
#13
![]()
Posted to microsoft.public.excel.charting
|
|||
|
|||
![]()
I was thinking about using an xy scatter plot because I wanted to plot time
on the x axis and number of widgets purchased on the y axis. So the x axis is definitely treated as a value rather than a category. I actually don't care too much about differentiating among the 255 customers; I'm mainly looking for general trends in buying behavior. I was thinking that if I graph a large number of customers and juxtapose their buying behavior together, certain patterns will emerge. Sparklines is an interesting concept. The biggest issue I see with them is that they don't allow me to map many simultaneously together, which is what I'm primarily interested in. The aggregate is more important than the individual customer. "Jon Peltier" wrote: How are you going to differentiate from among 255 customers on a chart? What type of chart is it? I wonder if using a set of sparklines might be what you need. These people have a good product: http://www.bonavistasystems.com/Prod..._Overview.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "rparker" wrote in message ... I want to track the widget buying history of each customer. I already have sales data on how many widgets have been purchased by a distinct customer on a specific date. There are other helpful fields such as geographic region, customer size, etc. I've already been able to massage the data a bit and use pivot tables so I can get statistics such as total widgets per region per quarter, widgets per quarter purchased by new vs. existing customers, etc. I'm trying to prove or disprove a hypothesis about the data; namely, are most widget purchases made simultaneously with the initial sale of the first widget or afterward? I was hoping that by graphing the widget buying history of a number of customers, this would become more apparent. This is why I planned to make each customer into its own series so I could graph out the customer's buying history over time. I plan on segmenting the customers by geographic region and customer size. If I still have more than 255 customers per segment then I was simply going to make each segment smaller by tweaking the customer size categories. I'm doing this analysis mostly for myself so I can draw some conclusions about our customers and their buying patterns, so you are correct in saying that legibility isn't the foremost concern. Hopefully my questions make more sense! I'm still fairly new to Excel charting but I'm doing my best to learn as much as I can to get the job done. "Jon Peltier" wrote: It depends on what you want to show. As I asked... Does each customer need its own series? Are you interested in widgets per day, cumulative widgets, or something else? Another thing you need is legibility, but your posts don't indicate that you're too concerned with that. What you need to think about includes: * do I understand my client? - do I understand the data? - do I understand what they need to learn from the chart? - do I know how they currently display the data? * what are my plotted values? * how do I segment the data? - what are the categories? dates? parts? customer? something else? - how do I define my series? customer? part number? classifications of parts? - Jon |
#14
![]()
Posted to microsoft.public.excel.charting
|
|||
|
|||
![]()
One of your buying patterns was when customers bought widgets relative to
their first widget purchase. If you just plot by date, you'll have a big mess. But try subtracting initial purchase data from purchase date, so you have number of days since first purchase. This will give you a better X variable. For your Y variable, you may leave quantity as is, or you may divide quantity by initial purchase quantity, or you may transform it in another way. And don't treat this as either-or. Make several charts, one for each variation you can think of. And keep trying other variations as they pop into your head. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "rparker" wrote in message ... I was thinking about using an xy scatter plot because I wanted to plot time on the x axis and number of widgets purchased on the y axis. So the x axis is definitely treated as a value rather than a category. I actually don't care too much about differentiating among the 255 customers; I'm mainly looking for general trends in buying behavior. I was thinking that if I graph a large number of customers and juxtapose their buying behavior together, certain patterns will emerge. Sparklines is an interesting concept. The biggest issue I see with them is that they don't allow me to map many simultaneously together, which is what I'm primarily interested in. The aggregate is more important than the individual customer. "Jon Peltier" wrote: How are you going to differentiate from among 255 customers on a chart? What type of chart is it? I wonder if using a set of sparklines might be what you need. These people have a good product: http://www.bonavistasystems.com/Prod..._Overview.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "rparker" wrote in message ... I want to track the widget buying history of each customer. I already have sales data on how many widgets have been purchased by a distinct customer on a specific date. There are other helpful fields such as geographic region, customer size, etc. I've already been able to massage the data a bit and use pivot tables so I can get statistics such as total widgets per region per quarter, widgets per quarter purchased by new vs. existing customers, etc. I'm trying to prove or disprove a hypothesis about the data; namely, are most widget purchases made simultaneously with the initial sale of the first widget or afterward? I was hoping that by graphing the widget buying history of a number of customers, this would become more apparent. This is why I planned to make each customer into its own series so I could graph out the customer's buying history over time. I plan on segmenting the customers by geographic region and customer size. If I still have more than 255 customers per segment then I was simply going to make each segment smaller by tweaking the customer size categories. I'm doing this analysis mostly for myself so I can draw some conclusions about our customers and their buying patterns, so you are correct in saying that legibility isn't the foremost concern. Hopefully my questions make more sense! I'm still fairly new to Excel charting but I'm doing my best to learn as much as I can to get the job done. "Jon Peltier" wrote: It depends on what you want to show. As I asked... Does each customer need its own series? Are you interested in widgets per day, cumulative widgets, or something else? Another thing you need is legibility, but your posts don't indicate that you're too concerned with that. What you need to think about includes: * do I understand my client? - do I understand the data? - do I understand what they need to learn from the chart? - do I know how they currently display the data? * what are my plotted values? * how do I segment the data? - what are the categories? dates? parts? customer? something else? - how do I define my series? customer? part number? classifications of parts? - Jon |
#15
![]()
Posted to microsoft.public.excel.charting
|
|||
|
|||
![]()
Thanks - I should have clarified that the x axis wouldn't show date but would
instead show days since the first purchase. I plan on calculating this as a separate columnn and then using it to graph with. As you say, plotting by date will result in a mess. Is it possible to automate the process of generating all of this data automatically? I don't think Excel will automatically create the charts I'm looking for, so I would have to manually add each new customer series one by one and then tweak the series formula. This is not viable from a sheer time perspective. The only way that comes to mind is write some sort of script to go through the data row by row. As soon as the script detects a change in the customer field, it will create a new series out of the rows it has previously processed. If this doesn't work then the only other option is to use a more flexible graphing tool such as gnuplot or something in combination with scripts to process and prepare the data for graphing. I would have much more flexibility in processing and graphing the data but I was hoping that Excel would be powerful enough to do this. It can certainly do a lot - sometimes I think it can read my mind. :) "Jon Peltier" wrote: One of your buying patterns was when customers bought widgets relative to their first widget purchase. If you just plot by date, you'll have a big mess. But try subtracting initial purchase data from purchase date, so you have number of days since first purchase. This will give you a better X variable. For your Y variable, you may leave quantity as is, or you may divide quantity by initial purchase quantity, or you may transform it in another way. And don't treat this as either-or. Make several charts, one for each variation you can think of. And keep trying other variations as they pop into your head. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "rparker" wrote in message ... I was thinking about using an xy scatter plot because I wanted to plot time on the x axis and number of widgets purchased on the y axis. So the x axis is definitely treated as a value rather than a category. I actually don't care too much about differentiating among the 255 customers; I'm mainly looking for general trends in buying behavior. I was thinking that if I graph a large number of customers and juxtapose their buying behavior together, certain patterns will emerge. Sparklines is an interesting concept. The biggest issue I see with them is that they don't allow me to map many simultaneously together, which is what I'm primarily interested in. The aggregate is more important than the individual customer. "Jon Peltier" wrote: How are you going to differentiate from among 255 customers on a chart? What type of chart is it? I wonder if using a set of sparklines might be what you need. These people have a good product: http://www.bonavistasystems.com/Prod..._Overview.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "rparker" wrote in message ... I want to track the widget buying history of each customer. I already have sales data on how many widgets have been purchased by a distinct customer on a specific date. There are other helpful fields such as geographic region, customer size, etc. I've already been able to massage the data a bit and use pivot tables so I can get statistics such as total widgets per region per quarter, widgets per quarter purchased by new vs. existing customers, etc. I'm trying to prove or disprove a hypothesis about the data; namely, are most widget purchases made simultaneously with the initial sale of the first widget or afterward? I was hoping that by graphing the widget buying history of a number of customers, this would become more apparent. This is why I planned to make each customer into its own series so I could graph out the customer's buying history over time. I plan on segmenting the customers by geographic region and customer size. If I still have more than 255 customers per segment then I was simply going to make each segment smaller by tweaking the customer size categories. I'm doing this analysis mostly for myself so I can draw some conclusions about our customers and their buying patterns, so you are correct in saying that legibility isn't the foremost concern. Hopefully my questions make more sense! I'm still fairly new to Excel charting but I'm doing my best to learn as much as I can to get the job done. "Jon Peltier" wrote: It depends on what you want to show. As I asked... Does each customer need its own series? Are you interested in widgets per day, cumulative widgets, or something else? Another thing you need is legibility, but your posts don't indicate that you're too concerned with that. What you need to think about includes: * do I understand my client? - do I understand the data? - do I understand what they need to learn from the chart? - do I know how they currently display the data? * what are my plotted values? * how do I segment the data? - what are the categories? dates? parts? customer? something else? - how do I define my series? customer? part number? classifications of parts? - Jon |
#16
![]()
Posted to microsoft.public.excel.charting
|
|||
|
|||
![]()
You may be able to use a pivot table, or a VBA approach like this:
http://peltiertech.com/WordPress/200...-chart-series/ If you're not too concerned about the specific customers, why not just plot a single XY series of Days vs. Order Size? - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "rparker" wrote in message ... Thanks - I should have clarified that the x axis wouldn't show date but would instead show days since the first purchase. I plan on calculating this as a separate columnn and then using it to graph with. As you say, plotting by date will result in a mess. Is it possible to automate the process of generating all of this data automatically? I don't think Excel will automatically create the charts I'm looking for, so I would have to manually add each new customer series one by one and then tweak the series formula. This is not viable from a sheer time perspective. The only way that comes to mind is write some sort of script to go through the data row by row. As soon as the script detects a change in the customer field, it will create a new series out of the rows it has previously processed. If this doesn't work then the only other option is to use a more flexible graphing tool such as gnuplot or something in combination with scripts to process and prepare the data for graphing. I would have much more flexibility in processing and graphing the data but I was hoping that Excel would be powerful enough to do this. It can certainly do a lot - sometimes I think it can read my mind. :) "Jon Peltier" wrote: One of your buying patterns was when customers bought widgets relative to their first widget purchase. If you just plot by date, you'll have a big mess. But try subtracting initial purchase data from purchase date, so you have number of days since first purchase. This will give you a better X variable. For your Y variable, you may leave quantity as is, or you may divide quantity by initial purchase quantity, or you may transform it in another way. And don't treat this as either-or. Make several charts, one for each variation you can think of. And keep trying other variations as they pop into your head. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "rparker" wrote in message ... I was thinking about using an xy scatter plot because I wanted to plot time on the x axis and number of widgets purchased on the y axis. So the x axis is definitely treated as a value rather than a category. I actually don't care too much about differentiating among the 255 customers; I'm mainly looking for general trends in buying behavior. I was thinking that if I graph a large number of customers and juxtapose their buying behavior together, certain patterns will emerge. Sparklines is an interesting concept. The biggest issue I see with them is that they don't allow me to map many simultaneously together, which is what I'm primarily interested in. The aggregate is more important than the individual customer. "Jon Peltier" wrote: How are you going to differentiate from among 255 customers on a chart? What type of chart is it? I wonder if using a set of sparklines might be what you need. These people have a good product: http://www.bonavistasystems.com/Prod..._Overview.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "rparker" wrote in message ... I want to track the widget buying history of each customer. I already have sales data on how many widgets have been purchased by a distinct customer on a specific date. There are other helpful fields such as geographic region, customer size, etc. I've already been able to massage the data a bit and use pivot tables so I can get statistics such as total widgets per region per quarter, widgets per quarter purchased by new vs. existing customers, etc. I'm trying to prove or disprove a hypothesis about the data; namely, are most widget purchases made simultaneously with the initial sale of the first widget or afterward? I was hoping that by graphing the widget buying history of a number of customers, this would become more apparent. This is why I planned to make each customer into its own series so I could graph out the customer's buying history over time. I plan on segmenting the customers by geographic region and customer size. If I still have more than 255 customers per segment then I was simply going to make each segment smaller by tweaking the customer size categories. I'm doing this analysis mostly for myself so I can draw some conclusions about our customers and their buying patterns, so you are correct in saying that legibility isn't the foremost concern. Hopefully my questions make more sense! I'm still fairly new to Excel charting but I'm doing my best to learn as much as I can to get the job done. "Jon Peltier" wrote: It depends on what you want to show. As I asked... Does each customer need its own series? Are you interested in widgets per day, cumulative widgets, or something else? Another thing you need is legibility, but your posts don't indicate that you're too concerned with that. What you need to think about includes: * do I understand my client? - do I understand the data? - do I understand what they need to learn from the chart? - do I know how they currently display the data? * what are my plotted values? * how do I segment the data? - what are the categories? dates? parts? customer? something else? - how do I define my series? customer? part number? classifications of parts? - Jon |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Charts switch from 'Series in Rows' to 'Series in Columns' | Charts and Charting in Excel | |||
XY Scatter Charts, High/Low bars, Multiple Series | Charts and Charting in Excel | |||
How do I plot multiple x-axis series in excel charts | Charts and Charting in Excel | |||
Disappearing scales on bar charts with multiple series | Charts and Charting in Excel | |||
Charts Line Types for Multiple Data Series not Printing Properly | Charts and Charting in Excel |