Array formula counting empty cells as zero

[Marvo]

Hi. I have this array formula

=MAX(FREQUENCY(IF(ALL!$I$2:$I$5375="League",IF(ALL!$E$2:$E$5375="H",IF(ALL!$L$2:$L$5375=0,ROW(ALL!$L$2:$L$5375)))),IF(ALL!$I$2:$I$5375="League",IF(ALL!$E$2:$E$5375="H",IF(ALL!$L$2:$L$5375>0,ROW(ALL!$L$2:$L$5375))))))

which works fine up to a point. It's looking for the longest sequence of zero's. However the formula is counting blank cells as zero's so as there is more data to be added each week and there are more rows left to be filled then the formula is counting these empty cells as zero's which is some cases produces a false result. To stop this happening I have to limit the range of the formula to the last row where data has been entered. Is there a way round this or does it mean every week I have to update the array formula? Many thanks.

[Glenn Kennedy]

Probably the same issue as before. Can you post a sheet, and indicate where the problem is to be found. It's very hard to visualise a formula like that!!

[Marvo]

Sorry Glenn. Its within the array formulas in column C. I want the array to go to the final game of the season which is ROW 5400 on the ALL page which is fine when you're looking for consecutive data but not so good when you're looking for the gaps in between. So it's the LONGEST RUNS WITHOUT that are the problem because the formula counts the blank cells if I set the array at row 5400. If you can have a look at C120 & C121 and if they can be fixed I can work out the rest from there.

[Bo_Ry]

Please try

=MAX(FREQUENCY(IF(ALL!$I$2:$I$5400="League",IF(ALL!$L$2:$L$5400<>"",IF(ALL!$L$2:$L$5400=0,ROW(ALL!$L$2:$L$5400)))),(IF(ALL!$I$2:$I$5400="League",IF(ALL!$L$2:$L$5400>0,ROW(ALL!$L$2:$L$5400))))))

[Marvo]

Yep that works Bo-Ry. so basically I've just got to add the extra if segment to my array formulas. Great. Many thanks.

[Glenn Kennedy]

I would be tempted to go for Named ranges at this point. These adjust automatically to suit the length of your data.

For the competion type (Comp) I used:
ALL!$I$2:INDEX(ALL!$I$2:$I$20000,SUMPRODUCT(--(LEN(ALL!$J$2:$J$20000)>0)))

the red bit ensures that it only goes down the competition column to the last result.

For the result (WDL), I used:
=ALL!$J$2:INDEX(ALL!$J$2:$J$20000,SUMPRODUCT(--(LEN(ALL!$J$2:$J$20000)>0)))

for scores home and away, set up named ranges based on WDL, as you don't want them to look at rows where no result is in.

The formula (C120) then becomes:
=MAX(FREQUENCY(IF(Comp="League",IF(WDL<>"W",ROW(WDL))),(IF(Comp="League",IF(WDL="W",ROW(WDL))))))

I am leaving now. Can you add the other named ranges? If you have issues, I'll be back tomorrow. Incidentally, you have a fair number of dead Named Ranges (CTRL-F3). I suggest you kill the ones that are giving errors.

[Marvo]

Hi Glenn, that does seem like a good idea but I'm not sure how to set about doing it. I've used named ranges before many, many years ago but I cant recall how to set them up. The dead ranges you mention are still alive, they connect to other pages of the workbook I didn't include to make the example smaller. I'll have a look at it, see if I can make it work. Many thanks.

[Glenn Kennedy]

Adapt the formula for your new range in a cell. Copy.

CTRL-F3.

New

Paste

Done

check it (sometimes they get confused and slip to whole row/column references when copy pasting, but usually only if there are non-$'d ranges, which you won't have).

Run.

[Marvo]

Sorry Glenn, I tried but I couldn't do it. Managed to complete C113-C115 and C118-C120 as they were yours in Name Manger but couldn't set my own up for home or away (Column E) or goals scored (K) or conceded (L) whatever I tried, copying what you had done, trying stuff off my own back, no success, kept giving me a return of #NA.

I've attached the full workbook, you will see I have a "list" page for name manager, probably don't need it.

[BMV]

@Glenn

For the competion type (Comp) I used:
ALL!$I$2:INDEX(ALL!$I$2:$I$20000,SUMPRODUCT(--(LEN(ALL!$J$2:$J$20000)>0)))

it is working but what reason to use it instand of COUNTIF
ALL!$I$2:INDEX(ALL!$I$2:$I$20000,COUNTIF(ALL!$J$2:$J$20000;"?*"))
For my opinion the part =COUNTIF(ALL!$J$2:$J$20000;"?*") must be calculated in separate cell for best performance.

For this example B112 =COUNTIF(ALL!J:J,"?*")+1
Comp =ALL!$I$2:INDEX(ALL!$I:$I,Summary!$B$112)
WDL==ALL!$J$2:INDEX(ALL!$J:$J,Summary!$B$112)

[Glenn Kennedy]

The reason is simple. I didn't think of it.

[Marvo]

So, I've got up, all ready to spend hours on this to get it right and now I'm totally confused?

The 5368 result in B112 in the example by BMV is how many rows there are?

The total amount of games played is 5367.

What am I looking at?

On the ALL sheet, in cell R5350 will always be the total amount of games played. Wouldn't using this cell in the formula make things easier? If I need it to result in 5368 I could always +1 it?

[Marvo]

I think I've got it now guys. Will be back if I hit a snag but fingers crossed.

[BMV]

+1 because i ise whole column for index and count non zerro cells. Enter header and +1 can be deleted. However it is right for table from row 1 .

[Marvo]

Okay guys, all done. Thank you so much for all the help.

Completed workbook attached. Any advice always appreciated.

[AliGW]

I would remove the solved tag from this thread, if I were you.

[Marvo]

I think we're done Ali, I wouldn't want to hog all the valuable time of your contributors. I'm very happy with the outcome and I'm learning, just very slowly.

[AliGW]

Oh, I see - I thought you needed some more help.

Don't worry - nobody will let you hog them!!!