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
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.
=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.