Forum Discussion
Rounding Issues
I am trying to create a "change maker" spreadsheet. We have a need to determine monetary denominations for several clients for refunds...
| A | B | C | D | E | F | G | H | I | J | K | L |
| Random | Money | 100 | 50 | 10 | 5 | 1 | 0.25 | 0.10 | 0.05 | 0.01 | |
| 775.1047 | 775.10 | 7 | 1 | 2 | 1 | 0 | 0 | 1 | 0 | 0 | $775.10 |
| 949.3219 | 949.32 | 9 | 0 | 4 | 1 | 4 | 1 | 0 | 1 | 2 | $949.32 |
| 947.3099 | 947.31 | 9 | 0 | 4 | 1 | 2 | 1 | 0 | 1 | 0 | $947.30 |
| 1231.75 | 1231.75 | 12 | 0 | 3 | 0 | 1 | 3 | 0 | 0 | 0 | $1,231.75 |
| 1180.4836 | 1180.48 | 11 | 1 | 3 | 0 | 0 | 1 | 2 | 0 | 2 | $1,180.47 |
| 1486.3901 | 1486.39 | 14 | 1 | 3 | 1 | 1 | 1 | 1 | 0 | 4 | $1,486.39 |
| 59.166 | 59.17 | 0 | 1 | 0 | 1 | 4 | 0 | 1 | 1 | 2 | $59.17 |
| 1450.5418 | 1450.54 | 14 | 1 | 0 | 0 | 0 | 2 | 0 | 0 | 3 | $1,450.53 |
| 1042.6726 | 1042.67 | 10 | 0 | 4 | 0 | 2 | 2 | 1 | 1 | 2 | $1,042.67 |
| 88.9004 | 88.90 | 0 | 1 | 3 | 1 | 3 | 3 | 1 | 1 | 0 | $88.90 |
| 457.3006 | 457.30 | 4 | 1 | 0 | 1 | 2 | 1 | 0 | 1 | 0 | $457.30 |
The formulas:
A: ROUND(RAND()*(1500-30)+30,4) - random number generator to create random money amounts between $30 & $1500
B: ROUND(A3,2) - round A to 2 decimal places for money
C2: INT(B3/$C$2) - Calculate $100's (simple)
C3: =INT(($B3-(SUMPRODUCT(ROUND($C$2:C$2,2),ROUND($C3:C$3,2))))/D$2) - Calculate each other denomination - requires keeping track of current balance..
D3:K3 - Similar to C3 adjusting for col/row...
D3-K3 are same as D2 adjusted for col/row...
L3: ($C$2*C3)+($D$2*D3)+($E$2*E3)+($F$2*F3)+($G$2*G3)+($H$2*H3)+($I$2*I3)+($J$2*J3)+($K$2*K3) - Error Checking...
The problem is in the Pennies column. Sometimes it is off by $0.01 as in rows 4, 6, & 9
A secondary problem that I am having is copying formulas with $... Sometimes when I copy a formula down that has ROUND($C3:H$3,2) for example, the H$3 turns into H3 and the $ is removed. In other cases it is added where it didn't exist. Is this normal behavior? I thought the $ was there to fix the row/col reference?
MK
2 Replies
The problem is only in the last column for the penny. Replace INT with ROUND( ... ,0)
So column K should be:
=ROUND(($B3-(SUMPRODUCT($C$2:J$2,$C3:J3)))/K$2,0)
As for the problem with the $ is the you have $C3:H$3 which is technically locking row 3 and column C and when you copy down you would get $C4:H$3, which although you can type it that way, it is still C3:H4 and therefore Excel will correct it to $C$3:H4
Finally you can also do this using array formulas like:
A3 with =RANDARRAY(10,1,30,1500,0)
B3: =ROUND(A3#,2)
C3: =INT(B3#/C2)
D3: =INT(($B3#-MMULT($C3#:C3#,TRANSPOSE($C$2:C$2)))/D$2)
and then just copy to the right (but again change the INT to ROUND in column K
Alternatively you can use LAMBDA and LAMBDA helpers like MAP or BYROW to generate the entire table of values
EDIT: I like IlirU suggestion to use the INT(ROUND( .... , 2) / D$2). I like this because although the problem is only being experienced when you get to the penny level, I think in theory it could happen at other levels because if you get a round off error that after division results in X.999999 then INT will return X instead of X+1, which is the problem you were seeing.
