Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help on performing calculations on formula results
I need to apply the "RANDBETWEEN" function to a formula result in a cell and am having difficulty. I can get the following formula to work by using the "Iteration" function set to 1 but get erratic results, including values added to cells where there were no values as a result of the Lookup: "=if(if(isna(vlookup($A$1,sheet2!A$1$:B$1,2,false) ),"0",vlookup($A$1,sheet2!A$1$:$B$1,2,false))30,R ANDBETWEEN(20,30),A1)" What I want to do is generate a random number between 20 and 30 if the result of the lookup is greater than 30. The lookup can result in values from negative 500 through to postive 500. Any help would be appreciated as this is driving me nuts and costing a lot of development time. -- AussieExcelUser ------------------------------------------------------------------------ AussieExcelUser's Profile: http://www.excelforum.com/member.php...o&userid=32326 View this thread: http://www.excelforum.com/showthread...hreadid=520848 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help on performing calculations on formula results
In say, Sheet1,
Try something like this in B1: =IF(A1="","",IF(ISNA(VLOOKUP(A1,Sheet2!A:B,2,0)),0 , IF(VLOOKUP(A1,Sheet2!A:B,2,0)30,RANDBETWEEN(20,30 ),A1))) where A1 will hold an input to lookup If A2, A3, etc holds yet other inputs, the formula in B1 can be copied down "as-is" to return corresponding values -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "AussieExcelUser" <AussieExcelUser.24fg6q_1141949487.7215@excelfor um-nospam.com wrote in message news:AussieExcelUser.24fg6q_1141949487.7215@excelf orum-nospam.com... I need to apply the "RANDBETWEEN" function to a formula result in a cell and am having difficulty. I can get the following formula to work by using the "Iteration" function set to 1 but get erratic results, including values added to cells where there were no values as a result of the Lookup: "=if(if(isna(vlookup($A$1,sheet2!A$1$:B$1,2,false) ),"0",vlookup($A$1,sheet2! A$1$:$B$1,2,false))30,RANDBETWEEN(20,30),A1)" What I want to do is generate a random number between 20 and 30 if the result of the lookup is greater than 30. The lookup can result in values from negative 500 through to postive 500. Any help would be appreciated as this is driving me nuts and costing a lot of development time. -- AussieExcelUser ------------------------------------------------------------------------ AussieExcelUser's Profile: http://www.excelforum.com/member.php...o&userid=32326 View this thread: http://www.excelforum.com/showthread...hreadid=520848 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help on performing calculations on formula results
Thanks for your input Max, much appreciated. The only issue is that I need everything to be done in the one cell. The lookup returns the first value and then I need to apply the random number generator if the result of the lookup is greater than 30. I cannot insert a column adjacent to perfom part of the query and give a result as I am working on a number of large and different sized tables on a sheet, some with merged cells etc. and can only show one value per item. I have tried hidden colums and other ways to overcome this issue, but they played havoc for the end users of the reports when printed etc. -- AussieExcelUser ------------------------------------------------------------------------ AussieExcelUser's Profile: http://www.excelforum.com/member.php...o&userid=32326 View this thread: http://www.excelforum.com/showthread...hreadid=520848 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help on performing calculations on formula results
"AussieExcelUser" wrote:
Thanks for your input Max, much appreciated. The only issue is that I need everything to be done in the one cell. The lookup returns the first value and then I need to apply the random number generator if the result of the lookup is greater than 30. I cannot insert a column adjacent to perfom part of the query and give a result as I am working on a number of large and different sized tables on a sheet, some with merged cells etc. and can only show one value per item. I have tried hidden colums and other ways to overcome this issue, but they played havoc for the end users of the reports when printed etc. Think I'm out of ideas here, sorry. Perhaps others would drop by and offer you their insights (maybe a vba solution is available ?) Hang around awhile .. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help! All results of my formula are the same! | Excel Discussion (Misc queries) | |||
CSE formula results | Excel Discussion (Misc queries) | |||
How do I view formula results intead of formula in excel? | Excel Worksheet Functions | |||
no formula results in cell | Excel Worksheet Functions | |||
Cells displays formula, not results | Excel Worksheet Functions |