I have a huge list of values like:
0,1686448
216,4577890
0,00000453
0,0045663
and need to round them by the last two values not zero like:
0,17
216,46
0,0000045
0,0046
The first 2 numbers are not the problem by setting ROUND to 2 Decimals. But for the last 2 numbers like 0,00000453 I get 0.
Is there a formula to round anytime for the last two values not zero?
CodePudding user response：
You could use:
=INT(A1) ROUND(MOD(A1,1),INT(2-LOG(MOD(A1,1))))
CodePudding user response：
Split into two criteria: if rounded to 2 d.p. = 0 then round to 2 s.f.
=LET(n, A1, sf, 2, x, ROUND(n,2), IF(x=0, ROUND(n,sf-(1 INT(LOG10(ABS(n))))), x))
Uses the generic round to significant figures formula
ROUND(number,digits-(1 INT(LOG10(ABS(number)))))