Posted to microsoft.public.excel.charting
|
|
Formatting 'X' Axis -- 2 fundamental differences
Hokay, I don't volunteer so much anymore, but why don't you send me the
worksheet? Something funny's going on, and you've been around here long
enough that I know it's not one of the obvious things that trips up the
inexperienced. Remove the capital letters from the email in my profile.
- Jon
-------
Jon Peltier, Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
Advanced Excel Conference - Training in Charting and Programming
http://peltiertech.com/Training/2009...00906ACNJ.html
_______
"Francis L. Kulchar" wrote in message
...
Jon:
I cannon overstate how invaluable your help to me has been over the
previous 3 years or so; I absorb, and I follow everything that you say; it
is just, in this case, ALL OF MY 7 CHARTS COME FROM THE SAME PAGE
SPREADSHEET...AND ALL OF THEM HAVE THE "B" COLUMN CHOSEN AS THE X AXIS,
BECAUSE CELLS B1 THROUGH B1400 ALL HAVE THE SAME FORMAT...(CUSTOM
-- DATE ... == M-DDD").....AND THE CELLS ARE GENERATED VIA:
B2=B1+1
B3=B2 +1
B4=B3 +1
. ..
...
...
down to
B1400=B1399
The rest of my charts include 7 day averages
14 day averages
30 day averages
And, since the X axix is identical
in ALL the charts, I fail to see why all my X Axis formatting "Scale"
options are NOT the same in the 7 chaets...remember, I fixed the
problem,via changing all the X values from B972:B1109...if I try to go
back into time, such as B400:B1109 for the X axis, my formatting option
for formatting the 'SCALE' under X Axis reverts to my example 1 -- a
formatting option I do not aspire to.
Thanks, FLKulchar
eltiertech.com wrote in message
...
No need to shout.
All it takes is one cell with a text value, not a numeric value, to screw
up an axis. All the numeric cells can be formatted the same, and the text
cell have text that looks like the rest of the dates or numbers, and
you'll never know. The trick is to overcome your natural inclination to
assign a horizontal alignment (most people insist on centering data in
cells), and leave it as default. This way it becomes obvious: text is
left-aligned and numbers (including dates) are right aligned. Error
values are centered.
Another point is to check how the axis is defined. In 2003 & earlier, go
to Chart menu Chart Options Axes. Under Category (X) Axis, if Auto is
selected, Excel decided how to set up the axis. You can overrule Excel by
selecting Category or Time-Scale. One text cell will cause Excel to
automatically select Category.
In 2007, these settings are found on the Format Axis dialog, main tab,
and the two axis types are Text and Date, but the inner workings are the
same.
- Jon
-------
Jon Peltier, Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
Advanced Excel Conference - Training in Charting and Programming
http://peltiertech.com/Training/2009...00906ACNJ.html
_______
"Francis L. Kulchar" wrote in message
...
Thank you!
Fiorst off, I tried what you said...and I secected my column of "pseudo
dates" which is B600 -- B1400:
B600 says 1 - Nov
B601 says =B600+1
B602 says =B601 +1
etc, etc, etc
BUT THAT EXACT SAME COLUMN IS THE EXACT SAME FOR ALL 9 OF MY CHARTS
WRITTEN
ON THAT PAGE!!
ALL THE CELLS ARE FORMATTED:
CUSTOM "D -- MMM"
YET I STILL GET THE 2 DISTINCTLY DIFFERENT "FORMAT X AXIS" EXAMPLES THAT
I
EARLIER REFERRED TO!
WHY CHART 1 IS DIFFERENT FROM CHART 2 ...I DO NOT UNDERSTAND!
I tried your text to columns bit...but I got somewhat "lost" in your
last
step...'select Date in the top right'.
WOW!!!!! I JUST solved my problem:
All the graphs that follow my example 2, have as source datta for the
date:
B600:B1109
All the graphs that have my example 1, have as source data:
B972:B1109
Furthermore, all the charts show the date such as:
5/02/09 (For my example 1 -- what I'm after
All others, i.e. the charts from B600:B1109 show the X axis's date as:
12-Nov
SO FINALLY, I have fixed all my charsts...by "limiting" my opening date
to
12/31/08
and source data B972:B1109
Now , my charts start earlier, and my charsts whos "X Axis" was
B18:B1109
always came out "wrong" (my snd example.
I CANNOT UNDERSTAND WHY...ALL OB MY CELLS...B18:B1109 ARE FORMATTED
EXACTLY
THE SAME!!!
So, again, thank you for the help...BUT WHY WAS THERE A
DIFFERENCE?????????????????
Please RSVP.
Thamk you,
FLKulchar
Jon Peltier
"Jon Peltier" wrote in message
...
In the second case, Excel has made a category axis, treating your dates
as nonnumeric labels. Perhaps the dates were imported from some other
source and were pasted into Excel as text.
- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______
"F. Lawrence Kulchar"
wrote in message
...
I have many Line-type charts, all of which have as the X axis, the
DATE.
When I right click on the "X" axis, I get 2 distinctly different type
options under "SCALE" as follows:
1) FORMAT AXIS
Patterns Scale Font Number Alignment
minimum
maximum
base unit
major unit
minor unit
value (Y) axis
crosses at
Date in Reverse Order Value (Y) axis crosses between dates
Value (Y) axis crosses at maximum value
OR...
I get:
2) Patterns Scale Font
Number
Alignment
Value (Y) axis crosses
at category number
Number of categories
between tick-mark labels
Number of categories
between tick marks
IN ANY EVENT, I FAIL TO UNDERSTAND WHY, UNDER "SCALE", WHEN I FORMAT
THE
"X" AXIS,
I GET THESE 2 OPTIONS DESCRIBED ABOVE,
BECAUSE..
"I BELIEVE THE CHARTS ARE IDENTICAL!"
Can someone please help me???
Thank you,
FLKulchar
|