Help Me Excel Nerds - Interactive LTM Column
The columns in my model (assuming 1Q24 is in cell B1) are 1Q24, 2Q24, 3Q24, 4Q24, FY24, 1Q25, 2Q25. Assume below each is the date (i.e., in cell B2 it says 3/31/2024). The data in the below rows are items like Revenue and COGS, which would be in cells B3 and B4, respectively. I have an LTM column with the corresponding date, say it currently says 3/31/2025, but earnings come out this week and I will change it to 6/30/2025. I will input 2Q numbers in the corresponding row of the 2Q25 column, but I want the LTM column to automatically register the prior four quarterly periods, while skipping the FY (fiscal year) figure, to return the LTM (last twelve months) data. Anyone know how to do this? I have been trying a bunch with ChatGPT and CoPilot, but keep getting dud formulas.
Would j use a sumifs, criteria 1 is dates and criteria 2 is if Quarter or FY, should be easy enough to implement, might j have to add a row or 2
Couple options
1. Set a driver date (cell where you manually enter 3/31/25, 6/30/25, etc. Then write a sumifs where the criteria is in the last 12 months
2. Set up your file in a way so that you are summing monthly/quarterly figures and have a date structure set-up. In this example I have my driver date set to 6/30/25 and for my TTM period I am summing everything that is in the last 12 months of 6/30/25
.
Incidunt ullam quia ipsa itaque. Nisi quibusdam placeat in. Atque est voluptatem amet reprehenderit quasi. Voluptatum sapiente et voluptatibus sit.
Aspernatur voluptatem quas rem adipisci ut ducimus. Voluptas ex saepe et facilis cupiditate ipsum fugiat. Sequi in enim fugiat dolorum qui nam perspiciatis eius. Excepturi ut veritatis nobis perspiciatis et ipsa accusamus.
Rerum culpa non odio pariatur aspernatur. Rerum aut neque nemo sequi harum repellat quibusdam iste. Ut quas ea expedita sed qui quo sed. Fugit odio dolores temporibus. Veniam velit et animi quae.
See All Comments - 100% Free
WSO depends on everyone being able to pitch in when they know something. Unlock with your email and get bonus: 6 financial modeling lessons free ($199 value)
or Unlock with your social account...