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

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 withcontrol-shift-enter.You can then copy it down to all of the pertinentcells in Column C.

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

(6 responses) I'm part of an organisation that is altering the way it manages a waiting list, and we have decided we want a way of picking n people per ye

Let's Connect

MrExcel.com debuted on November 21, 1998.

MrExcel.com provides examples of Formulas, Functions and Visual Basic procedures
for illustration only, without warranty either expressed or implied, including
but not limited to the implied warranties of merchantability and/or fitness for
a particular purpose. The Formulas, Functions and Visual Basic procedures on this
web site are provided "as is" and we do not guarantee that they can be used in all
situations.
This site contains affiliate links. Any affiliate commissions that we
earn when you click a link to Amazon or other sites is reinvested in keeping MrExcel.com
running. You can earn a commission for sales leads that you send to us by joining our
affiliate program.
View my Privacy Policy and Terms of Use.