Create a Graph with Random Number Generator

[Luke Williams]

Hello,

I'm trying to reproduce a graphic. It was made using the random generator
number. Since, I am not very confortable with statistics I thought someone
could help me with this. The X axis represents days. The Y axis a Flow
volume that ranges from 0.4 to 32. The graphic represents a "possibility"
model of a flow during a year. For example, Day 1 to Day 12, the flow is
23.5 litres. Day 62 to 100, the flow is 0.4 litres. There are only 3
possibilities: 0.4, 23.5 or 32.0. Please help, it's driving me nuts!

Thanks,

L.

 

[James Silverton]

Luke wrote on Mon, 19 Dec 2005 06:19:02 -0800:

LW> I'm trying to reproduce a graphic. It was made using the
LW> random generator number. Since, I am not very confortable
LW> with statistics I thought someone could help me with this.
LW> The X axis represents days. The Y axis a Flow volume that
LW> ranges from 0.4 to 32. The graphic represents a
LW> "possibility" model of a flow during a year. For example,
LW> Day 1 to Day 12, the flow is 23.5 litres. Day 62 to 100,
LW> the flow is 0.4 litres. There are only 3 possibilities:
0.4, 23.5 or
LW> 32.0. Please help, it's driving me nuts!

LW> Thanks,

Since you only want 3 values, I think the easiest way might be
to insert/define a function, say, GAS,
=int(randbetween(1,3)+.1). Then equate your values:-
=if(gas=1,0.4,if(gas=2,23.5,32.0))


James Silverton.

 

[James Silverton]

Luke wrote on Mon, 19 Dec 2005 07:17:02 -0800:

LW> "James Silverton" wrote:

??>> Luke wrote on Mon, 19 Dec 2005 06:19:02 -0800:
??>>
Thanks James but it is not really giving me the results I need.
Is there a
way I could send you a JPEG of the graph so you could understand
better what
I am trying to reproduce?

Luke,
I guess you are right; I don't understand the problem. I regret
that I am going to be out for 10 days or so over the holidays so
I hope someone else can help.

Feel free to post again if you don't get any assistance. You
might be able to e-mail a graphic to me but the safest thing
might be for *me* to email you first if your posted address is a
real one.
James Silverton.

 

[Luke Williams]

Thanks James. My email is (e-mail address removed)

Could you quickly look at it and maybe suggest how to approach the problem?
I was told it was creqted with the random number generator add-in. The
add-in has som many options, I am unsure hot to proceed and have tried just
about everything it offers too...

I wish you happy holidays!

L.

 

[Tushar Mehta]

Suppose your dates are in col. A starting with A2. Then, in B2 enter
the formula =CHOOSE(INT(RAND()*3+1),0.4,23.5,32). Copy B2 as far down
col. B as you have dates in col. A. Plot the data in A:B.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Luke Williams]

Thanks Tushar, I will try that - Now, my problem is translating your formula
into French (work insisted on installing Excel in french so I have to find
what INT is in french?!)



L

 

[James Silverton]

Tushar wrote on Mon, 19 Dec 2005 11:06:07 -0500:

TM> Suppose your dates are in col. A starting with A2. Then,
TM> in B2 enter the formula
TM> =CHOOSE(INT(RAND()*3+1),0.4,23.5,32). Copy B2 as far down
TM> col. B as you have dates in col. A. Plot the data in A:B.

I'm not the OP but thanks Tushar. I have to admit that I did not
know about CHOOSE. It's certainly more elegant than my
"solution" but will it do what the OP wants?

James Silverton.

 

[Luke Williams]

I was able to translate your formula! Thanks! Is there a way also to make
sure that the number does not appear more than twice? I get for example in
cell b2, b3, b4 the same number I would like not more than 2 cells with the
same number.

Thanks again!

 

[David Biddulph]

I was able to translate your formula! Thanks! Is there a way also to make
sure that the number does not appear more than twice? I get for example in
cell b2, b3, b4 the same number I would like not more than 2 cells with the
same number.

If you're saying that instead of the random choice you don't want more than
2 consecutive rows with the same value, you could use a COUNTIF on the 2
rows above your current row, and if you've got 2 rows of the same number
then do your new CHOOSE between just the 2 remaining values (and
equivalently for 2 of each of your 3 options), otherwise choose between the
3 values as above.