Find Last Filled Cell in Column

User avatar
BobH
UraniumLounger
Posts: 9211
Joined: 13 Feb 2010, 01:27
Location: Deep in the Heart of Texas

Find Last Filled Cell in Column

Post by BobH »

The IF statement can test for blanks which will allow me to be sure I have a value before subtracting 2 cells' values. This requires that I put the test on each row. I'd like to be able to have a formula that will execute the subtraction only when the last cell in the column has a value.

For example, I have rows in a spreadsheet that contain daily electricity consumption. I'd like to subtract the first or starting value from the last consumption value and show the result in a single cell, J40 in the attached. This is a 'proof' cell which will show if I have any errors in the summed value or corrupted the range containing daily usage of KWH.
November '21 Usage.xlsx
:cheers: :chocciebar: :thankyou:
You do not have the required permissions to view the files attached to this post.
Bob's yer Uncle
(1/2)(1+√5)
Intel Core i5, 3570K, 3.40 GHz, 16 GB RAM, ECS Z77 H2-A3 Mobo, Windows 10 >HPE 64-bit, MS Office 2016

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Find Last Filled Cell in Column

Post by HansV »

You can use

=LOOKUP(9.99999999999999E+307,G6:G37)-F6
Best wishes,
Hans

User avatar
BobH
UraniumLounger
Posts: 9211
Joined: 13 Feb 2010, 01:27
Location: Deep in the Heart of Texas

Re: Find Last Filled Cell in Column

Post by BobH »

Thank you, Mr. V!!! :chocciebar: :wine:
Bob's yer Uncle
(1/2)(1+√5)
Intel Core i5, 3570K, 3.40 GHz, 16 GB RAM, ECS Z77 H2-A3 Mobo, Windows 10 >HPE 64-bit, MS Office 2016