Identifying Bottom-most Entry (And Other Questions)
 

I want to perform a date calculation. For example:

a
1 date
2 2004-01-04
3 2004-01-11
4 2004-01-18

.....and so on. I'll continually add things to this column and the goal
of the calculation will be to determine how many days span between the
item in a2 (the first date) and the last date. The trick is "how do I
tell Excel that what should be subtracted is the bottom-most item?
Today it'll be a3 but next week it'll be a4, then a5, etc.

Help?

Of course, let me know if this isn't clear.

Also, is there a way in Excel to add descriptors without actually
having them be part of the calculation? For example, I have "date" in
cell a1 in the above example. That doesn't seem to be affecting
calculations such as =sum(a:a) but it still looks messy. Ideas?

Third, is there a way to hide the messy #DIV/0! errors? I've done
fill-downs for things that don't yet exist and so they're there.

Finally, is there a way to say, in Excel, "fill down from here?"
Because of the above, ugly text descriptors, I find myself having to
do things manually like "=sum(d1:d1000)", which seems kind of odd.

Thanks in advance for all the very-likely newbie questions,
Jason

Identifying Bottom-most Entry (And Other Questions)
 

something like this should do it

=MAX(G3:G100)-G2
--
Don Guillett
SalesAid Software

"Jason" wrote in message
om...
I want to perform a date calculation. For example:

a
1 date
2 2004-01-04
3 2004-01-11
4 2004-01-18

....and so on. I'll continually add things to this column and the goal
of the calculation will be to determine how many days span between the
item in a2 (the first date) and the last date. The trick is "how do I
tell Excel that what should be subtracted is the bottom-most item?
Today it'll be a3 but next week it'll be a4, then a5, etc.

Help?

Of course, let me know if this isn't clear.

Also, is there a way in Excel to add descriptors without actually
having them be part of the calculation? For example, I have "date" in
cell a1 in the above example. That doesn't seem to be affecting
calculations such as =sum(a:a) but it still looks messy. Ideas?

Third, is there a way to hide the messy #DIV/0! errors? I've done
fill-downs for things that don't yet exist and so they're there.

Finally, is there a way to say, in Excel, "fill down from here?"
Because of the above, ugly text descriptors, I find myself having to
do things manually like "=sum(d1:d1000)", which seems kind of odd.

Thanks in advance for all the very-likely newbie questions,
Jason

Identifying Bottom-most Entry (And Other Questions)
 

One way:

=INDEX(A:A,COUNTA(A:A))-A2

In article ,
(Jason) wrote:

I want to perform a date calculation. For example:

a
1 date
2 2004-01-04
3 2004-01-11
4 2004-01-18

....and so on. I'll continually add things to this column and the goal
of the calculation will be to determine how many days span between the
item in a2 (the first date) and the last date. The trick is "how do I
tell Excel that what should be subtracted is the bottom-most item?
Today it'll be a3 but next week it'll be a4, then a5, etc.

Help?

Identifying Bottom-most Entry (And Other Questions)
 

That doesn't seem to be *quite* working.

In the instance where I have:

a
1 w/e
2 1/4/2002
3 1/11/2004

....and you'd expect 7 days or 1 week to be calculated, using this
equation:

=INDEX(A:A,COUNTA(A:A))-A2

....results in:

1/7/1900 0:00

....which kinda sorta seems to work if you do the math in your head and
say 1/7/1900 is precisely seven days after 1900, but I'm looking for
either "1" (for weeks or percentages of a week) or "7" (for days).

Ideas?

And thanks for the help so far. =)

Regards,
Jason

"J.E. McGimpsey" wrote in news:jemcgimpsey-
:

One way:

=INDEX(A:A,COUNTA(A:A))-A2

In article ,
(Jason) wrote:

I want to perform a date calculation. For example:

a
1 date
2 2004-01-04
3 2004-01-11
4 2004-01-18

....and so on. I'll continually add things to this column and the goal
of the calculation will be to determine how many days span between the
item in a2 (the first date) and the last date. The trick is "how do I
tell Excel that what should be subtracted is the bottom-most item?
Today it'll be a3 but next week it'll be a4, then a5, etc.

Help?

Identifying Bottom-most Entry (And Other Questions)
 

No, that appears to be giving me a number of hashes (#'s). No matter how
wide I open the field, I still don't get a number.

Thanks,
Jason

"Don Guillett" wrote in news:u7hV0881DHA.2324
@TK2MSFTNGP09.phx.gbl:

=MAX(G3:G100)-G2

Identifying Bottom-most Entry (And Other Questions)
 

Jut format the results cell as a number, it defaults to a date, and you will
see 7.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Jason" wrote in message
...
That doesn't seem to be *quite* working.

In the instance where I have:

a
1 w/e
2 1/4/2002
3 1/11/2004

...and you'd expect 7 days or 1 week to be calculated, using this
equation:

=INDEX(A:A,COUNTA(A:A))-A2

...results in:

1/7/1900 0:00

...which kinda sorta seems to work if you do the math in your head and
say 1/7/1900 is precisely seven days after 1900, but I'm looking for
either "1" (for weeks or percentages of a week) or "7" (for days).

Ideas?

And thanks for the help so far. =)

Regards,
Jason

"J.E. McGimpsey" wrote in news:jemcgimpsey-
:

One way:

=INDEX(A:A,COUNTA(A:A))-A2

In article ,
(Jason) wrote:

I want to perform a date calculation. For example:

a
1 date
2 2004-01-04
3 2004-01-11
4 2004-01-18

....and so on. I'll continually add things to this column and the goal
of the calculation will be to determine how many days span between the
item in a2 (the first date) and the last date. The trick is "how do I
tell Excel that what should be subtracted is the bottom-most item?
Today it'll be a3 but next week it'll be a4, then a5, etc.

Help?

Identifying Bottom-most Entry (And Other Questions)
 

Ideas?

Format the result cell as General or another non-date format.

In article ,
Jason wrote:

That doesn't seem to be *quite* working.

In the instance where I have:

a
1 w/e
2 1/4/2002
3 1/11/2004

...and you'd expect 7 days or 1 week to be calculated, using this
equation:

=INDEX(A:A,COUNTA(A:A))-A2

...results in:

1/7/1900 0:00

...which kinda sorta seems to work if you do the math in your head and
say 1/7/1900 is precisely seven days after 1900, but I'm looking for
either "1" (for weeks or percentages of a week) or "7" (for days).

Ideas?

And thanks for the help so far. =)

Identifying Bottom-most Entry (And Other Questions)
 

Oh, excellent! Thanks! ...

Any thoughts on the rest of my post? Specifically, the 'fill down from
here' question.

Thanks,
Jason

"Bob Phillips" wrote in
:

Jut format the results cell as a number, it defaults to a date, and
you will see 7.