Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 03-08-2021, 07:15 PM
oscarlimerick oscarlimerick is offline Whats the correct formula to point to the last populated cell in a column? Windows 8 Whats the correct formula to point to the last populated cell in a column? Office 2013
Advanced Beginner
Whats the correct formula to point to the last populated cell in a column?
 
Join Date: Jul 2020
Posts: 57
oscarlimerick is on a distinguished road
Default Whats the correct formula to point to the last populated cell in a column?

Hi folks. The excel attachment shows a chart containing dates beside a number reported for that day. I want to be able to take the current day (March 8 in this example) and have it report the difference from the previous day (March 7th in this example). The difference is to be reported in cell K1. In this example, the cell K1 contains “=D14-D13” and correctly displays 7301, which is the difference between Mar 8 and Mar 7 # reported today.

But tomorrow, when I have a new value for cell Mar 9 in cell D15, I want cell K1 to then show the difference between Mar 9 and Mar 8. I need to find the correct formula to put in K1 that will point to the bottom most cell in column D and subtract the second last value of column D, and do this every day when there is a new entry in column D.

I realize I could just create a subtraction formula in cell E5 saying “=D5-D4” and then drop that cell box down every day so it would display the differences in column E, but I don’t want to do that every day. I just want cell K1 to constantly display the difference between the bottom most populated cell in column D (which would be the current date) and the second last bottom most populated cell in column D.(which would be yesterdays date) Is this possible? If so how can I do this? Many thanks
Attached Files
File Type: xlsx sample for micosoft forum.xlsx (14.0 KB, 6 views)
Reply With Quote
  #2  
Old 03-08-2021, 10:32 PM
Purfleet Purfleet is offline Whats the correct formula to point to the last populated cell in a column? Windows 10 Whats the correct formula to point to the last populated cell in a column? Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

How about an index & match
Code:
=INDEX(D:D,MATCH(TODAY(),A:A,0))-INDEX(D:D,MATCH(TODAY(),A:A,0)-1)
Attached Files
File Type: xlsx Copy of sample for micosoft forum_purfleet.xlsx (14.9 KB, 4 views)
Reply With Quote
  #3  
Old 03-08-2021, 11:09 PM
ArviLaanemets ArviLaanemets is offline Whats the correct formula to point to the last populated cell in a column? Windows 8 Whats the correct formula to point to the last populated cell in a column? Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

Code:
=SUMIFS($D$4:$D$37,$A$4:$A$37,TODAY())-SUMIFS($D$4:$D$37,$A$4:$A$37,TODAY()-1)
Or, when adding a column header for dates (e.g. renaming header Date to Day, and naming leftmost column with dates as Date), and getting rid of special characters in column names (e.g. renaming # REPORTED TODAY AS Reported), you define your table as Table (Insert>Table) and giving it a name e.g. YourTable:
Code:
=SUMIFS(YourTable[Reported],YourTable[Date],TODAY())-SUMIFS(YourTable[Reported],YourTable[Date],TODAY()-1)
Reply With Quote
  #4  
Old 03-09-2021, 05:36 AM
oscarlimerick oscarlimerick is offline Whats the correct formula to point to the last populated cell in a column? Windows 8 Whats the correct formula to point to the last populated cell in a column? Office 2013
Advanced Beginner
Whats the correct formula to point to the last populated cell in a column?
 
Join Date: Jul 2020
Posts: 57
oscarlimerick is on a distinguished road
Default

I never tested the second one because I don't understand it, but it probably works. I think I will go with Purfleet's solution because I understand now what it is doing. I was unaware of the today function. Thanks to all for the help.
Reply With Quote
  #5  
Old 03-09-2021, 08:00 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Whats the correct formula to point to the last populated cell in a column? Windows 7 64bit Whats the correct formula to point to the last populated cell in a column? Office 2010
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

Take some time to delve into Excel Tables. It makes life much easier
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
IF cell in Col A is populated BUT cell in Col C is blank DELETE ROW ChrisOK Excel Programming 7 05-05-2019 09:00 PM
Filling a formula down a column where only one cell value referenced changes? sakurasanta86 Excel 1 08-27-2018 03:23 AM
formula help - value in cell looking up row and column using great than less than stefano Excel 3 10-20-2017 02:20 PM
Whats the correct formula to point to the last populated cell in a column? Entering a column value in a cell, to be used as part of a reference in a formula in another cell paulkaye Excel 4 02-26-2017 04:18 AM
Whats the correct formula to point to the last populated cell in a column? Creating formula based on if data is correct in cell MattMurdock Excel 1 08-06-2012 03:11 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 10:14 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft