Identifying Bottom-most Entry (And Other Questions)

  • Thread starter Thread starter Jason
  • Start date Start date
J

Jason

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
 
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
 
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
 
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)
 
Oh, excellent! Thanks! ...

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

Thanks,
Jason
 
Back
Top