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.
|