Rounding Formula Parameters to Make Summations Accurate

dennyvle

New Member
Joined
Sep 12, 2014
Messages
8
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,
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Jeffrey Mahoney

Well-known Member
Joined
May 31, 2015
Messages
1,768
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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,030
Messages
5,526,341
Members
409,697
Latest member
christopherlewis1620

This Week's Hot Topics

Top