Advanced counting? or do I need VBA for this?

[wigarth]

	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

 

[kensandy1]

You'll need a protected data sheet to get this to work if you want to do it without VBA.

A

B

C

D

E

F

G

H

I

J

K

L

M

N

1

week1

week2

week3

week4

week5

week6

week7

week8

week9

week10

week11

=MAX(B6:L6)

2

Becky

ok

ok

failed

ok

failed

ok

ok

ok

ok

ok

ok

3

Sam

failed

failed

ok

ok

ok

ok

ok

ok

ok

failed

ok

4

John

failed

failed

failed

ok

ok

failed

failed

ok

ok

ok

ok

5

Paula

failed

ok

ok

failed

failed

failed

failed

failed

failed

failed

failed

6

=IF(B2="ok", IF(C2<>"failed",IF(B2=C2,IF(A6>1,A6+1,2),1), 0),0)

7

8

9

10

<tbody>
</tbody>

Drag the formula in B6 down and over
Drag the formula in N2 down.

That should give you what you want. Put the numbers from the If's into a data sheet and protect the formulas and this should work without VBA.

 

[wigarth]

First ofall, Thanks for the reply and thanks for the suggestion.
I reallyappreciate you taking the time to try and help.

Unfortunatelyit doesn’t work.

Becky comesout with 6 witch is correct, but Sam comes out with 2,4???
It was alot of “IF” in that formula. It seems like one of them is triggering wrong atsome point.

Best regards:
Wigarth

 

[wigarth]

Sorry! When pasting formula i missed one "," that should have been a ";" So when I fixed that it worked. Thank you so much.