Generate random numberes using reference to the other cell.

[ramana]

Hi Everybody,

I have problem in generating random numbers depending on the other
cell value. My Problem is like this.
In column A I have 10 numbers Now I wnat to generate 20 random numbers,
This can be done By giving reference to one cell for two times.

Ex:

A1=1,A2=1,A3=2,A4=5,A5=2,A6=1,A7=2,A8=2,A9=1,A10=2
Now In B1=If(A1<=1,randbetween(10,20),randbetween(20,30))
In B2=if(A1>=,randbetween(10,20),randbetween(20,30))
I used For B1&B2 the cell A1 as reference as because I have to generate
20 random numbers from 10 refernce cells in Column A. and this can be
done.(20/10=2)

But When we have only Say 7 reference cells in column A and the random
numbers to be generated are 20 in Column B what kind of relation ship
can I apply In column B.(20/7=2.85). Is there any method that the
references can be given for the column B in Column A to generate the
required random numbers.

Thanks and Regards

Ramana

 

[Guest]

Hello,

We need more info, I'm afraid.

So far I think you might fancy a function similar to my UDF
http://www.sulprobil.com/html/rww.html
(but: you obviously need integer output).

What precisely would you like to simulate, please?

Regards,
Bernd

 

[ram117]

Hi

I have to simulate one parametr depending on the other parameter. One
parameter can be tested at regular interval, but the other parameters
occurence is irregular.
Time Parameter1 Parameter2 Time
8.00 2.34 X1` 8.00
9.00 2.19 x2 8.40
10.00 1.89 x3 9.10
X4 9.40
X5 10.00
You can observe the timings for parameter 1 and 2 which are different.
Now For X1 corresponding parameter is 2.34 for X2 its in between
2.34&2.19 for X3 2.19,
For X4 inbetween 2.19&1.89 and for X5 it is 1.89.

But I need to simulate parameter2 corresponding to parameter1 &
depending on condotion That if Parameter1 is >=2.2 then parameter2 is
Randbetween(12,13) otherwise randbetween(11,12).
Now I think that you may get a clear picture of my problem and if any
suggestions Please send the reply.

Thanks and Regards

Ramana

 

[Guest]

Hello Ramana,

I hope I understand you correctly. I think you need an interpolation function.

Take http://www.xlxtrfun.com/XlXtrFun/XlXtrFun.htm for example, and install
it as it is described.

If your time values for parameter 1 are in cells A2:A4 and parameter 1
values are in B2:B4 and Time values for parameter 2 are in cells D26, then
enter
=Interp($A$2:$A$4,$B$2:$B$4,D2)
into cell C2 and copy this down to D6.

Please notice that the function Interp() is part of the package mentioned
above. It won't work without that installation.

My results are:
X1 = 2.34
X2 = 2.256667
X3 = 2.150417
X4 = 2.006667
X5 = 1.89

HTH,
Bernd

 

[ramana]

Hi Bernd,

I tried your formula but it is giving #NUM error I tried
formatting cells in many ways but I couldn't help me any more
sugeestions please.

Thanks and Regards

Ramana

 

[bplumhoff]

Hi Ramana,

Did you install the addin http://www.xlxtrfun.com/XlXtrFun/XlXtrFun.htm
?

Send me your file or the substantial part of it if you like:
(e-mail address removed)

Regards,
Bernd

 

[ramana]

Hi,

I installed the file, I found that the problem is due to the range
I selected. The problem is that I have conditional formula in my cells
if the condition is satisfied it will return a value if not it will
display "FALSE" When ever I have False in my range then it is showing
#NUM! error. I will Send my file to you. Thank you very much for
showing interest in my problem.

Thanks and Regards

Ramana

 

[ramana]

Hi,

Thanks alot Bernd, The version you have given is still giving Error,
But after trying many options I could succeed in solving the problem
using match function to get the last row number which is having a value
and using indirect function in the interp function. Once again I thank
you, with out your support I couldn't complete the problem.

Thanks and Best Regards

Ramana