counting the number of weeks

[Roy Goddard]

I have a simple table of numbers with each row dated by weeks as below

A B C D
E
1 Date Machine 1 Machine 2 Machine 3 Machine
4
2 10/04/03 025 169 589
458
3 10/11/03 159 785 169
482
4 10/18/03 259 156 159
548
5 10/25/03 265 159 487
659
6 11/01/03 120 789 245
169

I need a formula that looks at all the numbers (B2:E6 in the case above)
then returns the number of weeks since it last occurred.
An example would be 4 weeks (rows) since 025 appeared.

Thanks
Roy

 

[Ken Wright]

In B9 put the following formula and then copy and paste it down and across to
E13:-

=MATCH(MAX($A$2:$A$6),$A$2:$A$6,0)-MATCH(SUMPRODUCT(MAX(($A$2:$A$6)*(($B$2:$E$6=
B2)))),$A$2:$A$6,0)