Rounding Formula Parameters to Make Summations Accurate

[dennyvle]

Hello,

I have reports that are in whole dollars and decimals with subtotals and totals. I need to report these in thousands. The round(x,-3) formula works for presentation for the individual rows and totals, but the sum does not equal the rounded totals and subtotals. I end up having to add or subtract 1 from a random row. Is the a better way to achieve this?

Thanks,

 

[Jeffrey Mahoney]

You can create a custom number format that will display the values in thousands. It doesn't change the result, just the display of the value.

0.00, "k"
Will display 1452.21 as 1.45 k

[>=1000000]0.00,,"M";[>=1000]0.00,"K";0
Will display 3,781,546.35 as 3.78M and 1452.21 as 1.45K

I got the info off this site: https://peltiertech.com/Excel/NumberFormats.html