Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is it possible to multiply a chart series formula?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is it possible to multiply a chart series formula?
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 ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "Nick H" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is it possible to multiply a chart series formula?
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is it possible to multiply a chart series formula?
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...00906ACNJ.html _______ "Nick H" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is it possible to multiply a chart series formula?
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Change Chart Series Formula | Charts and Charting in Excel | |||
seeing the Chart Series Formula | Charts and Charting in Excel | |||
returning a chart series formula | Excel Programming | |||
Convert Chart Series Formula to Array Via VBA? | Excel Programming | |||
Formula property of a series in a chart | Excel Programming |