Is it possible to multiply a chart series formula?

[Nick H]

My client would like to be able to adjust all the values in a chart,
that is based on a large set of data, by some percentage depending on
various scenarios.

Is it possible to do this directly in the series formula? E.g.
something like...

=SERIES(Sheet1!$D$2,Sheet1!$E$1:$HG$1,Sheet1!$E$2:$HG$2*MULTIPLIER,1)

....where MULTIPLIER is a reference to a cell containing the percentage
variance.

Preempting the alternative suggestions of recalculating via VBA or
placing a formula in each datasource cell that calculates the product
of the multiplier and its original value - can anyone suggest which
method might be quicker on a range of 40,000 cells?

Br, Nick H

 

[Jon Peltier]

You can't put any functions in a SERIES formula, just links or static
values.

The best way to handle this is with a helper row/column. For example, your Y
values are in E2:HG2. Put your multiplier into cell C3, select E3:HG3 with
E3 as the active cell, and enter this formula:

=$C3*E$2

then hold Ctrl and press Enter. Use row 3 instead of row 2 in the chart.
Changing the value in C3 will change the values in the chart automatically.
It's easy to understand, easy to see where an error may have crept in, and
in Excel 2003 and earlier it's very fast. Excel 2007 has some performance
issues with charts that have more than a trivial number of points, but the
SP2 (just out) is supposed to improve this.

- Jon

 

[Nick H]

Thanks Jon,

I had suspected this was the case but didn't want to give up hope
until an MVP confirmed it. ?;^)

I have about 200 series in rows so would need about 200 helper rows. I
may well do as you suggest with a helper 'range' but need to weigh up
the difference in speed between using VBA
(xlPasteSpecialOperationMultiply) and formulas.

Br, Nick H

 

[Jon Peltier]

Nick -

It depends on whether you care to retain the original data and on how easily
you want the changes to be made. I'd use the dual ranges. Worksheet space is
cheap, human labor to recreate something that you've overwritten is not.

- Jon
-------
Jon Peltier, Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/

Advanced Excel Conference - June 17-18 2009 - Charting and Programming
http://peltiertech.com/Training/2009-06-ACNJ/AdvExcelConf200906ACNJ.html
_______

 

[Nick H]

Jon -

Fair point, in this instance the embedded chart and its source data
are created via VBA from a master data sheet in the same workbook.

The idea of being able to change the 'live' chart by altering a
multiplier on-sheet was a 'nice to have'. I've decided to make do with
a textbox on the form that gets presented when the user chooses to
create charts. They can choose to accept or modify the default
multiplier here. If the user later changes his mind, it only takes a
few seconds to recreate the chart(s). This also allows them to retain
a copy of the original so they can flip between the two for
comparison.

Br Nick H