count weeks since last wash

**andre** · 2011-08-26, 03:33 PM

hi. i have a problem with a list. what i want is to find out how many weeks since last wash was done by any of the residents. the list is partly here. i need to count the zeroes back in time....week 10- week 9- week 8 and so on, until week 7 where JE has done his part and marked that with a 1....the answer should be in number of weeks since....this will solve a lot of arguments between nighbours

Navn	Uke 1	Uke 2	Uke 3	Uke 4	Uke 5	Uke 6	Uke 7	Uke 8	Uke 9	Uke 10	Sum	weeks since
JE	0	1	0	0	0	1	0	0	0	0	2	need forula
AR	1	0	0	0	0	0	0	0	0	0	1
FL	0	0	0	0	0	0	0	0	0	1	1
ACG	1	0	0	0	0	0	0	0	0	0	1
RS	0	0	1	0	0	0	0	0	1	0	2
GH	0	0	0	0	0	0	1	0	0	0	1
JK	0	0	0	1	0	0	0	0	0	0	1
TVA	0	0	0	0	1	0	0	0	0	0	1
VLO	0	0	0	0	0	0	0	1	0	0	1
SLH	0	0	0	0	0	1	0	0	0	0	1
OM	0	0	0	0	0	0	0	0	1	0	1

**Bob Phillips** · 2011-08-26, 04:31 PM

Try this array formula

=COLUMN(L2)-MAX(IF(B2:K2<>0,COLUMN(B2:K2)))-1

**andre** · 2011-08-26, 09:02 PM

Navn	Uke 1	Uke 2	Uke 3	Uke 4	Uke 5	Uke 6	Uke 7	Uke 8	Uke 9	Uke 10	Sum	weeks since
JE	0	0	0	0	1	0	0	0	0	0	1	9
AR	1	0	0	0	0	0	0	0	0	0	1	10
FL	0	0	0	0	0	0	0	0	1	0	1	8
ACG	1	0	0	0	0	0	0	0	0	0	1	10
RS	0	0	1	0	0	0	0	0	1	0	2	9
GH	0	0	0	0	0	0	1	0	0	0	1	9
JK	0	0	0	1	0	0	0	0	0	0	1	9
TVA	0	0	0	0	1	0	0	0	0	0	1	9
VLO	0	0	0	0	0	0	0	1	0	0	1	9
SLH	0	0	0	0	0	1	0	0	0	0	1	9
OM	0	0	0	0	0	0	0	0	1	0	1	9

**Bob Phillips** · 2011-08-27, 01:00 AM

I get 4,9,0,9,1,3,6,5,2,4,1

**andre** · 2011-08-27, 08:04 AM

strange. i still get the same wired numbers.....can it be that i run on exel 2007, i have changed from comma to semi colon in order to make it work, but is there more differens then that?

**Bob Phillips** · 2011-08-27, 09:36 AM

No, I use 2007 too on this machine. Semi-colon is just because you have a continental version of excel, so that should not matter. Can you post the workbook with your formula in it?