Hello,
I am using =LOOKUP(2,1/(V2:AI2<>""),V2:AI2) to return the last (ie furthest to the right) non blank entry from Row V2:AI2. This works well.
But...
I would like to subtract the entry that is one column to the left from this last entry, only if it is non-blank, and then fill down.
If necessary I would make a new column with the one column to the left entries (a formula for this anybody?), and then a new column to subtract, but being an Excel efficiency pedant, I wonder if there is one formula that will do the whole lot, and then just fill down.
eg First row: 2, 4, blank, 3, 3.5, blank, blank, blank would return 0.5 (i.e. 3.5 - 3)
after fill down...
fifteenth row: 0, blank, 1, 2, 1, 2, 4, blank would return 2 (i.e. 4-2)
twentieth row: 1, 2, 2, 2, 3, 3.5, blank, 4 would return blank or error or similar (i.e. can't subtract blank from 4)
Regards, Bucksy
Bookmarks