Forum Discussion
Solved
Excel 365 formula to calculate MTD actual vs MTD Budget
I'm working with a YTD P&L and need to calculate a % variance between the latest month numbers and the monthly budget. How can I get the calculation cell to read the latest month entered? In the exa...
Like I did in the attached version of your file, enter this formula in W5:
=LOOKUP(9.9E+307,J5:U5)-V5
It would be a lot easier (A LOT EASIER!) to help you if you posted a sample of an actual spreadsheet showing a more complete set of data. Here, what you've shown, with all its blank rows between the month heading and the numbers on the bottom row, you're prompting more questions--at least in my mind-- as to what I'm looking at.
Presumably you have an actual spreadsheet. Please post either a copy of the real thing or a mockup that reflects the whole of the reality, just without any confidential info.
Presumably you have an actual spreadsheet. Please post either a copy of the real thing or a mockup that reflects the whole of the reality, just without any confidential info.
- I added a test sheet. Hopefully this will work. I appreciate any help you can provide.
- Please try this. I am sure that there is a more elegant way to do this. but this seems to work.
=V5-INDEX($J$5:$U$5,MAX(IF($J$5:$U$5>0,COLUMN($J$5:$U$5)-COLUMN($I$5),"")))
v5 is the budget cell
J5 to U5 are the 12months' actuals
I is the column prior to the months data
Best wishes.
