count weeks since last wash

andre

New member
Joined
Aug 21, 2011
Messages
7
Reaction score
0
Points
0
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
NavnUke 1Uke 2Uke 3Uke 4Uke 5Uke 6Uke 7Uke 8Uke 9Uke 10Sumweeks since
JE01
000100002need forula

AR10000000001
FL00000000011
ACG10000000001
RS00100
000102
GH00000010001
JK00010000001
TVA00001000001
VLO00000001001
SLH00000100001
OM00000000101
 
Try this array formula

=COLUMN(L2)-MAX(IF(B2:K2<>0,COLUMN(B2:K2)))-1
 
nope..... getting some wired numbers

NavnUke 1Uke 2Uke 3Uke 4Uke 5Uke 6Uke 7Uke 8Uke 9Uke 10Sumweeks since
JE000010000019
AR1000000000110
FL000000001018
ACG1000000000110
RS001000001029
GH000000100019
JK000100000019
TVA000010000019
VLO000000010019
SLH000001000019
OM000000001019
 
I get 4,9,0,9,1,3,6,5,2,4,1
 
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?
 
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?
 
woorkbook

this is the file. have tried to change things, but nothing seem to work
 

Attachments

  • last wash.xlsx
    10 KB · Views: 11
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).
 
solved

thanks a lot, everything is working smootly.....great forum
 
Back
Top