excel 2007 Beta-Charting

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm having issues with the chart options. I'm trying out the chart type that
contained two columns and a line all on the same graph. I have two issues
with this

First it appears that Microsoft has removed this type of chart as an option
all together (used to be under chart wizard -> Custom Types (tab) - > Line -
Column).

Secondly the file that I currently have with this chart type no longer
displays properly. By this I mean that the x axis categories are dates which
are pulled using a formula. Excel however refuses to format these categories
as dates. I can format them as any and everything else but if I try to
format them as dates the text simply disappears and I no longer have x axis
categories.

If anyone can help me with either of these issues I would greatly appreciate
it as my company extensively uses this type of chart to analys production
data.
 
1. I hope this isn't real work you're having trouble with, but it sounds
like you've disregarded all the warnings about using a Beta for important
stuff. The Excel Beta is not really ready for production charting. The
dialogs are immature and inefficient, and some things just aren't quite
working. The problem with formula-generated dates was an old one, fixed by
Beta 2 or the Beta 2 Refresh.

2. The built-in custom types have always been somewhat limiting, You're
better off creating your own custom types. Create the type with all three
series as columns, then select one series, and using whatever mechanism
(chart menu > chart type in Excel 97-2003 or chart type on one of the chart
context tabs on the ribbon in Excel 2007) change the series to a line type.

- Jon
 
I will try and see if I can get it to work as suggested...however I don't
hold much hope as I am using Beta 2 TR....and no this is not real work but it
is an offline copy of a real workbook used for data analysis to try and get a
feel for what effects the new version of office may have on my users.
The problem with formula-generated dates was an old one, fixed by
Beta 2 or the Beta 2 Refresh.
Click to expand...

I am using the B2TR for testing and if this is an old problem its still not
gone yet. I can provide a non confidential version of the workbook or the
formulas themselves if you would like.
 
I had also reported this issue previously, and when the TR came out, I
tested it. I remember MS told me it was fixed, and I recall agreeing.

Do your formulas calculate a date, or do they construct a string of text
that looks like a date? If your formulas calculate a date, Excel 2007 (Beta
TR) recognizes them as dates, and your charts work as expected. If your
formulas construct a text string, then Excel 2007 only knows that they are
category labels, but so did Excel 2003. I know this because I just repeated
my testing.

What do you plan to tell your clients about Excel 2007?

- Jon
 
try this:
create a sheet with the following info:

13 1 2 3
etc -> 18
Month 1/1/2006 2/1/2006 3/1/2006 etc -> 6/1/2007

Data1 $val1 $val2 $val3 etc ->
$val18
Data2 $val1 $val2 $val3 etc ->
$val18
Data Total sum1 sum2 sum3 etc -> sum18

(the month fields are formated as follows 'JAN-06')
(also note that the 13 shown above is a key cell for the formulas later)

now that you have your data stored with an indicator number above each
column on the same sheet (or different) create an "static" area for your
chart info to pull from showing only 12 months at a time (formated exactly as
above minus the key cell and the column indicators) and populate it using the
following formula incremented down by 1 from right to left until you get to
-11 for each row of data.

=sumif([range of column indicators],[keycell value],[range of months])
=sumif([range of column indicators],[keycell value]-1,[range of months])
=sumif([range of column indicators],[keycell value]-2,[range of months])
etc ->
=sumif([range of column indicators],[keycell value]-11,[range of months])

This would fill the 12 cells [each containing one of the sumif statements]
with the excel value of the date in the cells created earlier. repeat the
sumif statements on the next several rows adjusting the [range of months] to
be [range of data1], [range of data2], and [range of data total] and chart
the 12 month window that is populated in your 'static' chart range with data
1 and data 2 being columns, data total being a line, and x axis categories
being the months and y axis category being auto calc dollar values.


as far as what I plan to tell my clients - I'm not sure yet...I only got a
copy of the beta two weeks ago and am still undecided. Excel more than the
other apps make me feel as though MS is trying to dumb the users down. I'm
having some troubles adapting to the new UI as I custimize the copy of excel
heavily (to the point of using VBA to add functions that I use most to my
right mouse click popup menu)...but am trying to keep an open mind as some of
the features I do like.
 
After getting some time to try and recreate the chart it appears that the
issue is one with excel being able to properly handle the existing graph. If
I create a new one (and save it as a template [thanks for the suggestion :-)
]) the new chart displays the category labels correctly.

Unfortunately this means that all of my workbooks containing this type of
graph (and perhaps others) will have to have the graphs recreated for them to
work properly :-(

Cosmic said:
try this:
create a sheet with the following info:

13 1 2 3
etc -> 18
Month 1/1/2006 2/1/2006 3/1/2006 etc -> 6/1/2007

Data1 $val1 $val2 $val3 etc ->
$val18
Data2 $val1 $val2 $val3 etc ->
$val18
Data Total sum1 sum2 sum3 etc -> sum18

(the month fields are formated as follows 'JAN-06')
(also note that the 13 shown above is a key cell for the formulas later)

now that you have your data stored with an indicator number above each
column on the same sheet (or different) create an "static" area for your
chart info to pull from showing only 12 months at a time (formated exactly as
above minus the key cell and the column indicators) and populate it using the
following formula incremented down by 1 from right to left until you get to
-11 for each row of data.

=sumif([range of column indicators],[keycell value],[range of months])
=sumif([range of column indicators],[keycell value]-1,[range of months])
=sumif([range of column indicators],[keycell value]-2,[range of months])
etc ->
=sumif([range of column indicators],[keycell value]-11,[range of months])

This would fill the 12 cells [each containing one of the sumif statements]
with the excel value of the date in the cells created earlier. repeat the
sumif statements on the next several rows adjusting the [range of months] to
be [range of data1], [range of data2], and [range of data total] and chart
the 12 month window that is populated in your 'static' chart range with data
1 and data 2 being columns, data total being a line, and x axis categories
being the months and y axis category being auto calc dollar values.


as far as what I plan to tell my clients - I'm not sure yet...I only got a
copy of the beta two weeks ago and am still undecided. Excel more than the
other apps make me feel as though MS is trying to dumb the users down. I'm
having some troubles adapting to the new UI as I custimize the copy of excel
heavily (to the point of using VBA to add functions that I use most to my
right mouse click popup menu)...but am trying to keep an open mind as some of
the features I do like.


Jon Peltier said:
I had also reported this issue previously, and when the TR came out, I
tested it. I remember MS told me it was fixed, and I recall agreeing.

Do your formulas calculate a date, or do they construct a string of text
that looks like a date? If your formulas calculate a date, Excel 2007 (Beta
TR) recognizes them as dates, and your charts work as expected. If your
formulas construct a text string, then Excel 2007 only knows that they are
category labels, but so did Excel 2003. I know this because I just repeated
my testing.

What do you plan to tell your clients about Excel 2007?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______
Click to expand...
Click to expand...
 
Back
Top