Formula for Column to Keep a Running Balance of Transactions is Producing Additional Decimal Places

Question

*Update: Solved

I've learned that it's not really a problem other than to annoy me. See additional blockquote notes for more info. If it still bugs you, I posted a solution.

Original Question:

I have a checking account register sheet to track transactions and to keep a running balance. For example...

Check #	Date	Desc.	Amount	Cleared	Balance
	12/01	Start	$50.00	Yes	$50.00
	12/02	Dep.	$10.00	Yes	$60.00
123	12/03	Store	-$10.54	Yes	$49.46

The 'Balance' column is to add or subtract the amount from the previous transaction above, so that every new transaction shows the current balance at the time for that line. However, I've tried two formulas and they both result in many of the results having extra decimal places. They display correctly since I have the column formatted as currency, but the data stored often has extra decimal places.

For example: $49.46 will display correctly when formatted as currency, but the actual data produced behind that inside the cell is often something like 49.455 (.xxx.) or 49.45500000001 (.xxx0000000x) or 49.455999999999 (.xxx999999999).

I found three 'Amount' entries that that had typos with three decimal places (.xxx). Fixing those fixed the 'Balance' entries that had three decimal places (.xxx). After fixing the typos, (.xxx0000000x) went to (.xx0000000000x), and (.xxx999999999) went to (.xx9999999999x). But those long (.xx0000000000x) and (.xx9999999999x) entries are still there.

I've done some searching for what might cause this and found that it might be due to Floating Point errors. I only skimmed it but kind of get what that is now.

I've since learned this is normal and is indeed a result of Floating Point (FP) arithmetic. More below.

Both formulas I've tried...

={"Balance";
 ARRAYFORMULA(
  IF( ISBLANK(C2:C)*ISBLANK(D2:D),,
   SCAN( 0,D2:D,
    LAMBDA(
     runningTotal,
     currentAmount,
     runningTotal+currentAmount
    )
   )
  )
 )
}

={"Balance";
 ARRAYFORMULA(
  IF( ISBLANK(C2:C)*ISBLANK(D2:D),,
   SUMIF(
    ROW(D2:D),
    "<="&ROW(D2:D),
    D2:D
   )
  )
 )
}

I have three questions regarding this...

1. Will this actually affect the accuracy of the math down to the absolute penny over the course of thousands of transactions? (I have close to 9,000.) *Solved
2. If so, what is the best way to fix this? *Solved
3. Even if it remains 100% accurate, is there a simple fix so that it doesn't irritate me so much? Haha *See solution posted as an answer

Thank you in advance!

Here is a test sheet that demonstrates the issue... https://docs.google.com/spreadsheets/d/1YCUReqisi92prqhdb0aRgRaZmoaileqhdtHXYB0UVTY/edit?usp=sharing

The problematic cells are highlighted in orange. They display correctly on the surface because of the currency formatting, but when you click on the cell, they have extra decimal points within the data of the cell.

As was pointed out in the comments below, Floating Point (FP) arithmetic or operations (in cases like this, sometimes called Floating Point errors because they appear to be errors) are not actually errors at all.

The longer decimal places, (.xx0000000000x) and (.xx9999999999x), are just a part of how FP does its thing. I've learned that it's benign and does not affect the actual final math.

For more info, here's the link that was shared: Is floating point math broken?

If it still irritates you as much as it did me, my semi-cosmetic fix (posted as an answer below) will do the trick. Thanks everyone!

Answer 1

As was pointed out in the comments to the original question, Floating Point (FP) arithmetic or operations (in cases like this, sometimes called Floating Point errors because they appear to be errors) are not actually errors at all.

The longer decimal places, (.xx0000000000x) and (.xx9999999999x), are just a part of how FP does its thing. I've learned that it's benign and does not affect the actual final math.

For more info, here's the link that was shared: Is floating point math broken?

If it still irritates you as much as it did me, my semi-cosmetic fix (posted here) will do the trick. It makes it so that the data produced in the actual cell (behind the two-decimal-place 'displayed' result) is now also at two-decimal-places.

All I did was incorporate the ROUND function into each formula. I had listed two formulas that I attempted to use in the original question. Here are both of those options with the ROUND function added. (The LAMBDA version seems to work more efficiently with less load on the sheet.)

={"Balance";
 ARRAYFORMULA(
  IF( ISBLANK(C2:C)*ISBLANK(D2:D),,
   SCAN( 0,D2:D,
    LAMBDA(
     runningTotal,
     currentAmount,
     ROUND(runningTotal+currentAmount,2)
    )
   )
  )
 )
}

={"Balance";
 ARRAYFORMULA(
  IF( ISBLANK(C2:C)*ISBLANK(D2:D),,
   ROUND(
    SUMIF(
     ROW(D2:D),
     "<="&ROW(D2:D),
     D2:D
    ),2
   )
  )
 )
}