MrExcel Publishing
Your One Stop for Excel Tips & Solutions


Posted by BJF on January 19, 2002 8:28 PM

The concept is simple but to do it on a spread sheet eludes me. I have in column A a list of dates starting with 1/12/02. I have in column B a list of integer values. The dates always have a corresponding integer. The integer is always increasing in each succeeding row (Like the ever increasing miles on a car). As the columns grow I would like the most recent date (last row written) subtracted from the first date (lets say, written in cell A3) and placed in a 'time elapsed' cell (lets say A20). At the same time I would like to have the most recent integer value subtracted from the first value and placed in a 'total' cell (lets say B20). Lastly, the easy part, I will divide the integer by the time elapsed and put the result in a seperate cell (C20). The values must change automatically as new dates and integers are added.
Can anyone show me how a formula would look that would perform this dynamic calculation?

Posted by Tom Dickinson on January 19, 2002 10:01 PM

In A20 put


Similarly for B20. You may have to format A20 as generic.

In C20 put


This assumes that the latest entry will always be the latest date, and the latest value will always be the highest.