Asked by: fdic-ga
List Price: $2.00
27 Feb 2005 08:51 PST
Expires: 29 Mar 2005 08:51 PST
Question ID: 481782
Okay all you Excel gurus, I wish to build a scatter XY scatter chart which details graphically a factor (say profit margin) per location. The problem is that the location numbers are all over the board i.e. 194, 794, 802 etc. so the wizard orders the data by this order. What I want it to do is just put the data in the order I specify in a separate column so the factor is displayed in a logical manner. However I do want each data point to be labeled with the location number and the margin factor. The goal is to see all of our locations on a chart and there overall profit margins so we can compare them to each other and spot trends. I have tried many configurations and the problem is they always end up ordering the data by the location number which is hard to read. The data points need to be evenly displayed and labeled. If anyone is interested I will email a screenshot of what I am able get now and this may help you understand better.
Re: Excel Chart
Answered By: maniac-ga on 27 Feb 2005 09:26 PST
Hello Fdic, A screen shot is not necessary. What you describe is pretty stright forward but not very easy to do automatically in Excel unless you do a "trick". For an example, I created a worksheet like this: X Location Profit Margin 1 194 2% 2 820 3% 3 702 6% 4 210 3% When I did Insert -> Chart... and selected a scatter chart, the default action was to create two series - one labeled "Location" and the second labeled "Profit Margin". I assume the second series is what you want with the first series as the labels. The "trick" is to change the location values to text. What I then did was to enter a single quote "'" at the beginning of each location number. So the first one will be displayed as '194 in the formula bar and the value 194 will be displayed left justified in the spreadsheet (unless you change the cell formatting). Change all the values and then select the two columns (Location and Profit Margin) and now do Insert -> Chart... the default scatter chart is in the order of values in the spreadsheet cells and when you select "Show Value" as part of the formatting, the value from the Location cell will be displayed next to each plotted Profit Margin point. Of course - maybe you need the locations to be a number (and not text). In that case, I suggest a separate column for the location (as a number). Just be sure to plot using the text value (and not the number). If this does not work for you - please make a clarification request indicating other factors that must be addressed. I should be able to come up with a suitable solution. --Maniac
rated this answer:
That worked perfectly! Thanks so much.
|There are no comments at this time.|
If you feel that you have found inappropriate content, please let us know by emailing us at email@example.com with the question ID listed above. Thank you.
|Search Google Answers for|