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 example, the variance would be based on Sept.  Next month, when I enter Oct numbers, I want it to switch from reading Sept actual to Oct actual and offer a new variance with Budget. 

 

 

    MTDMTD Var
Sept 2021Oct 2021Nov 2021Dec 2021BudgetBdg - Act
      
      
259,840.22   262,000.00?????
excel

7 Replies

  • 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) )
    • mdemitchell's avatar
      mdemitchell
      Copper Contributor
      Nov 01, 2021

      Twifoo 

       

      This works very well!  Thank you.  If possible, can you run through the formula explaining each segment? For example, what does "9.9E+307" mean?

      • Twifoo's avatar
        Twifoo
        Silver Contributor
        Nov 01, 2021
        9.9E+307 is the highest possible number in Excel. Because it cannot be found in the lookup_vector argument, LOOKUP returns the last number.
  • mathetes's avatar
    mathetes
    Silver Contributor
    Oct 29, 2021
    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.
    • mdemitchell's avatar
      mdemitchell
      Copper Contributor
      Oct 29, 2021
      I added a test sheet. Hopefully this will work. I appreciate any help you can provide.
      • Dinesh_Natarajan_Mohan's avatar
        Dinesh_Natarajan_Mohan
        Copper Contributor
        Oct 29, 2021
        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.

Resources