Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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! Last edited by DubyaG : September 28th 12 at 09:30 AM Reason: SOLVED |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem with Conditional Formatting and Greater than or equal too | Excel Discussion (Misc queries) | |||
Greater Than/Equal To Formula | Excel Discussion (Misc queries) | |||
formula to workout equal to or greater than | Excel Worksheet Functions | |||
GREATER OR EQUAL TO BUT LESS THAN Problem using Sumproduct | Excel Worksheet Functions | |||
Formula which is greater tahn or equal to zero | Excel Worksheet Functions |