Forum Discussion

Tin Contributor

Jun 22, 2026

Solved

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 ...

Excel for web

Formulas and Functions

IlirU

Jun 22, 2026

Hi JuJuBee,

Try below formula in cell D2 (drag it right and down).

=INT(ROUND(($B2 - (SUMPRODUCT(ROUND($C$1:C$1, 2), ROUND($C2:C2, 2)))), 2) / D$1)

or more shorter:

=INT(ROUND(($B2 - (SUMPRODUCT($C$1:C$1, $C2:C2))), 2) / D$1)

HTH

IlirU

Ahmed_Masoud97

Steel Contributor

Sep 15, 2026

Calculate in whole cents to avoid floating point errors. Use =QUOTIENT(ROUND($B3*100,0),ROUND(C$2*100,0)) in C3, then use =QUOTIENT(ROUND($B3*100,0)-SUMPRODUCT($C3:C3,ROUND($C$2:C$2*100,0)),ROUND(D$2*100,0)) in D3 and copy it through K3. Check the result with =SUMPRODUCT(C$2:K$2,C3:K3). Excel confirms that ROUND changes the stored calculation value, rather than only its display.

Copying or filling normally does not remove $: $C3 locks the column, H$3 locks the row, and $H$3 locks both. Therefore, copying H$3 downward should leave it as H$3; use F4 while editing a reference to cycle through relative, absolute, and mixed forms.