Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Utilizing a RANDBETWEEN() formula within a LOOKUP formula
I'm trying to make a sheet that'll determine various qualities depending on a
given number, one of those qualities is a randomly determined number within a variety of ranges. For example: If the number is within the range of 1-6 "Office" will be output. From this "Office" output I need the sheet to generate a number in the range of 2-16. The problem I'm having is that I have 16 initial ranges from 1-96 which result in a different type of building being selected. Based on which building is selected, I need the formula to spit out a number from a range that is determined by the type of building. The formula I attempted to use was rather long and still didn't work: LOOKUP(B3,{0,1,6,12,18,24....},{=RANDBETWEEN(1,3), =RANDBETWEEN(2,16),... and so forth. That didn't work and now I'm attempting to use a really long IF statement to achieve the desired effect. Does anyone know how to do this or if EXCEL 2007 is even capable of it? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Utilizing a RANDBETWEEN() formula within a LOOKUP formula
Why don't you create a 3 column lookup table like this:
1...Office...=RANDBETWEEN(n,n) 7...Kitchen...=RANDBETWEEN(n,n) 13...Diner...=RANDBETWEEN(n,n) Or, am I not following you? -- Biff Microsoft Excel MVP "Rich Werk." wrote in message ... I'm trying to make a sheet that'll determine various qualities depending on a given number, one of those qualities is a randomly determined number within a variety of ranges. For example: If the number is within the range of 1-6 "Office" will be output. From this "Office" output I need the sheet to generate a number in the range of 2-16. The problem I'm having is that I have 16 initial ranges from 1-96 which result in a different type of building being selected. Based on which building is selected, I need the formula to spit out a number from a range that is determined by the type of building. The formula I attempted to use was rather long and still didn't work: LOOKUP(B3,{0,1,6,12,18,24....},{=RANDBETWEEN(1,3), =RANDBETWEEN(2,16),... and so forth. That didn't work and now I'm attempting to use a really long IF statement to achieve the desired effect. Does anyone know how to do this or if EXCEL 2007 is even capable of it? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
3 Column lookup table?
I think what you're suggesting will work, but I don't know what it is or how to use it. Here's the whole story, at least as much as pertains to having a random number generated based upon the type of building generated with each building having a different random number range.
I've got a table going, Column B is a random number 1-96 using RANDBETWEEN(1,96). Based on that number Column C outputs what type of building it is using =LOOKUP(B3,{0,1,6,12,18,24,30,36,42,48,54,60,66,72 ,78,84,90,97},{"Check","Office","Restaurant","Arse nal","Single_Family_House","Department_Store","Pol ice_Station","Hardware_Store","Gas Station_/_Garage","Convenience_Store","Factory","Warehouse" ,"Hospital","Fire_Station","Specialty_Retail","Sup ermarket","Drug_Store"}). Now I need Column D to reference either Column B or C, doesn't matter because they pretty much mean the same thing and Column B just randomly determines what kind it is and Column C actually puts it into text, but base on what Column B or C is, it'll be a different =RANDBETWEEN(n,n) value. Like Office is =RANDBETWEEN(2,16). By the way, the numerical range for each building type is what I've converted from a dice roll, so =RANDBETWEEN(2,16) is actually the same as rolling 2d8, or two 8-sided dice. I was actually thinking of using a massive nested =IF() statement for this last part, but that may or may not work. I believe I'd have to have the true_value be the =RANDBETWEEN() value for that building, but I don't know if it'll let me just throw the formula into it. T. Valko wrote: Why do not you create a 3 column lookup table like this:1...Office... 02-Nov-09 Why do not you create a 3 column lookup table like this: 1...Office...=RANDBETWEEN(n,n) 7...Kitchen...=RANDBETWEEN(n,n) 13...Diner...=RANDBETWEEN(n,n) Or, am I not following you? -- Biff Microsoft Excel MVP Previous Posts In This Thread: EggHeadCafe - Software Developer Portal of Choice The Guru's Guide To Transact-SQL http://www.eggheadcafe.com/tutorials...-to-trans.aspx |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
3 Column lookup table?
Ok...
Instead of using that long lookup formula build a 3 column like I suggested: 0...Check 1...Office...=RANDBETWEEN(n,n) 6...Restaurant...=RANDBETWEEN(n,n) 12...Arsenal...=RANDBETWEEN(n,n) 18...Single_Family_House...=RANDBETWEEN(n,n) ... ... 90...Drug_Store...=RANDBETWEEN(n,n) Let's assume this table is in the range D1:F17. Then, to get the same result as that long lookup formula: =VLOOKUP(B3,D1:E17,2) Then, to get the random number associated with that location: =VLOOKUP(B3,D1:E17,3) -- Biff Microsoft Excel MVP <Rich Werkmeister wrote in message ... I think what you're suggesting will work, but I don't know what it is or how to use it. Here's the whole story, at least as much as pertains to having a random number generated based upon the type of building generated with each building having a different random number range. I've got a table going, Column B is a random number 1-96 using RANDBETWEEN(1,96). Based on that number Column C outputs what type of building it is using =LOOKUP(B3,{0,1,6,12,18,24,30,36,42,48,54,60,66,72 ,78,84,90,97},{"Check","Office","Restaurant","Arse nal","Single_Family_House","Department_Store","Pol ice_Station","Hardware_Store","Gas Station_/_Garage","Convenience_Store","Factory","Warehouse" ,"Hospital","Fire_Station","Specialty_Retail","Sup ermarket","Drug_Store"}). Now I need Column D to reference either Column B or C, doesn't matter because they pretty much mean the same thing and Column B just randomly determines what kind it is and Column C actually puts it into text, but base on what Column B or C is, it'll be a different =RANDBETWEEN(n,n) value. Like Office is =RANDBETWEEN(2,16). By the way, the numerical range for each building type is what I've converted from a dice roll, so =RANDBETWEEN(2,16) is actually the same as rolling 2d8, or two 8-sided dice. I was actually thinking of using a massive nested =IF() statement for this last part, but that may or may not work. I believe I'd have to have the true_value be the =RANDBETWEEN() value for that building, but I don't know if it'll let me just throw the formula into it. T. Valko wrote: Why do not you create a 3 column lookup table like this:1...Office... 02-Nov-09 Why do not you create a 3 column lookup table like this: 1...Office...=RANDBETWEEN(n,n) 7...Kitchen...=RANDBETWEEN(n,n) 13...Diner...=RANDBETWEEN(n,n) Or, am I not following you? -- Biff Microsoft Excel MVP Previous Posts In This Thread: EggHeadCafe - Software Developer Portal of Choice The Guru's Guide To Transact-SQL http://www.eggheadcafe.com/tutorials...-to-trans.aspx |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
3 Column lookup table?
Correction:
Then, to get the random number associated with that location: =VLOOKUP(B3,D1:E17,3) The correct formula should be: =VLOOKUP(B3,D1:F17,3) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Ok... Instead of using that long lookup formula build a 3 column like I suggested: 0...Check 1...Office...=RANDBETWEEN(n,n) 6...Restaurant...=RANDBETWEEN(n,n) 12...Arsenal...=RANDBETWEEN(n,n) 18...Single_Family_House...=RANDBETWEEN(n,n) .. .. 90...Drug_Store...=RANDBETWEEN(n,n) Let's assume this table is in the range D1:F17. Then, to get the same result as that long lookup formula: =VLOOKUP(B3,D1:E17,2) Then, to get the random number associated with that location: =VLOOKUP(B3,D1:E17,3) -- Biff Microsoft Excel MVP <Rich Werkmeister wrote in message ... I think what you're suggesting will work, but I don't know what it is or how to use it. Here's the whole story, at least as much as pertains to having a random number generated based upon the type of building generated with each building having a different random number range. I've got a table going, Column B is a random number 1-96 using RANDBETWEEN(1,96). Based on that number Column C outputs what type of building it is using =LOOKUP(B3,{0,1,6,12,18,24,30,36,42,48,54,60,66,72 ,78,84,90,97},{"Check","Office","Restaurant","Arse nal","Single_Family_House","Department_Store","Pol ice_Station","Hardware_Store","Gas Station_/_Garage","Convenience_Store","Factory","Warehouse" ,"Hospital","Fire_Station","Specialty_Retail","Sup ermarket","Drug_Store"}). Now I need Column D to reference either Column B or C, doesn't matter because they pretty much mean the same thing and Column B just randomly determines what kind it is and Column C actually puts it into text, but base on what Column B or C is, it'll be a different =RANDBETWEEN(n,n) value. Like Office is =RANDBETWEEN(2,16). By the way, the numerical range for each building type is what I've converted from a dice roll, so =RANDBETWEEN(2,16) is actually the same as rolling 2d8, or two 8-sided dice. I was actually thinking of using a massive nested =IF() statement for this last part, but that may or may not work. I believe I'd have to have the true_value be the =RANDBETWEEN() value for that building, but I don't know if it'll let me just throw the formula into it. T. Valko wrote: Why do not you create a 3 column lookup table like this:1...Office... 02-Nov-09 Why do not you create a 3 column lookup table like this: 1...Office...=RANDBETWEEN(n,n) 7...Kitchen...=RANDBETWEEN(n,n) 13...Diner...=RANDBETWEEN(n,n) Or, am I not following you? -- Biff Microsoft Excel MVP Previous Posts In This Thread: EggHeadCafe - Software Developer Portal of Choice The Guru's Guide To Transact-SQL http://www.eggheadcafe.com/tutorials...-to-trans.aspx |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup Formula | Excel Worksheet Functions | |||
Utilizing a Cell even with a Formula located inside it | Excel Worksheet Functions | |||
I: Utilizing non-standard graphs | Charts and Charting in Excel | |||
Lookup Formula | Excel Discussion (Misc queries) | |||
Lookup Formula - but have a formula if it can't find/match a value | Excel Worksheet Functions |