| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| where is excel2007 installed? | Setting up and Configuration of Excel | |||
| SUM formula in Excel2007 | Excel Worksheet Functions | |||
| Complex If/Then formula? | Excel Discussion (Misc queries) | |||
| complex formula | Excel Discussion (Misc queries) | |||
| Complex formula | Excel Discussion (Misc queries) | |||
Linear Mode
