Problem with formula for finding last greater than/less than or equal to value

[DubyaG]

Hi everyone,

Here's the issue: Dates (most recent at top) in column A, and then
simple "Win" or "Lose" in Column B, and then in Column C a count o
consecutive wins or losses, which starts at +1 for each win and goes u
sequentially for each consecutive win thereafter, and starts at -1 fo
losses and goes down 1 for consecutive losses thereafter.

What I'm trying to do is find a formula which will record the last tim
there were the current number of consecutive wins/losses or a greate
value.

I.e. If a team has currently won 4 games in a row, it will display +4 i
the current date cell, and I want the formula to show the last time th
team won 4 games OR MORE. Likewise, if they've lost 3 times in a row i
shows -3, and I want to find the last time the team lost 3 games or eve
more (so -3,-4,-5, etc.).

Here's what I have so far (in an array formula):

=INDEX(A16:A264,MATCH(TRUE,C16:C264>=C15,0))

Where A16 is the location of the previous date, C16 the location o
previous date's +/- value, and C15 the current +/1 value.

Now the problem is, the formula works perfectly for negative values, s
it will always find the last date where the team lost the same or
greater number of games. BUT, when it's working with positive values
the formula returns the date of the last loss (i.e. the most recen
negative value) instead of the last positive value which is equal to o
greater than the current one.

Any and all help would be hugely appreciated.

Thanks

 

[Don Guillett]

Hi everyone,



Here's the issue: Dates (most recent at top) in column A, and then a

simple "Win" or "Lose" in Column B, and then in Column C a count of

consecutive wins or losses, which starts at +1 for each win and goes up

sequentially for each consecutive win thereafter, and starts at -1 for

losses and goes down 1 for consecutive losses thereafter.



What I'm trying to do is find a formula which will record the last time

there were the current number of consecutive wins/losses or a greater

value.



I.e. If a team has currently won 4 games in a row, it will display +4 in

the current date cell, and I want the formula to show the last time the

team won 4 games OR MORE. Likewise, if they've lost 3 times in a row it

shows -3, and I want to find the last time the team lost 3 games or even

more (so -3,-4,-5, etc.).



Here's what I have so far (in an array formula):



=INDEX(A16:A264,MATCH(TRUE,C16:C264>=C15,0))



Where A16 is the location of the previous date, C16 the location of

previous date's +/- value, and C15 the current +/1 value.



Now the problem is, the formula works perfectly for negative values, so

it will always find the last date where the team lost the same or a

greater number of games. BUT, when it's working with positive values,

the formula returns the date of the last loss (i.e. the most recent

negative value) instead of the last positive value which is equal to or

greater than the current one.



Any and all help would be hugely appreciated.



Thanks!

send file to dguillett1 @gmail.com with a complete explanation and examples

 

[plinius]

Il 28/09/2012 04:15, DubyaG ha scritto:

Hi everyone,

Here's the issue: Dates (most recent at top) in column A, and then a
simple "Win" or "Lose" in Column B, and then in Column C a count of
consecutive wins or losses, which starts at +1 for each win and goes up
sequentially for each consecutive win thereafter, and starts at -1 for
losses and goes down 1 for consecutive losses thereafter.

What I'm trying to do is find a formula which will record the last time
there were the current number of consecutive wins/losses or a greater
value.

I.e. If a team has currently won 4 games in a row, it will display +4 in
the current date cell, and I want the formula to show the last time the
team won 4 games OR MORE. Likewise, if they've lost 3 times in a row it
shows -3, and I want to find the last time the team lost 3 games or even
more (so -3,-4,-5, etc.).

Here's what I have so far (in an array formula):

=INDEX(A16:A264,MATCH(TRUE,C16:C264>=C15,0))

Where A16 is the location of the previous date, C16 the location of
previous date's +/- value, and C15 the current +/1 value.

Now the problem is, the formula works perfectly for negative values, so
it will always find the last date where the team lost the same or a
greater number of games. BUT, when it's working with positive values,
the formula returns the date of the last loss (i.e. the most recent
negative value) instead of the last positive value which is equal to or
greater than the current one.

Any and all help would be hugely appreciated.

Thanks!

Use IF:
=INDEX(A16:A$264,IF(C15>0,MATCH(TRUE,C16:C$264>=C15,0),MATCH(TRUE,C16:C$264<=C15,0)))

or search between numbers having the same sign only:
=INDEX(A16:A$264,MATCH(TRUE,ABS(C16:C$264)*(SIGN(C15)=SIGN(C16:C264))>=ABS(C15),0))

Hi,
E.