Fix value from the sum of 4 random values....

[ShyamSharma]

Hello Every One,

I am looking forward for a solution of below cited problem....


1) I want to have total of four random values between 2.5 and 7.
2) System must pick random values by its own and values must be in between 0 and 5.
3) Total must be in multiple of 0.5 e.g 2.5, 3, 4.5, 6.5, 7, 6, 4 etc...

Hope I am clear about my requirement , pl feel free to connect in case any other info is required.

Thanks and Regards,

Shyam Sharma

 

[jondavis1987]

I'm looking at this and I'm slightly confused. in 1 you say between 2.5 and 7, then in 2 you say 0 and 5.

 

[jondavis1987]

=ROUND(RANDBETWEEN(5,14)*0.5,1)

I know the 5 and 14 doesn't appear what you want but the *0.5 will generate numbers only between 2.5 and 7 and they will all be .5 or .0

 

[BlueHornet]

Your requirements are in conflict with each other. Even after we get past the conflict that jondavis1987 pointed out, and settle on only one of those as the governing requirement, you can expect "random values" to add up to particular multiples. If they did that, then they would not be random.

However, it is possible to pick, say, four random values and then use a formula to add another value so that the final sum is what you want. (That may require a Goal Seek or some other non-automatic formula; I haven't worked that out. I just wanted to point out the illogic between the requirements.)

 

[jondavis1987]

i re-read it and I understand it better.

The requirements you need are this. You need four values to generate between 0 and 5. All values need to be #.0 or #.5 because the total sum of the values needs to be a whole number or a number and a half. Finally, those four values need to sum up to a total between 2.5 and 7.

=ROUND(RANDBETWEEN(1.25,3.5)*0.5,1)

This formula generated four times meets ALL of you requirements. The catch is that to make sure it stays in your total requirements it can only generate a number of 1 or 1.5. Anything else and here is what happens. After one the lowest anything could generate to meet your multiplication of 0.5 would be 0.5. If they all generate 0.5 then it would total 2.0. Now, after 1.5 the next number it can generate is 2 to be able to meet the multiplication of 0.5 requirement. If they all generate 2 then the total would be 8. So, this meets all of your requirements, it just won't generate the range of values you were hoping to.