How do you find the difference between one cell in a column and the last non-empty cell in the same column in Google Sheets?

Question

I am very inexperienced with spreadsheets and can only do very basic functions and formulas. My friends and I are tracking our weight loss over the next couple of months, so we're adding our weighs daily. I'm looking for a formula where I can find the difference between our starting weights (mine begins in G8) and the most recently entered weight (my last entry will be in G67). To clarify, I would like to keep track of my progress as I go, not simply the difference between the first and final entries.

I've researched this question quite a bit, but seem to only be able to find answers for Microsoft Excel (which didn't work for Google Sheets).

I've included a link to a test spreadsheet, so you can see what ours looks like: https://docs.google.com/spreadsheets/d/1RRMsauNAnC8PVjP5ieuHQUDawN33ybzSioajfQxwf_w/edit#gid=0

Just to clarify; you're looking for a formula that will result in your current total loss based on your most recently entered weight, and not just your final total once you get to G67? If that's the case, the first Google result from my search gave me =G8-index(G:G,max(row(G:G)*(G:G<>""))) — Das_Geek
That is correct, yes. Thanks for the clarification. Unfortunately, I found a similar formula, but when I try it, it gives me a #REF! error. I'm not sure why. Your formula came up with the same error. — Krysten
It works in the example Sheet I made to test the formula. Without seeing your sheet, I don't think I'll be able to help much firther — Das_Geek
Here's a link to a test spreadsheet I made, so you can see what ours looks like. docs.google.com/spreadsheets/d/… — Krysten
Ah, I see the issue. It's because the "Total" cell is in the same column as the data. Since the formula indexes the entire column, it's indexing itself, as well. That's probably not good. To fix that, replace the formula with =G8-index(G8:G67,max(row(G8:G67)*(G8:G67<>""))), replacing the numbers as necessary to extend the number of cells the formula references — Das_Geek

user11982798 user11982798 · Accepted Answer · 2020-01-10T00:45:19

You can do like this:

=arrayformula(
 {"Begin", B8:H8;
  "Last", {
  indirect("B" & max(filter(row(B8:B67),B8:B67<>""))),
  indirect("C" & max(filter(row(C8:C67),C8:C67<>""))),
  indirect("D" & max(filter(row(D8:D67),D8:D67<>""))),
  indirect("E" & max(filter(row(E8:E67),E8:E67<>""))),
  indirect("F" & max(filter(row(F8:F67),F8:F67<>""))),
  indirect("G" & max(filter(row(G8:G67),G8:G67<>""))),
  indirect("H" & max(filter(row(H8:H67),H8:H67<>"")))}
  ;"Different",{B8:H8}-{
  indirect("B" & max(filter(row(B8:B67),B8:B67<>""))),
  indirect("C" & max(filter(row(C8:C67),C8:C67<>""))),
  indirect("D" & max(filter(row(D8:D67),D8:D67<>""))),
  indirect("E" & max(filter(row(E8:E67),E8:E67<>""))),
  indirect("F" & max(filter(row(F8:F67),F8:F67<>""))),
  indirect("G" & max(filter(row(G8:G67),G8:G67<>""))),
  indirect("H" & max(filter(row(H8:H67),H8:H67<>"")))}})

And you must continue to additional columns after column H (include change B8:H8 to B8:~8), and if any additional row you must change number row 67 to next future Last Row number of your data

or

=arrayformula({
               {"Stage";"First";"Last";"Diff"},
               {B7:H7;
                B8:H8;transpose(
                  query(split(
                    query(transpose(
                      query(text(row(A8:A66)*not(isblank(B8:H66)),"0000") & ":" & B8:H66,
                        "Select " & join(",","max(Col" & column(B7:H7)-COLUMN(B7)+1 & ")"))
                  ),"Select Col2"),":",true,false),"Select Col2"));
                B8:H8-transpose(
                  query(split(
                    query(transpose(
                      query(text(row(A8:A66)*not(isblank(B8:H66)),"0000") & ":" & B8:H66,
                         "Select " & join(",","max(Col" & column(B7:H7)-COLUMN(B7)+1 & ")"))
                  ),"Select Col2"),":",true,false),"Select Col2"))
               }})

How do you find the difference between one cell in a column and the last non-empty cell in the same column in Google Sheets?

1 Answers