Subtracting cells that aren't next to each other.....

Posted by Sean Sutherland on December 01, 2000 12:47 AM

Here goes...Column A that lists each day of the year. Column B has committed amounts. Column C is the difference between the figures in Column B. Here is the catch. Since there are weekends and holidays in the year, there are dates that there is no data in Column B (actually 0's) and the number of cells (in a row) vary. For example, a regular weekend would have have two 0's in Column B. A long weekend would have three 0's in Column B and a week would have seven 0's in a row. How can I find the difference between the present day's committed and the previous committed (ie subtract Mondays committed from Friday's committed). I am stumped and would greatly appreciate any help! Thanks.

Posted by Tim Francis-Wright on December 01, 2000 1:01 PM

I'll assume that each column starts in row 2.
Cell C2 is 0, I suppose. Cell C3 is:
=IF(B3>0,B3-VLOOKUP(MAX(IF($B$2:$B2>0,$A$2:$A2)),$A$2:$B2,2),0)
This is an array formula, so enter it with
control-shift-enter.
You can then copy it down to all of the pertinent
cells in Column C.

This will generate an error if B2 is zero (as
it will often be, because January 1st is a holiday)
for the first workday of the year. But it will
work after that.

HTH

Posted by Sean Sutherland on December 03, 2000 3:51 PM

THANKS!!!

Tim,
Your formula worked great! Thanks for the help!!!
Sean