complex randbeween formula, Excel2007

[Titus]

=IF(RANDBETWEEN(INT(A1084),$BG$6)>1,$BM$6,IF(RANDBETWEEN(INT(A1084),$BG
$9)>1,$BM$9,IF(RANDBETWEEN(INT(A1084),$BG$10)>1,$BM
$10,IF(RANDBETWEEN(INT(A1084),$BG$11)>1,$BM
$11,IF(RANDBETWEEN(INT(A1084),$BG$12)>1,$BM
$12,IF(RANDBETWEEN(INT(A1084),$BG$13)>1,$BM
$13,IF(RANDBETWEEN(INT(A1084),$BG$15)>1,$BM
$15,IF(RANDBETWEEN(INT(A1084),$BG$16)>1,$BM
$16,IF(RANDBETWEEN(INT(A1084),$BG$19)>1,$BM$19,$BM$24)))))))))

I am trying to use the above formula to pick a name from a table
"column BM", the name is dependent on the values in column BG and the
variable in column A.
the formula works for the first name, but it gives #num! after that.
Can you help me please?

 

[Bernie Deitrick]

Titus,

Post an example of the data in column BG and BM, and what is in cell A1084, and describe (in word)
what you actually want the formula to do.

HTH,
Bernie
MS Excel MVP

 

[Titus]

the data in columns BG is depth Interval values ,e.g. 2510 ft 3250 ft
the data in BM is the name related to this depth Interval , e.g.
Mantu, and the data in the A column is the current depth.
what i am trying to do is correlate the current depth (Axxxx) to Depth
Interval(BGxxxx:BGxxxy) and name it accordingly from the list BMxxxx.
so, if the current depth fit in depth interval 1, the name is picked
up from the related list, otherwise i check the next depth interval
and so on.
hope this is clear and thanks for your help in advance.

HTH
Hani
New 2007 user

 

[Guest]

randbetween is not what you want, Randbetween selects a random integer
between two variables
I am not sure what you have but probably a vlookup
=vlookup(A1084,$BG$6:$BM$19,7)
might do most of what you want
I do not know where the BM 24 fits in

 

[Titus]

randbetween is not what you want, Randbetween selects a random integer
between two variables
I am not sure what you have but probably a vlookup
=vlookup(A1084,$BG$6:$BM$19,7)
might do most of what you want
I do not know where the BM 24 fits in






- Show quoted text -

Vlookup does not work for me, it picks up the closest number and match
the result. this is an attempt to have a value verified between two
values, not the close match.
what i need is a "between" function
Thanks for the suggestion

 

[Peo Sjoblom]

What do you mean, which value should it pick if it can't find the exact
value, assume the lookup value is 10.50 and the values are 10 and 11?


--


Regards,


Peo Sjoblom

 

[Bernie Deitrick]

Titus,

If you have a table of depths

0
500
1250
2100
2510
3250
etc

put the names next to the value that STARTS the depth interval for that name. Since you only gave
one name and interval:

0 Really Shallow
500 Kind of Shallow
1250 Deep
2100 Not Mantu
2510 Mantu
3250 Very Mantu
etc

and if you have the depth

2700

then VLOOKUP(2700, TableAbove, 2) will return "Mantu"

HTH,
Bernie
MS Excel MVP

 

[Titus]

Titus,

If you have a table of depths

0
500
1250
2100
2510
3250
etc

put the names next to the value that STARTS the depth interval for that name. Since you only gave
one name and interval:

0 Really Shallow
500 Kind of Shallow
1250 Deep
2100 Not Mantu
2510 Mantu
3250 Very Mantu
etc

and if you have the depth

2700

then VLOOKUP(2700, TableAbove, 2) will return "Mantu"

HTH,
Bernie
MS Excel MVP







- Show quoted text -

Thanks to everyone for helping out.
Hani
New2007 user