Question Regarding Rounding Scientific Notation in Excel2007

Greetings all,

I am in the process of creating a risk assessment calculator for work, and am having some issues with a particular function.

Essentially, I want the formula results to be displayed as X.XXE-X, which is easy enough to do just by cell formatting. However, when I sum multiple numbers I have values that don't "add up" due to rounding issues (e.g. 2.28E-6 + 1.06E-5 =1.28E-5 instead of rounding correctly to 1.29E-5) so I want to force the result of each formula to be rounded to three significant figures, so the sum reflects what is displayed correctly (so 2.28E-6 is actually 2.28E-6 and not 2.2758E-6 displayed as such).

After searching around for a while on this forum and elsewhere, I have determined that the way to accomplish this would be to use the following formula (let's assume that the formula is in cell A1):

=ROUND(A1,3 - 1 - INT(LOG10(ABS(A1))))

This seems to work just fine and does resolve the rounding issues. My question though (and perhaps this isn't the right place? I dunno, I am new here) is why? I understand what the component formulas are (INT, LOG10, ABS), but what does the -1- mean and how does "3 - 1 - INT(LOG10(ABS(A1))))" translate into three significant figures? I am glad that it works but I would like to be able to have a basic understanding of the syntax as I don't want to tell my superiors that I used it "because it works, but I don't know why."

Thank you very much in advance, and I apologize if I have posted this in the wrong forum!

Colin

For a number greater than 1, int(log10(abs(A1))) will return the number of digits -1, for a number less than 1, it will return the number of 0's as a negative.

So if you have:
4567812
then int(log10(abs(a1))) will return 6,
3-1-6 (you could just use 2-int(log10(abs(a1))) for what it's worth) will round to -4 decimal places, or
4560000

if you have
0.000343123
then int(log10(abs(a1))) will return -4, so rounding to 3-1+4 = 6 so rounding to 6 decimal places:
0.000343

That...makes a lot of sense, actually. Thank you very much for the speedy reply!

Originally Posted by AngularUnconformity

I want the formula results to be displayed as X.XXE-X, which is easy enough to do just by cell formatting. However, when I sum multiple numbers I have values that don't "add up" due to rounding issues (e.g. 2.28E-6 + 1.06E-5 =1.28E-5 instead of rounding correctly to 1.29E-5) so I want to force the result of each formula to be rounded to three significant figures, so the sum reflects what is displayed correctly (so 2.28E-6 is actually 2.28E-6 and not 2.2758E-6 displayed as such).

To round to 3 significant digits, simply write:

--TEXT(A1,"0.00E+0")

where A1 can be a cell reference or an expression. The double negation (--) converts the text result to numeric.