0

1 AU is 149597870700 meters. This is much more information than typically needed, so 150 million kilometers would be precise enough in most situations.

Does spreadsheets have a function to handle this? (I use Google Spreadsheet, LibraOffice and Excel)

Currently I do it by dividing the number to 14,9597870700 and round it. Then multiply up again.

log10(149597870700) = 11,18
floor(11,18) = 11
11+1-2 = 10 # two significant digits
149597870700/power(10,10) = 14,9597870700
round(14,9597870700) = 15
15*power(10,10) = 150,000,000
hschou
  • 111

1 Answers1

1

A combination of LOG10() and ROUND() will achieve this. I found a reference here that gives the Excel formula

=ROUND(number,digits-(1+INT(LOG10(ABS(number)))))

where number is the number you want to round and digits is the required number of significant digits.

I don't have Excel, but an equivalent formula in a different spreadsheet worked as intended.

nickgard
  • 4,116
  • Nice. Not one single formula, but simpler than mine. It works fine i Google Spreadsheet exactly as written. In sc the formula is @round(A1,B1-(1+@floor(@log(@abs(A1))))) but actually the same. – hschou Nov 24 '17 at 09:02