PDA

View Full Version : 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?

andre
2011-08-27, 11:37 AM
this is the file. have tried to change things, but nothing seem to work

Bob Phillips
2011-08-27, 03:23 PM
You replaced the colon (:) between B2 and K2 with a comma (,), you should not have done that. Change it back, and ARRAY enter it (Ctrl-Shift-Enter).

andre
2011-08-27, 06:31 PM
thanks a lot, everything is working smootly.....great forum