Excel Formula needed for penny or rounding issue vs standard calculator help

[rlc]

I have church that uses a calculator to spread funds and I have translated these simple formulas into excel. The issue I am having is the calculator is 2 decimal places and the Excel Sheet is 3 decimal places that occurred in a recent scenario.

Calculator:
If you enter into the calculator: $4,315.82-267.98-75.00=$3,972.84 then $3,972.84*.1 = 397.28 then $3,972.84-397.28-397.28=$3,178.28

The total in the calculator would then total: 3,178.27+397.28+397.28+267.98+75.00=4,315.81 (note 267.98 and 75.00 are fixed numbers for this scenario )

Excel

Cell C1 $4,315.82
Cell A4 $267.98
Cell A5 $75.00
Cell A3 =($C$1-$A$4-$A$5)*1 = 397.284
Cell A2 =($C$1-$A$4-$A$5)*1 = 397.284
Cell A1 =C1-A4-A5-A3-A2 =3,178.272

Cell A6 = A1+A2+A3+A4+A5= $4,315.82

The Excel dilemma is it is a penny more and if Excel is solely used for a bank deposit it would be off a penny. Any assistance in finding an advanced function that would resolve this would be welcomed. Thanks in advance

 

[dUBBINS]

You likely have a number on your sheet that has a third decimal. In Excel you may only see two decimals, but if there is a third one entered, Excel will calculate it as such. Formatting is only a visual aid.

Try left clicking the Square in the upper left hand corner (off the sheet) near the A and the 1. Then right click on the sheet anywhere (while it is all highlighted) and format the sheet (temporarily) to be 4 decimals places. Then look around for a number that is longer than dollar and cents allows for. Then repeat the steps to format it back to 2 decimals after you eradicate the 10kth place digits.

 

[dUBBINS]

Well, if your example is the actual formula your problem must be in one of those cells, or whatever cells feed them: C1, A4, or A5

Look for a third decimal in these before trying the steps above.

And if this has happened more than once, the fixed numbers are the likely culprits

 

[mikerickson]

"$3,972.84*.1 = 397.28 " is the where the penny went.

You need to decide who is boss, the calculator or Excel?

If the calculator is boss, you can format the cells to two decimal places and check the Precision As Displayed box in the Calcluation Preferences.

I would suggest that you leave Excel at three decimal places and have any inconstancies be resolved with someone with accounting/calculation experience. These kind of oddities crop up all the time.

 

[rlc]

The scenario presented is the actual Excel results if you were to calculate items in cell A1,A2, &A3 I have displayed the results which occur it goes to 3 decimal places. I am aware I can format this to two decimal, and the spreed sheet was set to two decimal places when the calculator vs Excel variance of .01 occurred. The cumulative rounding of A1, A2, & A3 in the 100th place creates the .01. Cells A4 and A5 are manually entered and are 2 decimal places only. I was thinking there has to be a function for cells A1, A2, and A3 that would ignore the 3rd decimal or force is to only two with an "IF" statement or ABS or something similar. I am clearly aware I can reduce it to two decimal place, but when it sums it will include that 3rd decimal place not visable in Excel when you reduce it to two decimal place.

 

[rlc]

I found the solution it is the round function, thanks to everyone who responded.

 

[dUBBINS]

Mike...Good eye. I didn't catch that percentage.