Count the highest number of days without “errors”

[granlygard4400]

Dear all

I want to count, the highest number of days without “errors” in the table below (this is a subset of the overall table).

"Yes" means no error and "NO" means one or more errors.
The table is dynamic and gets data from underlying tables.

I want an automatic counting, showing the highest number of days without error. As an example in this table, there is a continuous period from 09.nov to 13.nov without error - 5 days.
This number should only be overwritten when a continuous period of “YES’s” is higher.

- Thank you in advance for your help. Best Hans

Date Check
05. nov 2013  Yes
06. nov 2013  Yes
07. nov 2013  Yes
08. nov 2013  No
09. nov 2013  Yes
10. nov 2013  Yes
11. nov 2013  Yes
12. nov 2013  Yes
13. nov 2013  Yes
14. nov 2013  No
15. nov 2013  Yes
16. nov 2013  Yes
17. nov 2013  No
18. nov 2013  Yes
19. nov 2013  Later
20. nov 2013  Later
21. nov 2013  Later

 

[Claus Busch]

Hi Hans,

Am Mon, 18 Nov 2013 08:01:44 -0800 (PST) schrieb
(e-mail address removed):

"Yes" means no error and "NO" means one or more errors.
The table is dynamic and gets data from underlying tables.

Date Check
05. nov 2013  Yes
06. nov 2013  Yes
07. nov 2013  Yes
08. nov 2013  No

try:
=MAX(LARGE((B2:B1000<>"Yes")*(B1:B999="Yes")*ROW(1:999),ROW(1:999))-LARGE((B2:B1000="Yes")*(B1:B999<>"Yes")*ROW(1:999),ROW(1:999)))
and array-enter the formula with CTRL+Shift+Enter


Regards
Claus B.

 

[hwested]

Hi Claus

Sorry for this late reply - It works and I am happy !

- Thanks

Best Hans