Count the highest number of days without “errors”
 

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

Count the highest number of days without ?errors?
 

Hi Hans,

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

"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.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Count the highest number of days without ?errors?
 

Hi Claus

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

- Thanks

Best Hans





On Monday, 18 November 2013 17:56:39 UTC+1, Claus Busch wrote:
Hi Hans,



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

:



"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.

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2