Excel Charts

[Guest]

Hi There!

I am figuring out whether it is possible to change the variance of numbers
by adjusting a graph in excel. For example: I have a graph that I made from a
table of numbers. All the numbers total up to hundred. Is it possible that by
changing the chart the variance of my numbers will also change?

 

[Jerry W. Lewis]

If the plotted values are numbers rather than formulas, then you can
drag a plotted point an change the corresponding value. Note that this
is one value at a time, will not preserve data relationships (i.e. the
numbers will cease to sum to one hundred, and the result will only
indirectly relate to the resulting variance.

A linear transformation of your data may be more satisfactory. If
SUM(data) = 100
VAR(data) = v
then
SUM(data*b+a) = 100*b+a
VAR(data*b+a) = v*b^2

By suitable choice of a and b, you can make sum and variance any desired
values.

Jerry

 

[Jon Peltier]

Tracy -

Try using a helper column. I set up A1:C8 to look like this:

Plot
A 5.050505051 5
B 12.12121212 12
C 20.2020202 20
D 25.25252525 25
E 20.2020202 20
F 12.12121212 12
G 5.050505051 5

Column C are the numbers I play with to make the shape I want, and
column B has the scaled numbers that add to 100. To get the formulas to
work, select B2:B8 with B2 as the active cell, enter this into the
formula bar:

=C2*100/SUM($C$2:$C$8)

and hold CTRL while pressing Enter. Select A1:B8 and make your chart.
Now if I want to skew the bump toward the earlier part of the curve, I
fiddle with column C, and column B adjusts accordingly:

Plot
A 8.196721311 10
B 18.03278689 22
C 22.95081967 28
D 20.49180328 25
E 16.39344262 20
F 9.836065574 12
G 4.098360656 5

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______