format axis number - bug?

[willy]

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?

 

[Shane Devenshire]

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.

 

[willy]

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.

 

[Jon Peltier]

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

 

[willy]

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(B3>0.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