Whats the correct formula to point to the last populated cell in a column?

oscarlimerick · #1 03-08-2021, 07:15 PM

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

Purfleet · #2 03-08-2021, 10:32 PM

How about an index & match

Code:

=INDEX(D:D,MATCH(TODAY(),A:A,0))-INDEX(D:D,MATCH(TODAY(),A:A,0)-1)

ArviLaanemets · #3 03-08-2021, 11:09 PM

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)

oscarlimerick · #4 03-09-2021, 05:36 AM

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.

Pecoflyer · #5 03-09-2021, 08:00 AM

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

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
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
Creating formula based on if data is correct in cell	MattMurdock	Excel	1	08-06-2012 03:11 AM