nested formula

[ramana]

Hi every body,

I'm a new member in this group.
I have a Question regarding nesting of formulae in one cell.
Cell A1 can have any integer value between 1 to 100.
Then Cell B1 should have a formula such that it should return me a
value using random formula.
ex: if cell A1 = 1, then CellB1 shoud return a value between 1 to 5.
using random formula.

if cell A1=2, then cell B1 should return a value between 6 to10 using
random formula and so on..

I have tried this using if&lookup functions but I couldn't get through.

Can any body help me on this issue.

Regards

Ramana

 

[Mangesh Yadav]

In B1 enter the formula:
=INT(RAND()*5+1)+5*(A1-1)

And enter any number in A1

Mangesh

 

[B. R.Ramachandran]

Hi,

You could use one of the following formulas depending on the kind of random
numbers you want to generate:

=(A1-1)*5+RANDBETWEEN(1,5) (will generate only random integers)

=(A1-1)*5+1+RAND()*4 (will generate random values)

Note: You should have the 'Analysis Toolpack' installed in Excel and enabled
under 'Add-Ins' for using the RANDBETWEEN function.

Regards,
B. R. Ramachandran

 

[ramana]

Thanks alot Mangesh & ramachandran,
This solution made me half a way through again I posted one more topic
that which escribes my actual proble under the subject
"nested formula with if" can you read out the topic and help me out.

Regards

Ramana