Versions Compared

Old Version 26

changes.mady.by.user K A

Saved on

compared with

New Version 27

changes.mady.by.user K A

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

values

results

comments

123456789

Calc macro display
predefined-formatGeneral Number
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)))))

  • 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

Calc macro display
format#,###
ROUND(a2,3-(1+INT(LOG10(ABS(a2)))))

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

123800210

Calc macro display
format#,###
ROUND(a3,3-(1+INT(LOG10(ABS(a3)))))

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

13.123

Calc macro display
format#.#
ROUND(a4,3-(1+INT(LOG10(ABS(a4)))))

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

with a format, “#.#”

13

Calc macro display
format#.#
ROUND(a5,3-(1+INT(LOG10(ABS(a5)))))

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

with a format, “#.#”

table2

values

results

comments

123456789

Calc macro display
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)))))

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.