Excel Charts

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

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?
 
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
 
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/
_______
 
Back
Top