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?

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 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

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

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