Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
format axis number - bug?
Hello, hoping for a bit of help with XY charts in XL2007. In the example I
have here now the data is coming from a Pivot Table (but it's not a Pivot Chart), however I also have the same problem with other XY charts which I think are straight off plain text data. And Pivot Charts. I cannot get the default number format for the x axis to change to my selection. I find and choose the option FORMAT AXIS, NUMBER, CUSTOM, I put in a new custom format (in this case dd mmm) and click ADD, with no change. I have toggled the LINK TO SOURCE and formatted the source data the same, with no luck. Even just choosing from the list of default formats, no change. I have had the same problem trying to manipulate dates, and with trying to change number of decimal pts displayed (the default seemed to be many....) It's like a bug in 2007? |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
format axis number - bug?
Hi,
It worked for me: 1. Format the spreadsheet range to dd mmmm 2. On the chart right click the x-axis and choose Format Axis 3. On the Number tab choose Link to sourse it should display your custom number format in then Format Code box when you turn on the Link to sourse checkbox. -- If this helps, please click the Yes button Cheers, Shane Devenshire "willy" wrote: Hello, hoping for a bit of help with XY charts in XL2007. In the example I have here now the data is coming from a Pivot Table (but it's not a Pivot Chart), however I also have the same problem with other XY charts which I think are straight off plain text data. And Pivot Charts. I cannot get the default number format for the x axis to change to my selection. I find and choose the option FORMAT AXIS, NUMBER, CUSTOM, I put in a new custom format (in this case dd mmm) and click ADD, with no change. I have toggled the LINK TO SOURCE and formatted the source data the same, with no luck. Even just choosing from the list of default formats, no change. I have had the same problem trying to manipulate dates, and with trying to change number of decimal pts displayed (the default seemed to be many....) It's like a bug in 2007? |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
format axis number - bug? - More thoughts
I have worked on this a bit more, in the first dialog for format axis, I note XL gives AXIS OPTIONS, and a bit of explanation from help suggests that it is choosing a DATE AXIS by default. However even within the date options I cannot get rid of the reference to years. Which takes me back to the original problem and maybe I could get some help with the fundamental structure. I want to display plots of seasonal data, with a line/plot for each year, superimposed one on the other, with months (yearless) as the x axis. Specifically it is fruit size data, and I want to show how one season might be more or less developed than any other year. So I start with two columns, date and mm, and then I generate another two columns, one is season (=YEAR(A2)) and the other is season date (=(DATEDIF(DATE(2000,1,1),A2-212,"yd"))+212) adjusted to show the season starting on 1 August or thereabouts. I then use a PIVOT TABLE to pull this together with the first column being season date, then 2004 2005 2006 etc, note there are many blanks in the PVT which works fine. Then a chart from that. I need an XY scatter plot. Maybe I have got the tiger by the tale? And furthermore, if I have started to test the limits of MS XL Charting, can anyone suggest where to go next? Many thanks in advance. |
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
format axis number - bug? - More thoughts
Check that all of the apparent numbers are treated as such by Excel. When
you do grouping by time periods in a pivot table, for example, the resulting labels are text, not numeric, even if they look like years. There are any number of other ways numbers may be encoded as text. Sometimes, but not always, text can be used in formulas and seem fine. For example, if I have text dates, the formula =YEAR(textdate) gives me the year. Text is displayed as it is entered without regard to number formats. Also, a date axis uses dates, that is day-month-year values, not just day-month or days since last August 1. Changing the number format doesn't hide the fact that a date is comprised of day, month, and year. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "willy" wrote in message ... I have worked on this a bit more, in the first dialog for format axis, I note XL gives AXIS OPTIONS, and a bit of explanation from help suggests that it is choosing a DATE AXIS by default. However even within the date options I cannot get rid of the reference to years. Which takes me back to the original problem and maybe I could get some help with the fundamental structure. I want to display plots of seasonal data, with a line/plot for each year, superimposed one on the other, with months (yearless) as the x axis. Specifically it is fruit size data, and I want to show how one season might be more or less developed than any other year. So I start with two columns, date and mm, and then I generate another two columns, one is season (=YEAR(A2)) and the other is season date (=(DATEDIF(DATE(2000,1,1),A2-212,"yd"))+212) adjusted to show the season starting on 1 August or thereabouts. I then use a PIVOT TABLE to pull this together with the first column being season date, then 2004 2005 2006 etc, note there are many blanks in the PVT which works fine. Then a chart from that. I need an XY scatter plot. Maybe I have got the tiger by the tale? And furthermore, if I have started to test the limits of MS XL Charting, can anyone suggest where to go next? Many thanks in advance. |
#5
Posted to microsoft.public.excel.charting,microsoft.public.excel.misc
|
|||
|
|||
format axis number - bug? - More thoughts - A RESULT!
Dear John
Many thanks for your advice, you nailed it. Although I did have to work hard to impress upon XL my interest in getting a result. I had to use the =Value() function prior to the PVT (in conjunction with the =Datedif(date()) fiddle) and then set up a dummy table after the PVT with =IF(B30.01,VALUE(B3),NA()) before the chart would play ball. And then it did as a regular xy scatterplot with all the docile features. Note that I have spent about 6 hrs in the last few days investigating R and Genstat (which both are appealing), but working with something familiar got me up and running in 3/4 hour. Big thumbs up to this community group. Kind regards Bill Williamson Australia "Jon Peltier" wrote: Check that all of the apparent numbers are treated as such by Excel. When you do grouping by time periods in a pivot table, for example, the resulting labels are text, not numeric, even if they look like years. There are any number of other ways numbers may be encoded as text. Sometimes, but not always, text can be used in formulas and seem fine. For example, if I have text dates, the formula =YEAR(textdate) gives me the year. Text is displayed as it is entered without regard to number formats. Also, a date axis uses dates, that is day-month-year values, not just day-month or days since last August 1. Changing the number format doesn't hide the fact that a date is comprised of day, month, and year. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Format the x axis of a pivot chart, no number tab??? | Charts and Charting in Excel | |||
Format Y-Axis | Charts and Charting in Excel | |||
Replace million-billion number format to lakhs-crores format | Excel Discussion (Misc queries) | |||
Format Axis; Scale Tab; "Value Axis Crosses At" ends with preposit | Charts and Charting in Excel | |||
Number of labels on X-axis one more than number of values on Y-axi | Charts and Charting in Excel |