[Solved] Rounding to significant digits (not decimals)

[Marius Marsh]

I am looking for a way in Open Office Calc to round many numbers in tables to a given number of significant digits as opposed to decimal places. For example, to take a number such as 0.005789 and round it to 0.0058, and, using the same formula, round 1789 to 1800. In Excel, this can be done by using the formula
ROUND(xxxx,#Digits-1-INT(LOG10(ABS(xxxx))))
but this formula doesn't work in Open Office Calc. It seems that there is a difference between how Open Office handles the General Number format and how Excel handles it. Excel seems to read the number before formatting it and use a logical determination as to how it allows the display, which then allows the above formula to work, but the Open Office method doesn't appear to allow this flexibility and sometimes ends up cutting off digits (e.g. 0.005789 becomes 0).
The obvious solution is to use scientific format, but the general population doesn't really like to read 150 as 1.5e02, so it is not a desirable option.
Any suggestions would be appreciated.

Solved by change to "general" format in later version of Calc.

[mriosv]

The format never afect the cell content or formula result, only the visualization.
I think the formula is:
=ROUND(a1;LEN(a1)-3-INT(LOG10(ABS(a1))))
Works equal on OOo and Excel for me.

[Robert Tucker]

The formula below rounds the value in cell A1 to the number of significant digits specified in cell A2:

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

http://spreadsheetpage.com/index.php/ti ... nt_digits/

which is essentially the same as the formula given in the macro discussed here:

http://user.services.openoffice.org/en/ ... =9&t=25658

and available for download here:

http://homepages.paradise.net.nz/hillview/OOo/ (It contains nothing but the above formula.)

(Just thought I'd add this as general reference for visitors to this thread.)

[Marius Marsh]

Thanks for the suggestion. The solution
=ROUND(A1,A2-1-INT(LOG10(ABS(A1))))
works in Excel in all cases, but in Open Office it falls apart if the number being rounded is smaller than 0.1. For example, used for A1 = 0.001234 and A2 =2 returns "0". Yes, the actual number is there somewhere, but it is displayed as 0, and since the purpose of the rounding is for display, this poses a problem when presenting the information. If one sets the format as a fixed number of decimal places, then it displays all the decimal places, implying a precision that the rounding is trying to avoid, or cutting off legitimate digits. One doesn't want to have to set the decimals in the display format independently for each cell (after the calculation has been done) if one has tables with hundreds or thousands of numbers.

If the above formula is working for you for all numbers (from small (e.g. 0.00123) to large), are you using a special numeric format other than general, and what version of Open Office are you using?

As for the other suggestion
=ROUND(a1;LEN(a1)-3-INT(LOG10(ABS(a1))))
this doesn't appear to work for anything but a very limited range of numbers unless the "3" is changed to an appropriate number for each A1 value string length.

Any more suggestions would be appreciated. This might not be possible without altering how OOcalc handles the general number format, such as each cell being formatted according to the string length of the input number, which appears to be what Excel does.

[Robert Tucker]

OpenOffice 3.1.1 on Fedora 12.

[acknak]

Just out of curiosity, could you explain what you want to do with this formula?

I've never run into a situation where the answer for a particular calculation varied enough to need an adjustable format.

Also, doesn't the proper output format (i.e. the number of significant digits) depend on the precision of all the inputs, and not the value (magnitude) of the result?

Sorry if I'm just muddying the waters here--I'm no mathematician.

[Marius Marsh]

Hello and thanks for your interest. The spreadsheet uses a series of complicated models and algorithms to calculate values for chemical concentrations in different media, and which might be used by the public in the future. As such, it is important that the format of the final calculated value is meaningful and understandable (scientific notation works, but it is not likely to be popular with the non-scientific community) Input variables are generally to either two or three digit (note, not decimal) accuracy, and therefore the final numbers should be to two digit (not decimal) accuracy. Results can vary from in the tens of thousands to less than 0.0001, depending on the chemical and its toxicity. As mentioned, the formula given in the first posting and by others works in Excel, but in OOCalc it sometimes cuts off significant digits, or leaves a string of trailing zeros, for numbers smaller than 0.1. (e.g. 0.0123 becomes 0.01 and 0.00123 becomes 0).

[acknak]

Thanks for the explanation.

[eremmel]

When you want to have three sig. dig. you might convert the number to a string and back to a number with =VALUE(TEXT(A1;"0,00E00")). You can use any display format you like Note that it might be handsome to replace the format string with a named-cell reference to have one place to control the format.

[Cambirder]

As this is for scientific purposes I would have thought the proper way to do this would be to display it in scientific notation. so for instance 0.000786 would be displayed as:-

7.86E-004 for 3 significant figures

and

7.9E-004 for 2 significant figures

[Robert Tucker]

...or leaves a string of trailing zeros...

You can remove trailing zeros with:

Code: Select all

=LEFT(C4;MAX(IF(MID(C4;ROW(INDIRECT("1:"&LEN(C4)));1) *1<>0;ROW(INDIRECT("1:"&LEN(C4))))))

entered as an array formula – Ctrl+Shift+Enter.

http://www.tech-archive.net/Archive/Exc ... /3121.html

[mriosv]

What don't run in OOo?
Attached file with your formula. =ROUND(A1,A2-1-INT(LOG10(ABS(A1))))

[eremmel]

When you do not use scientific notation, but want only rounding and present zeros at the end of the number, those should be considered as significant number as well. An idea to prevent too many zeros at the end in combincation with rounding might be done with the help of the following functions (but this is still wrong for numbers that are >= 100 with 2 sig digits):

Digits.PNG (8.58 KiB) Viewed 34576 times

Note that you get a leading zero in column D when you state "0.##########"

[Robert Tucker]

The biggest remaining problem to me is that if you present a computer or human with a decimal number like, say, 0.934, and say you want it to two significant figures, it/he/she has no way to tell if it should be 0.9 or 0.93. Also if you remove all the trailing zeros and one sees 0.1 one cannot tell if it is 0.1±0.05 or 0.10±0.005. Can values be given with an error range and the general public be expected to understand such a value?

[eremmel]

You have that problem also for the larger numbers. When you read 123000, has that an error of +/- 500, 50 ,5 or 0.5. When all data in the sheets uses the same number of significant digits you can tell. I would say when you present info to people that know about significant digits, you can also assume they know how to read 0.000E00 formats. You can only present data in non-scientific notation for the values between <1E+n .. 1E-n> with n-1 significant digits. I think that we hit here the origin of calc/excel: the financial world... One need to have a printf(3) format like %4.3g as with C and perl etc to make it right.
One might solve the large number issue with help of conditional formatting. I can not think of a quick way to solve the missing right hand side zeros. Replace the format in my previous post in Column D with:

Code: Select all

=Text(C1;"[>1000]0.00E+00;[<0.1]0.00E+00;0.###")

Unfortunate one can only have three groups in place.
When you work with two digits one can work all possibilities out with

Code: Select all

=IF(C1>=100;TEXT(C1;"0.0e+0");IF(C1>=10;TEXT(C1;"0");IF(C1>=1;TEXT(C1;"0.0");IF(C1>=0.1;TEXT(C1;"0.00");TEXT(C1;"0.0e+0")))))

Note: one need first to round to significant digits as proposed in the calculations in columns B and C

Edit: Tip: One can work with 'Named Ranges' (Ctrl-F3) and use them as 'functions when one specify the range relative. I position to field B1 and defined the following two ranges: Code: Select all Name: Assign To: Round3 VALUE(TEXT(A1;"0.00e0")) Scientific3 IF(Round3>=10;TEXT(Round3;"[>=1000]0.00E+00;[>=100]000;00.0");IF(Round3>0;TEXT(Round3;"[>=1]0.00;[>=0.1]0.000;0.00e+00");IF(Round3=0;TEXT(Round3;"0.000");IF(Round3<=-10;TEXT(Round3;"[<=-1000]0.00E+00;[<=-100]-000;-00.0");TEXT(Round3;"[<=-1]0.00;[<=-0.1]-0.000;-0.00e+00")))) Now I can present any numerical value (e.g. '23.45' in D14) with three significant digits by specifying '=Scientific3' in the cell right to the value (e.g. E14 shows '23.5').

[mriosv]

As

The formula below rounds the value in cell A1 to the number of significant digits specified in cell A2:
=ROUND(A1,A2-1-INT(LOG10(ABS(A1))))
http://spreadsheetpage.com/index.php/ti ... nt_digits/

The formula works, only need to change the Number formula style to see the result well.
like ###.##0,########

[Robert Tucker]

The formula works, only need to change the Number formula style to see the result well.
like ###.##0,########

Or for the UK, US etc ###,##0.########

[Marius Marsh]

Thanks everyone. I found a posting on Bugs.launchpad.net/openoffice that indicated a bug (#314743) whereby Open Office Calc is displaying numbers less than 0.01 as 0 when in the general number format (also appears to be on openoffice.org/issues as issue #26826). Solving this bug would likely solve the problem that we are discussing and allow the formula that works in Excel to work in OOCalc. I think that they are working on it, but in the meantime, I am going to try your ideas. (or, maybe people can accept scientific notation!!)

[dharasty]

I'm new to OpenOffice, but not new to the need to use "significant digits" in the mathematical-precision sense.

Here's my solution... based on string-hacking of the exponential notation:
A1 = 1234
B1 =TEXT(A1;"#.#E#")
C1 = FIND("E";B1)
D1 = VALUE(LEFT(B1;C1-1))
E1 = VALUE(RIGHT(B1;LEN(B1)-C1))
F1 = D1*10^E1

Code: Select all

data:
A            B       C   D     E    F
1234         1.2E3	4	1.2	3	 1200
123.4        1.2E2	4	1.2	2	 120
12.34        1.2E1	4	1.2	1	 12
1.234        1.2E0	4	1.2	0	 1.2
0.1234       1.2E-1  4	1.2	-1	0.12
0.01234      1.2E-2  4	1.2	-2	0.012
0.001234     1.2E-3  4	1.2	-3	0.0012

Works for negative numbers, too.

For the reader:
- adjust the TEXT format to get the number of significant digits as you'd like
- consider replacing the "FIND" with a fixed number (once you have settled on your format string, and all your data is of one sign)
- make it a one-liner (instead of using many columns)

Helpful?

LIGHTBULB: For two significant digits, I think this will do the same: =VALUE(TEXT(A1;"#.#E#")). Just add more "#" after the "." in the format if you want more significant digits.

[eremmel]

This is also a nice approach, but it lacks the trailing zeros for small numbers in column F. E.g. the numer 0.01000 becomes 0.01 i.s.o. 0.010.

[kingfisher]

FWIW the function MROUND achieves the same results, as shown in the snapshot.

mround.png (8.48 KiB) Viewed 30840 times

[Marius Marsh]

Thanks everyone for the renewed interest. Since the original posting in 2009, Libre Office formatting has changed (I am not sure exactly when) in some manner such that the original equation that was used in Excel; that is, ROUND(xxxx,#Digits-1-INT(LOG10(ABS(xxxx))))
now works in Libre Office, except in situations where a conditional formatting (e.g. shading) has been used.

[acknak]

FWIW the function MROUND achieves the same results, ...

The problem--as I understand it--is a formula to display n significant digits for any input; MROUND requires you to know the input ahead of time to choose the proper rounding value.

... the original equation that was used in Excel; that is, ROUND(xxxx,#Digits-1-INT(LOG10(ABS(xxxx)))) now works in Libre Office ...

It seems to work in OOo as well. The Number/General formatting was enhanced just before the OOo/LibO split--IIRC.

[kingfisher]

Ah, that would be messy.

[eremmel]

Time to change the status to [Solved] with the green check mark?

[Marius Marsh]

OK - I hope I did it correctly. There is still a minor problem with condiditonal formatting reverting to scientific notation, but that is in consequential, (I think).