Yogi Anand, D.Eng, P.E. Google Spreadsheet www.energyefficientbuild.com
SuperUser said:
Add top and bottom of a column (not whole column)
I have several spreadsheets with different length columns of numbers. I want to subtract the top number (always in row 2) from the last number in the column, which could be in any row. Ex: If the list goes from F2 through F10, and is empty, I want =F10-F2. If the list goes from G2 through G14, and G15 is empty, I want =G14-G2).
What I really need is a way to identify the bottom filled cell (the next cell will always be empty). Anyone know how to do that?
-----------------------------------------------
SuperUser said:
ReplyDeleteI was able to make that formula work only by adding a blank line between the summary row and the start of the data.
In other words, that formula is in cell G5, cell G6 is blank, cell G7 starts the data. There are no blanks in the data until the cell after the last entry.
I don't understand why you have to use a blank line between the summary row and the start of the data. For me the following work fine ...
=index(G6:G,count(G6:G))-G6
or
if there could be blanks in the range of interest
=index(G:G,max(if(len(G6:G),row(G6:G))))-G6
as illustrated in sheet named BasedOnSuperUserComment in my Blog post
Cheers!
Yogi