Excel 2007 and dynamic charting

[smaruzzi]

I would like to create a simple bar chart representing only actual monthly
financial data. The idea is to dynamically select a range based on the
availability of consolidated number starting from January and expanding
progressively month after month.

I used to rely on the OFFSET function in Excel 2003, but it generates an
error when i try to manually type the formula in the Select Data wizard which
powers the charting engine.

Any suggestion on how I might solve this?

Thanks, Stefano

 

[Bernard Liengme]

Perhaps you could show us what you typed.

An alternative to using a dynamic range is to make the data an Excel List
(have a look in Help). Data plotted from a List is automatically updated
when the list is added to or otherwise changed.
best wishes

 

[ShaneDevenshire]

Hi,

First, if you are plotting data all columns of which get new data at the
same time, then in 2003 or later the List/Table feature is your best choice.

If you are plotting data inwhich one column gets new data while the other
column is already complete and you want the chart to expand to the range of
the partially filled column then the dynamic formula method is your best bet.
However, the OFFSET function should be defined as a range name and the name
should be used in the SERIES function rather than the OFFSET function.

You should show us the data area, for example, does it look like
Act Bud
Jan 2 3
Feb 1 4
Mar 3 5
Apr 9 7
May 6 9
Jun 8
Jul 4
........

Or like this

Jan 2 3
Feb 1 4
Mar 3 5
Apr 9 7
May 6 9

with no entries for the remaining months?

Cheers,
Shane Devenshire

 

[smaruzzi]

The data series lokks like this:

Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
Adv 3 5 6 7 8

And is progressively updated when we data become available.
While the starting cell is a constant, the end of this series is variable.

And I'm not 100% sure I understand what you mean by SERIES in Excel 2007
because my impression is that all chart inputs should be managed via the
Chart Wizard.

Thanks, Stefano

 

[ShaneDevenshire]

Suppose Jan is in cell B1 and the first data point (3) is in B2. Then choose
Formulas, Define Name and in the Name box enter Avg, in the Refers to box
enter
=OFFSET(Sheet1!$B$2,,,,COUNT(Sheet1!$B$2:$M$2))
Then click OK.
Now create your chart by highlighting all the data.
Next, in the chart select the series (the columns for example), notice the
formula bar contains a SERIES function. Edit this formula to read:
=SERIES(Sheet1!$A$2,Sheet1!$B$1:$M$1,Book1!AVG,1)

If you laid your data out such that you did not enter the month name before
you had data for the month, you could use the Table feature in 2007. This
makes the data area look a little strange, but you can work it back to
looking pretty normal. To this your data would look like:
Months Jan Feb Mar Apr
Adv 3 5 6 7

You would not add the lable for May until there was data for May. If you
want to try this, select the above data and press Ctrl T and click yes. This
defines the range as a table. Highlight the range and use the Insert tab to
create your chart. There is nothing left to do. As you add now months and
their data the chart will update automatically.

 

[Jon Peltier]

Pay close attention to Shane's protocol. It was not possible to enter such a
formula in any Source Data dialog in any version of Excel. You have to
define a name that uses the formula to identify a range, and enter the name
in the Source Data/Select Data dialog.

- Jon