Note

  • The following formula calculates N significant figures

    • ROUND( ​your cell , ​precision -(1+INT(LOG10(ABS( ​your cell )))))

    • CITE: ​https://exceljet.net/formula/round-a-number-to-n-significant-digits

values

results

comments

123456789

Qron Calc Macro
​

  • TEXT(IF(A1<0,"-","")&LEFT(TEXT(ABS(A1),"0."&REPT("0",3-1)&"E+00"),3+1)*10^FLOOR(LOG10(TEXT(ABS(A1),"0."&REPT("0",3-1)&"E+00")),1),(""&(IF(OR(AND(FLOOR(LOG10(TEXT(ABS(A1),"0."&REPT("0",3-1)&"E+00")),1)+1=3,RIGHT(LEFT(TEXT(ABS(A1),"0."&REPT("0",3-1)&"E+00"),3+1)*10^FLOOR(LOG10(TEXT(ABS(A1),"0."&REPT("0",3-1)&"E+00")),1),1)="0"),LOG10(TEXT(ABS(A1),"0."&REPT("0",3-1)&"E+00"))<=3-1),"0.","#")&REPT("0",IF(3-1-(FLOOR(LOG10(TEXT(ABS(A1),"0."&REPT("0",3-1)&"E+00")),1))>0,3-1-(FLOOR(LOG10(TEXT(ABS(A1),"0."&REPT("0",3-1)&"E+00")),1)),0)))))

  • don’t work

    • under investigation

123456789

Qron Calc Macro
​

ROUND(a2,3-(1+INT(LOG10(ABS(a2)))))

123800210

Qron Calc Macro
​

ROUND(a3,3-(1+INT(LOG10(ABS(a3)))))

13.123

Qron Calc Macro
​

ROUND(a4,3-(1+INT(LOG10(ABS(a4)))))

with a format, “#.#”

13

Qron Calc Macro
​

ROUND(a5,3-(1+INT(LOG10(ABS(a5)))))

with a format, “#.#”


table2

values

results

comments

123456789

Qron Calc Macro
​

TEXT(IF(A1<0,"-","")&LEFT(TEXT(ABS(A1),"0."&REPT("0",3-1)&"E+00"),3+1)*10^FLOOR(LOG10(TEXT(ABS(A1),"0."&REPT("0",3-1)&"E+00")),1),(""&(IF(OR(AND(FLOOR(LOG10(TEXT(ABS(A1),"0."&REPT("0",3-1)&"E+00")),1)+1=3,
RIGHT(TEXT(LEFT(TEXT(ABS(A1),"0."&REPT("0",3-1)&"E+00"),3+1)*10^FLOOR(LOG10(TEXT(ABS(A1),"0."&REPT("0",3-1)&"E+00")),1), "#."),1)
="0"),LOG10(TEXT(ABS(A1),"0."&REPT("0",3-1)&"E+00"))<=3-1),"0.","#")&REPT("0",IF(3-1-(FLOOR(LOG10(TEXT(ABS(A1),"0."&REPT("0",3-1)&"E+00")),1))>0,3-1-(FLOOR(LOG10(TEXT(ABS(A1),"0."&REPT("0",3-1)&"E+00")),1)),0)))))


First argument of RIGHT function must be String.

So, inserted TEXT(val, “#”) after RIGHT function.