Forum Discussion

mdemitchell's avatar
mdemitchell
Copper Contributor
Oct 29, 2021
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...
excel
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Nov 01, 2021

mdemitchell 

Since 365 was explicitly mentioned, I chose the XLOOKUP to return the rightmost value and subtracted the budget for the row.

= XLOOKUP(TRUE,ISNUMBER(currentIncomeRow),currentIncomeRow,,,-1) - currentBudget

This is fine for a single row, but if the objective is to return an array of values for multiple jobs, XLOOKUP will error.  In that case, turning to the Insider beta version of 365, one can use Lambda functions and the BYROW higher order helper function.  That requires a more serious programming approach 

= LET(
    finalValueλ, LAMBDA(Inc,--XLOOKUP(TRUE,ISNUMBER(Inc),Inc,,,-1)),
    latestValues, BYROW(JobIncome, finalValueλ),
    latestValues - Budget
  )

As an array formula, one could calculate the MTD Variance over the entire portfolio by changing the final line to

       SUM(latestValues - Budget) )

Resources