How days since last visited

[Pablo]

I am trying to figure out how to determine how days it has been since someone
last visited. I have a weekly attendance sheet that listed a class roster in
column A3:A10. Running across the top D2:I2 are class dates 1/04/09, 1/11/09,
1/18/09, 1/25/09, 2/02/09... When someone attends a 'X' is placed in the cell.

So if someone's last attendance was 1/04/09 the formula would return 13.

Any ideas?

 

[T. Valko]

Try this:

=IF(COUNTIF(D3:I3,"x"),TODAY()-LOOKUP("zzzzz",D3:I3,D2:I2),"no records")

 

[Jarek Kujawa]

mine is a slightly more complicated solution as cd. to that of Biff

=IF(COUNTIF(D3:I3,"x"),TODAY()-OFFSET($C$2,,MAX(IF(D3:I3="x",COLUMN
(D3:I3)-1,""))),"no attendances")

CTRL+SHIFT+ENTER this formula cause this is an array formula

 

[Pablo]

Biff,

Thanks. What does "zzzzz" represent? Also, is there a way to use a HLookup?

Paul

 

[T. Valko]

What does "zzzzz" represent?

zzzzz is the lookup_value. This may be hard to understand....

If the lookup_value is greater than any value in the lookup_vector the
formula will return the result that corresponds to the *last text* value in
the lookup_vector that is *less than* the lookup_value. You're entering an
"x" to denote attendance. "X" is less than "zzzzz" so the formula "finds"
the *last* "x" in the range and uses the corresponding date.

is there a way to use a HLookup?

No. With HLOOKUP, you would lookup a date and return data from the rows
below the date headers. In your application you have to find the last "x"
before you can find the corresponding date.