A | B | C | D | E | F | G | H | I | |
1 | week1 | week2 | week3 | week4 | week5 | sum "ok" weeks | longest coherent period with OK | coherent status with "OK" backwards as of week 5 | |
2 | Becky | ok | ok | failed | ok | failed | 3 | 2 | 0 |
3 | Sam | failed | failed | ok | ok | ok | 3 | 3 | 3 |
4 | John | failed | failed | failed | ok | ok | 2 | 2 | 2 |
5 | Paula | failed | ok | ok | failed | failed | 2 | 2 | 0 |
<tbody>
</tbody>
This is a simplified table I am working on. Its the formulas in Column "H" and "I" that I am struggling with.
Goal of tableis to calculate which person has achieved goals each week. And to specify whohas done most weeks coherent OK
Column H: Goalis to count how many coherent weeks in a row a person has done OK at thehighest from week 1 to week 5 For Becky this is 2 (week 1 and week 2)
Column I: Goalhere is to count which person has the most coherent amount of weeks as of nowwith the result OK (So If we imagine being in week 5 now, we need to count coherentweeks with OK from week 5, then week 4, then week 3 etc
I can't seem to get this sorted by myself. Anyone please suggest.
Best Regards:
Wigarth