Forum Discussion
Rounding Issues
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.