| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to count number of errors? | Excel Programming | |||
| Count number of cells with data validation errors | Excel Programming | |||
| COUNT how many ROWS ago out of 10 days that the highest high in 10 days was made | Excel Worksheet Functions | |||
| I want to count the highest number of equal cells | Excel Worksheet Functions | |||
| 2 rows, highest No in row 1, then highest number in row 2 relating to that column, possible duplicates | Excel Worksheet Functions | |||
Linear Mode
