On Sep 12, 9:06 pm, joeu2004 wrote:
On Sep 12, 4:57 pm, "David Biddulph" <groups [at] biddulph.org.uk
wrote:
at your A35 stage you may find yourself with the
((s^2 - sum((A1:A34-m)^2/n))*n/2) term negative, in which
case you wouldn't be able to achieve STDEV of 10.

Of course you are right.


I thought about that, too [...].
But I concluded that I can prove that s^2 is always greater than "v"
[....] Since all the terms of the sum are non-negative, sum(...)/n,
the partial variance, must be less than or equal to s^2, the total
variance. Therefore, s^2 - sum(...)/n must be non-negative.

Of course that is wrong. I confused myself between what would be true
__if__ sd were 10 and what __is__ true given the sd of the 34
generated data points. Klunk!

I will write 10,000 times "I will try not to solve math problems while
I am rushing to get ready for a trip". Double klunk!


I had previously thought along partly similar lines, producing 34
random numbers and intending to set the 35th and 36th to achieve
the mean and STDEV required, but had discarded the idea because
the spread of the first 34 might be so large as to prevent the STDEV
being achievable.

Now I wonder if even that is feasible. After all, isn't that
essentially what I tried to do? It is easy to meet the mean criteria,
at least by my method. But how else would you meet the sd criteria,
other than with my method, which does not work?

It seems that the solution must ensure that the variance of the 34
data values (actually 34/36 of the variance) does not exceed the
desired variance of the 36 data values. I 'spose we could iterate in
a macro until that condition is met. But I generally avoid iterative
implementations that are not guaranteed to terminate in a finite (and
reasonable) time. Of course, we could put a limit on the number of
iterations, returning #NUM if we fail. Hmm, that sounds familiar
<wink.

Oh well....