In the example shown, the formula in D6 is as follows: First, when you have a formula like this where one function (in this case ROUND) wraps around all others, it’s often helpful to work from the outside in. So, at the core, this formula is rounding the value in B6 using the ROUND function: Where x is the number of significant digits required. The tricky part of this formula is calculate x. This is a variable, because it will change depending on the number being rounded. x is calculated with this bit: This seems complicated, so let’s look first at how the formula needs to work for the given examples. Remember with ROUND that a negative number of digits works on the left side of the decimal. So, to round 1234567 to an increasing number of significant digits, we would have: So, the main problem is how to calculate -6, -5, -4 and so on depending on the number we are rounding. The key is understanding how these numbers could be expressed using exponents, as in scientific notation: Note that the exponent is 6 in all cases, which is determined with this bit: So, the rest of the formula is just using the calculated exponent value to figure out the right number to give ROUND depending on the number of significant digits desired: So, in summary:
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
