This can be an intimidating formula if you don't have a good background in math, but let's work through it step-by-step.
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:
=ROUND(1234567,-6)=1000000// 1 sig. digit=ROUND(1234567,-5)=1200000// 2 sig. digits=ROUND(1234567,-4)=1230000// 3 sig. digits=ROUND(1234567,-3)=1235000// 4 sig. digits
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:
The Excel INT function returns the integer part of a decimal number by rounding down to the integer. Note the INT function rounds down, so negative numbers become more negative. For example, while INT(10.8) returns 10, INT(-10.8) returns -11.
Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.