MrExcel Publishing
Your One Stop for Excel Tips & Solutions

DYNAMIC SUBTRACTION - DATES & ABSOLUTE VALUES


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?
Thanks,
bjf


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

In A20 put

=Max(A4:A19)-A3

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

In C20 put

=B20/A20

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