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

SOLVED: Please delete.

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!

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

On Thursday, September 27, 2012 9:15:15 PM UTC-5, DubyaG wrote:
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!









--

DubyaG

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

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

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(C150,MATCH(TRUE,C16:C$264=C1 5,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(C 15)=SIGN(C16:C264))=ABS(C15),0))

Hi,
E.