nested formula
 

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

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

And enter any number in A1

Mangesh



"ramana" wrote in message
ups.com...
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

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" wrote:

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

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