Find a value and count the rows back to the reference row.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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
 
Try this array formula** :

=COUNT(D46:INDEX(D46:D61,MATCH(1,(D46:D61<>"")*(D46:D61<=1.359),0)))/2

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)
 
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,(E24: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?
 
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,(E24: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?
 
=IF(AJ24="up",COUNT(E24:INDEX(E24:E5011,MATCH(1,(E24: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"))
:-)
 
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.
 
Back
Top