reverse engineering random number generating

hi
I can generate 100 random numbers between 1 and 5 [as =randbetween(1,5) or else] and calculate ∑x^2/∑x [x=random numbers] on a cell. My question is: how can I do it in reverse? In other words, suppose A1:A100 my potential random numbers, and B1 my formula ∑x^2/∑x. How can I generate A1:A100 based on B1=4 [for instance], whether exactly or approximately.
regards
tourman

You mean generate 100 numbers such that the sum of their squares divided by their sum is a given number?

What does 'approximately' mean?

What does 'approximately' mean?

sorry for my bad English

Looking at some algebra.

k=sum(x^2)/sum(x)
variance s^2=sum[(x-m)^2]=sum(x^2)/n-[sum(x)]^2/n^2 (m is arithmetic mean)

we have two equations in 3 unkowns [sum(x), sum(x^2), s^2]. If we could add a third equation (might I suggest the mean) with a third assumed piece of information, then we will have three equations in three unkowns, and we should be able to solve them. Then, I expect, the problem will reduce to the more common "generate a bunch of random numbers that will have a given mean and variance (or standard deviation=sqrt(variance))". http://answers.microsoft.com/en-us/o...4-829f94daea65

Does that help at all?

I think the problem is complicated by the use of integers. There are 4,598,126 combinations (I think) of 100 numbers between 1 and 5, e.g., all 1's, 99 ones and one 2, ... 99 4's and one 5, all 5's. For only 1 of those (all 1's), ∑x^2/∑x = 1. Also, for only one (all 5's) ∑x^2/∑x = 5. In between, there are a heap of possiblities that give (approximately or exactly) other whole numbers.

You could write a formula (or code) that 'servoes' the next random number to push the metric closer to the goal, but that would peak the distribution toward the desired value, rather than keep it nominally as flat as possible.

Nothing good comes to mind.

Hey, Mr S, the OP has vacated, but I figured out an approach.

Given the parameters (whole numbers 1 to 5 and the desired metric (is that some common moment??)), generate random numbers from a truncated normal distribution and round to integer:

Row\Col	A	B	C	D	E	F	G
1		name				rand()
2	bottom truncation	a	0.5			3	F2: =ROUND(NORMINV(pa + RAND() * (pb - pa), u, s), 0)
3	top truncation	b	5.49			4
4	cumu prob(a)	pa	0.0098	C4: =NORMDIST(a, u, s, TRUE)		3
5	cumu prob(b)	pb	0.8397	C5: =NORMDIST(b, u, s, TRUE)		4
6	mean	u	4	C6: Input		5
7	sd	s	1.5	C7: Input		4
8						4
9	∑x^2/∑x	3.9623		B9: =SUMPRODUCT(F2:F1001^2)/SUM(F2:F1001)		5
10	min(x)	1		B10: =MIN($F$2:$F$1001)		5
11	max(x)	5		B11: =MAX($F$2:$F$1001)		2
12						4
13		num	count			5
14		0	0			3
15		1	47			5
16		2	129			3
17		3	267			4
18		4	325			4
19		5	259			3
20		(larger)	0			3
21						4
22						3
23						3
24						3
25						2
26						3
27						4

Works pretty well for a target mean between 1 and 4.

Your English is fine; what I meant was, how close is close enough.