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?

Try this:

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

--
Biff
Microsoft Excel MVP


"Pablo" wrote in message
...
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?

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


On 18 Sty, 05:17, Pablo wrote:
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?

Biff,

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

Paul

"T. Valko" wrote:

Try this:

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

--
Biff
Microsoft Excel MVP


"Pablo" wrote in message
...
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?

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.


--
Biff
Microsoft Excel MVP


"Pablo" wrote in message
...
Biff,

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

Paul

"T. Valko" wrote:

Try this:

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

--
Biff
Microsoft Excel MVP


"Pablo" wrote in message
...
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?