Counting last (most recent) consecutive positive or negative numbers in a row

[damianberry]

I am trying to keep track of the current standings for a fantasy football league. I want to track the current winning/losing streak for each team.

Therefore I need to count the consecutive positive numbers going backwards from the most recent date for wins, and the same thing going backwards for losses.

I've included a worksheet with some sample numbers and what the outcomes should be in the cells. The positive numbers are how much a team won by, the negatives are how much they lost by.

Thanks,
Damian

[benishiryo]

hi again Damian. try this 2 formulas in K7 & M7 respectively:
=IF(I7>0,COLUMNS($B7:$I7)-MAX(IF($B7:$I7<0,COLUMN($B7:$I7)-1)),"")
=IF(I7<0,COLUMNS($B7:$I7)-MAX(IF($B7:$I7>0,COLUMN($B7:$I7)-1)),"")

both are array formulas. that means you got to paste it inside the formula bar, then press CTRL + SHIFT + ENTER to confirm

[damianberry]

benishiryo

Your solution works if all cells are filled. Unfortunately, we have a 17 week schedule and we've only used 9 weeks thus far. We have blank cells in the row at the end, and your formula doesn't account for that. We only want it to count back from the last cell with a number in it. I've included another example.....would you please take a look for me?

Thanks,
Damian

[benishiryo]

then try:
=IF(LOOKUP(99^99,$B$7:$O$7)>0,COUNTA($B$7:$O$7)-MAX(IF($B7:$O7<0,COLUMN($B7:$O7)-1)),"")
=IF(LOOKUP(99^99,$B$7:$O$7)<0,COUNTA($B7:$O7)-MAX(IF($B7:$O7>0,COLUMN($B7:$O7)-1)),"")

same thing. CTRL + SHIFT + ENTER

[damianberry]

Benishiryo,

I appreciate your help, and your formula works when I try it in the examples I've sent you. However, for some reason, and I have no idea why, I can't get it to work in my workbook. Here's the sheet from it I'm trying to compute in.

In F53:S53 is the range I'm working from. Way over in CY53 and DA53 is where I'm trying to get the results.

Thanks,
Damian

[benishiryo]

i understand that you try to simplify the example, but many factors can affect the results. like the change in column or if there's a formula in it. you can still do up a sample, but considering all these factors if possible. i do love reading a simplified eg. try these now:
=IF(INDEX($F$53:$S$53,COUNTIF($F$53:$S$53,"<>0"))>0,COUNTIF(F53:S53,"<>0")-MAX(IF($F53:$S53<0,COLUMN($F53:$S53)-5)),"")
=IF(INDEX($F$53:$S$53,COUNTIF($F$53:$S$53,"<>0"))<0,COUNTIF($F$53:$S$53,"<>0")-MAX(IF($F53:$S53>0,COLUMN($F53:$S53)-5)),"")

[damianberry]

Fantastic! I have it fully functional now! Super!

Thank you so very much!

Damian