Reverse average?

[Cross]

Hello!
I'm trying to figure out how to create 5 random results that will give specyfied average.

For example if A1=25 is it possible to get random data in cells B1,B2,B3,B4,B5 that will have average 25?

 

[Comfy]

Sure. But I imagine you have not provided all the criteria for your question.

This will do what you have asked.

Excel 2010
	A	B
1	25	97
2		31
3		40
4		82
5		-125
6		25
Sheet1

Cell Formulas
Range	Formula
B1	=ROUND(RAND()*100,0)
B2	=ROUND(RAND()*100,0)
B3	=ROUND(RAND()*100,0)
B4	=ROUND(RAND()*100,0)
B5	=(A1*5)-(SUM(B1:B4))
B6	=AVERAGE(B1:B5)

 

[Stiuart_W]

If you are "forcing" the sum of the 5 numbers to be 125 then they can't really be considered random can they?

Why not try ranbetween(0,50) for B1 to B4 and then B5 = 125-SUM(B1:B4)

But it isn't random

 

[Cross]

Thank You gentlemans for answers. You were right Comfy I forgot to mention that there should be range for random numbers

	A	B
1	20	=(RANDBETWEEN($A$1;$A$2)+(ROUND((RAND());2)))
2	25	=(RANDBETWEEN($A$1;$A$2)+(ROUND((RAND());2)))
3		=(RANDBETWEEN($A$1;$A$2)+(ROUND((RAND());2)))
4	24	=(RANDBETWEEN($A$1;$A$2)+(ROUND((RAND());2)))
5		=(A4*5)-SUM(B1:B4)
6		=AVERAGE(B1:B5)

<tbody>
</tbody>

The problem is with cell B5 beacaue it generates numbers out of range 20÷26.
Do You have any idea how to solve this?

 

[Stiuart_W]

Shouldn't A4 be 25?

If your first 4 numbers are between 20 and 25 (inclusive) and they are random you can't have the fifth number between 20 and 26 to achieve an average of 25 - It's just NOT mathematically possible.

Eg A1 to A4 could easily all be 20 meaning A5 would need to be 45 to have an average of 25.

All I can suggest is that you just recalculate until A5 falls in the range required

 

[Comfy]

It would not be possible with your range.

As 20 is too low below the average for the remaining numbers to make up the average.

If one of the numbers was 20 then another number must be 27 to make up the difference.

This is the closest I could get.

Excel 2010
	A	B
1	20	26
2	26	25
3		25
4	25	25
5		24
6
7		25
Sheet1

Cell Formulas
Range	Formula
B1	=RANDBETWEEN(A1,A2)
B2	=ROUND(($A$4*5-SUM($B$1:B1))/4,0)
B3	=ROUND(($A$4*5-SUM($B$1:B2))/3,0)
B4	=ROUND(($A$4*5-SUM($B$1:B3))/2,0)
B5	=ROUND(($A$4*5-SUM($B$1:B4)),0)
B7	=AVERAGE(B1:B5)

This is based on the first value being random, but due to your range restriction there are only a limited number of possible out comes.

 

[Cross]

All I can suggest is that you just recalculate until A5 falls in the range required

Actually I think it is great idea! I will write loop macro that will check if B5 falls in range and if not it will recalculate

 

[progress]

Hi Guys

I need that VBA code that can recalculate the random numbers until the fifth value fall in range

how this VBA code should be written

Thanks in Advance