Excel Pro Tip: How to round numbers to the tens, hundreds, or thousands place; not just integers

I just discovered a neat trick for estimating numbers in Excel. This was done in Excel 2007, but should work in the newer versions.

I was working on some retirement account projections, and projecting various growth levels for 1, 5 and 25 years in the future. I was getting some incredibly specific numbers, but that is what happens when you raise a number to 1.075^25th power.

I wanted some ambiguity in the model so that I had nice round numbers. This has real-world applications in consultancy. Unless you're sitting down with Pat the Accountant, no one cares that you're reducing costs by $34,946. Just tell Larry, the jackass Director of Fleet Services, that it's going to be $35 grand per whatever. He's probably not listening anyway, and is just trying to figure out how to retreat back to his office before anyone needs him to actually do anything.

You can even build in your estimates to a certain round number, such as $40,000 -> $50,000 with no results in between.

Here's how to do this in Excel:

• Start with your number, preferably formatted in dollars (if you're working with money). I don't like the accountant formatting except in special cases; use currency instead.
• In a new cell, use the ROUND function. The number of places you round to will follow the cell.
• Normally, we would round money to two decimal places. Sometimes you want to round it off to the nearest dollar. Zero will give you integer values.
• If you want to round to tens, hundreds, or some other decimal place to the left of the decimal point, use a negative number.
• In the example below, I've rounded my 25th power of 7.5% value several different ways.
• You can also use ROUNDUP or ROUNDDOWN with a negative value. In the example, anything equal to or greater than $30,000.01 will round up to $40k.