Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
Identifying Bottom-most Entry (And Other Questions)
|
#3
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
Identifying Bottom-most Entry (And Other Questions)
|
#5
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
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? |
#7
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
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. =) |
#8
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy last entry of column to bottom of column | Excel Worksheet Functions | |||
Answers to questions posing more questions in a workbook | Excel Worksheet Functions | |||
View Questions and Answer to questions I created | Excel Discussion (Misc queries) | |||
dislike jump bottom of column by double-clicking the bottom of cel | Excel Discussion (Misc queries) | |||
Identifying bottom of list | Excel Worksheet Functions |