complex randbeween formula, Excel2007
 

=IF(RANDBETWEEN(INT(A1084),$BG$6)1,$BM$6,IF(RANDB ETWEEN(INT(A1084),$BG
$9)1,$BM$9,IF(RANDBETWEEN(INT(A1084),$BG$10)1,$B M
$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?

complex randbeween formula, Excel2007
 

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" wrote in message
oups.com...
=IF(RANDBETWEEN(INT(A1084),$BG$6)1,$BM$6,IF(RANDB ETWEEN(INT(A1084),$BG
$9)1,$BM$9,IF(RANDBETWEEN(INT(A1084),$BG$10)1,$B M
$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?

complex randbeween formula, Excel2007
 

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

complex randbeween formula, Excel2007
 

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" wrote:

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

complex randbeween formula, Excel2007
 

On Sep 26, 3:01 pm, bj wrote:
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" wrote:
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- Hide quoted text -

- 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

complex randbeween formula, Excel2007
 

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




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

complex randbeween formula, Excel2007
 

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" wrote in message
oups.com...
On Sep 26, 3:01 pm, bj wrote:
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" wrote:
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- Hide quoted text -

- 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

complex randbeween formula, Excel2007
 

On Sep 27, 7:41 am, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:
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" wrote in message

oups.com...



On Sep 26, 3:01 pm, bj wrote:
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" wrote:
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- Hide quoted text -

- 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- Hide quoted text -

- Show quoted text -

Thanks to everyone for helping out.
Hani
New2007 user