Forum Discussion

Rod_RolstonHotRod-202's avatar
Rod_RolstonHotRod-202
Copper Contributor
Sep 03, 2023

New Excel problem

I recently purchased Office H&S 2021for Mac. I'm not able to get running summaries like i used to. I use this spreadsheet like a check register with the e column being debit or checks and the f column being credits or deposits and the g column as summary. I take for example G3371-e3372+f3372=G3372 ands on. Previous spreadsheets worked now I get #VALUE as answer. I have Mac & latest OS.

I made sure the entires are numbers or currency.  Tried both.  What's wrong.  Used to work fine like this for years.

 

excel

3 Replies

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Sep 04, 2023

    Rod_RolstonHotRod-202 

    Newer versions of Excel are backward compatible so old formulas should still work.  

    If your version of Excel supports dynamic arrays but lacks Lambda then you could use SUMIFS

    = SUMIFS(credits, date, "<="&date) -
  SUMIFS(debits,  date, "<="&date)

    to obtain a running total.  With the latest versions of Excel it would be

    = SCAN(0, credits-debits, LAMBDA(x,y,x+y))

    Either of these would write the entire column of balances calculated as a single formula.

    • Rod_RolstonHotRod-202's avatar
      Rod_RolstonHotRod-202
      Copper Contributor
      Sep 09, 2023

      It turns out for whatever reason, me pasting an entry from numbers into Excel was the problem.

  • NikolinoDE's avatar
    NikolinoDE
    Platinum Contributor
    Sep 04, 2023

    Rod_RolstonHotRod-202 

    It sounds like you are trying to create a running balance in Excel for Mac, but you are encountering a #VALUE! error. This issue can sometimes occur due to the order of operations or data types in your formulas. To create a running balance in Excel, you can use a formula in the "G" column to sum up the previous balance with the current debit and credit transactions.

    Here is a step-by-step guide to setting up a running balance:

    Assuming your data is set up like this:

    • Debits (money going out) are in column "E."
    • Credits (money coming in) are in column "F."
    • The running balance will be in column "G."
    1. In cell G2 (or the cell where you want to start the running balance), enter the initial balance if any (e.g., 0 if you're starting from scratch).
    2. In cell G3, enter the following formula:

    =G2+E3-F3

    This formula takes the previous running balance (in G2), adds the current debit (in E3), and subtracts the current credit (in F3).

    1. Copy cell G3 and paste it down the G column to calculate the running balance for subsequent rows.

    Make sure that you do not have any non-numeric values or errors in columns E and F, as these can cause the #VALUE! error in your formula. Additionally, verify that the data types are consistent (numbers or currency) throughout columns E and F.

    If you still encounter issues, it might be helpful to check for any accidental changes in Excel settings or updates that could have affected your spreadsheet's functionality.

    The text and steps were created with the help of AI.

     

    My answers are voluntary and without guarantee!

     

    Hope this will help you

    Was the answer useful? Mark them as helpful!

    This will help all forum participants.

Resources