| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find a value and count the rows back to the reference row.
I have a column of random numbers and need to find, from a specific row
forward, the first number that is = than the number in the reference row. Then I need to know how many rows away from the reference row (excluding empty rows) that number was found in. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Find a value and count the rows back to the reference row.
Can you post a sample that depicts what you mean?
-- Biff Microsoft Excel MVP "dlbeiler" wrote in message ... I have a column of random numbers and need to find, from a specific row forward, the first number that is = than the number in the reference row. Then I need to know how many rows away from the reference row (excluding empty rows) that number was found in. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Find a value and count the rows back to the reference row.
A picture is worth a thousand words. The following column is D46:D61 with
D52-D54 empty. I need a formula that will proceed through column D and find the first occurance of a value that is <= 1.3590, which in this case is D58. Then I need to count how many rows of values there are from D46 to D58, excluding empty cells. Each row is 30 minutes of data and I need the result in hours(/2). In this case, Row 58 is 5.0 hours from Row 46. 1.3601 1.3603 1.3604 1.3606 1.3599 1.3600 1.3601 1.3600 1.3599 1.3587 1.3591 1.3591 1.3587 "T. Valko" wrote: Can you post a sample that depicts what you mean? -- Biff Microsoft Excel MVP "dlbeiler" wrote in message ... I have a column of random numbers and need to find, from a specific row forward, the first number that is = than the number in the reference row. Then I need to know how many rows away from the reference row (excluding empty rows) that number was found in. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Find a value and count the rows back to the reference row.
Try this array formula** :
=COUNT(D46:INDEX(D46:D61,MATCH(1,(D46:D61<"")*(D4 6:D61<=1.359),0)))/2 ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "dlbeiler" wrote in message ... A picture is worth a thousand words. The following column is D46:D61 with D52-D54 empty. I need a formula that will proceed through column D and find the first occurance of a value that is <= 1.3590, which in this case is D58. Then I need to count how many rows of values there are from D46 to D58, excluding empty cells. Each row is 30 minutes of data and I need the result in hours(/2). In this case, Row 58 is 5.0 hours from Row 46. 1.3601 1.3603 1.3604 1.3606 1.3599 1.3600 1.3601 1.3600 1.3599 1.3587 1.3591 1.3591 1.3587 "T. Valko" wrote: Can you post a sample that depicts what you mean? -- Biff Microsoft Excel MVP "dlbeiler" wrote in message ... I have a column of random numbers and need to find, from a specific row forward, the first number that is = than the number in the reference row. Then I need to know how many rows away from the reference row (excluding empty rows) that number was found in. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Find a value and count the rows back to the reference row.
Further assistance needed. The formula works as intended, however, I cannot
figure out how to adapt it to my situation. Column AJ has either the word "up" or the word "down". If the word is "up" I use: =IF(AJ24="up",COUNT(E24:INDEX(E24:E5011,MATCH(1,(E 24:E5011<"")*(E24:E5011=G24+0.003),0)))/2) If the word is "down" I use: =IF(AJ24="down",COUNT(F24:INDEX(F24:F5011,MATCH(1, (F24:F5011<"")*(F24:F5011<=G24+0.003),0)))/2) How do I create one formula to address the appropriate condition? "T. Valko" wrote: Try this array formula** : =COUNT(D46:INDEX(D46:D61,MATCH(1,(D46:D61<"")*(D4 6:D61<=1.359),0)))/2 ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "dlbeiler" wrote in message ... A picture is worth a thousand words. The following column is D46:D61 with D52-D54 empty. I need a formula that will proceed through column D and find the first occurance of a value that is <= 1.3590, which in this case is D58. Then I need to count how many rows of values there are from D46 to D58, excluding empty cells. Each row is 30 minutes of data and I need the result in hours(/2). In this case, Row 58 is 5.0 hours from Row 46. 1.3601 1.3603 1.3604 1.3606 1.3599 1.3600 1.3601 1.3600 1.3599 1.3587 1.3591 1.3591 1.3587 "T. Valko" wrote: Can you post a sample that depicts what you mean? -- Biff Microsoft Excel MVP "dlbeiler" wrote in message ... I have a column of random numbers and need to find, from a specific row forward, the first number that is = than the number in the reference row. Then I need to know how many rows away from the reference row (excluding empty rows) that number was found in. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Find a value and count the rows back to the reference row.
Further assistance needed. The formula works as intended, however, I cannot
figure out how to adapt it to my situation. Column AJ has either the word "up" or the word "down". If the word is "up" I use: =IF(AJ24="up",COUNT(E24:INDEX(E24:E5011,MATCH(1,(E 24:E5011<"")*(E24:E5011=G24+0.003),0)))/2) If the word is "down" I use: =IF(AJ24="down",COUNT(F24:INDEX(F24:F5011,MATCH(1, (F24:F5011<"")*(F24:F5011<=G24+0.003),0)))/2) How do I create one formula to address each condition with the appropriate formula? "T. Valko" wrote: Try this array formula** : =COUNT(D46:INDEX(D46:D61,MATCH(1,(D46:D61<"")*(D4 6:D61<=1.359),0)))/2 ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "dlbeiler" wrote in message ... A picture is worth a thousand words. The following column is D46:D61 with D52-D54 empty. I need a formula that will proceed through column D and find the first occurance of a value that is <= 1.3590, which in this case is D58. Then I need to count how many rows of values there are from D46 to D58, excluding empty cells. Each row is 30 minutes of data and I need the result in hours(/2). In this case, Row 58 is 5.0 hours from Row 46. 1.3601 1.3603 1.3604 1.3606 1.3599 1.3600 1.3601 1.3600 1.3599 1.3587 1.3591 1.3591 1.3587 "T. Valko" wrote: Can you post a sample that depicts what you mean? -- Biff Microsoft Excel MVP "dlbeiler" wrote in message ... I have a column of random numbers and need to find, from a specific row forward, the first number that is = than the number in the reference row. Then I need to know how many rows away from the reference row (excluding empty rows) that number was found in. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Find a value and count the rows back to the reference row.
=IF(AJ24="up",COUNT(E24:INDEX(E24:E5011,MATCH(1,(E 24:E5011<"")*(E24:E5011=G24+0.003),0)))/2,IF(AJ24="down",COUNT(F24:INDEX(F24:F5011,MATCH(1 ,(F24:F5011<"")*(F24:F5011<=G24+0.003),0)))/2,"Grand
Old Duke of York")) :-) -- David Biddulph "dlbeiler" wrote in message ... Further assistance needed. The formula works as intended, however, I cannot figure out how to adapt it to my situation. Column AJ has either the word "up" or the word "down". If the word is "up" I use: =IF(AJ24="up",COUNT(E24:INDEX(E24:E5011,MATCH(1,(E 24:E5011<"")*(E24:E5011=G24+0.003),0)))/2) If the word is "down" I use: =IF(AJ24="down",COUNT(F24:INDEX(F24:F5011,MATCH(1, (F24:F5011<"")*(F24:F5011<=G24+0.003),0)))/2) How do I create one formula to address each condition with the appropriate formula? "T. Valko" wrote: Try this array formula** : =COUNT(D46:INDEX(D46:D61,MATCH(1,(D46:D61<"")*(D4 6:D61<=1.359),0)))/2 ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "dlbeiler" wrote in message ... A picture is worth a thousand words. The following column is D46:D61 with D52-D54 empty. I need a formula that will proceed through column D and find the first occurance of a value that is <= 1.3590, which in this case is D58. Then I need to count how many rows of values there are from D46 to D58, excluding empty cells. Each row is 30 minutes of data and I need the result in hours(/2). In this case, Row 58 is 5.0 hours from Row 46. 1.3601 1.3603 1.3604 1.3606 1.3599 1.3600 1.3601 1.3600 1.3599 1.3587 1.3591 1.3591 1.3587 "T. Valko" wrote: Can you post a sample that depicts what you mean? -- Biff Microsoft Excel MVP "dlbeiler" wrote in message ... I have a column of random numbers and need to find, from a specific row forward, the first number that is = than the number in the reference row. Then I need to know how many rows away from the reference row (excluding empty rows) that number was found in. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Find a value and count the rows back to the reference row.
"Grand Old Duke of York"
??? -- Biff Microsoft Excel MVP "David Biddulph" <groups [at] biddulph.org.uk wrote in message ... =IF(AJ24="up",COUNT(E24:INDEX(E24:E5011,MATCH(1,(E 24:E5011<"")*(E24:E5011=G24+0.003),0)))/2,IF(AJ24="down",COUNT(F24:INDEX(F24:F5011,MATCH(1 ,(F24:F5011<"")*(F24:F5011<=G24+0.003),0)))/2,"Grand Old Duke of York")) :-) -- David Biddulph "dlbeiler" wrote in message ... Further assistance needed. The formula works as intended, however, I cannot figure out how to adapt it to my situation. Column AJ has either the word "up" or the word "down". If the word is "up" I use: =IF(AJ24="up",COUNT(E24:INDEX(E24:E5011,MATCH(1,(E 24:E5011<"")*(E24:E5011=G24+0.003),0)))/2) If the word is "down" I use: =IF(AJ24="down",COUNT(F24:INDEX(F24:F5011,MATCH(1, (F24:F5011<"")*(F24:F5011<=G24+0.003),0)))/2) How do I create one formula to address each condition with the appropriate formula? "T. Valko" wrote: Try this array formula** : =COUNT(D46:INDEX(D46:D61,MATCH(1,(D46:D61<"")*(D4 6:D61<=1.359),0)))/2 ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "dlbeiler" wrote in message ... A picture is worth a thousand words. The following column is D46:D61 with D52-D54 empty. I need a formula that will proceed through column D and find the first occurance of a value that is <= 1.3590, which in this case is D58. Then I need to count how many rows of values there are from D46 to D58, excluding empty cells. Each row is 30 minutes of data and I need the result in hours(/2). In this case, Row 58 is 5.0 hours from Row 46. 1.3601 1.3603 1.3604 1.3606 1.3599 1.3600 1.3601 1.3600 1.3599 1.3587 1.3591 1.3591 1.3587 "T. Valko" wrote: Can you post a sample that depicts what you mean? -- Biff Microsoft Excel MVP "dlbeiler" wrote in message ... I have a column of random numbers and need to find, from a specific row forward, the first number that is = than the number in the reference row. Then I need to know how many rows away from the reference row (excluding empty rows) that number was found in. |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Find a value and count the rows back to the reference row.
In my first attempt at the formula I had written "Neither up nor down", and
I'm afraid I couldn't resist the temptation to reword it. -- David Biddulph "T. Valko" wrote in message ... "Grand Old Duke of York" ??? -- Biff Microsoft Excel MVP "David Biddulph" <groups [at] biddulph.org.uk wrote in message ... =IF(AJ24="up",COUNT(E24:INDEX(E24:E5011,MATCH(1,(E 24:E5011<"")*(E24:E5011=G24+0.003),0)))/2,IF(AJ24="down",COUNT(F24:INDEX(F24:F5011,MATCH(1 ,(F24:F5011<"")*(F24:F5011<=G24+0.003),0)))/2,"Grand Old Duke of York")) :-) -- David Biddulph "dlbeiler" wrote in message ... Further assistance needed. The formula works as intended, however, I cannot figure out how to adapt it to my situation. Column AJ has either the word "up" or the word "down". If the word is "up" I use: =IF(AJ24="up",COUNT(E24:INDEX(E24:E5011,MATCH(1,(E 24:E5011<"")*(E24:E5011=G24+0.003),0)))/2) If the word is "down" I use: =IF(AJ24="down",COUNT(F24:INDEX(F24:F5011,MATCH(1, (F24:F5011<"")*(F24:F5011<=G24+0.003),0)))/2) How do I create one formula to address each condition with the appropriate formula? "T. Valko" wrote: Try this array formula** : =COUNT(D46:INDEX(D46:D61,MATCH(1,(D46:D61<"")*(D4 6:D61<=1.359),0)))/2 ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "dlbeiler" wrote in message ... A picture is worth a thousand words. The following column is D46:D61 with D52-D54 empty. I need a formula that will proceed through column D and find the first occurance of a value that is <= 1.3590, which in this case is D58. Then I need to count how many rows of values there are from D46 to D58, excluding empty cells. Each row is 30 minutes of data and I need the result in hours(/2). In this case, Row 58 is 5.0 hours from Row 46. 1.3601 1.3603 1.3604 1.3606 1.3599 1.3600 1.3601 1.3600 1.3599 1.3587 1.3591 1.3591 1.3587 "T. Valko" wrote: Can you post a sample that depicts what you mean? -- Biff Microsoft Excel MVP "dlbeiler" wrote in message ... I have a column of random numbers and need to find, from a specific row forward, the first number that is = than the number in the reference row. Then I need to know how many rows away from the reference row (excluding empty rows) that number was found in. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I switch from 1,2,3... back to A,B,C... column reference na | Excel Discussion (Misc queries) | |||
| How do you change Cell name reference from R[-2]C[-1] back to C2 | Excel Discussion (Misc queries) | |||
| Find and Count Frequency of Numeric Value in Non-Contiguous Rows | Excel Worksheet Functions | |||
| Macro to: Find a Reference, and then Paste into the 10 Rows Below | Excel Discussion (Misc queries) | |||
| Match Last Occurrence of Numeric Value and Count BACK to Previous | Excel Worksheet Functions | |||
Linear Mode
