Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
UPDATED - Referencing named Ranges within a Nested IF formula | Excel Worksheet Functions | |||
Formula works in some cells, doesn't in other | New Users to Excel | |||
Howdo U copy a formula down a column, that uses data in another w. | Excel Worksheet Functions | |||
Howdo U copy a formula down a column, that uses data in another w. | Excel Worksheet Functions | |||
Problem with VBA returning the contents of a long formula. | Excel Discussion (Misc queries) |