Forum Discussion

LesHughes's avatar
LesHughes
Copper Contributor
Oct 07, 2022
Solved

Auto cell formatting

Is it possible to format a number in a cell by number of decimal places, where the number of decimal places is stored in a different cell?   I enter a load of data in rows into a tab ("data") that ...
excel
  • dscheikey's avatar
    dscheikey
    Oct 07, 2022

    LesHughes 

    Another possibility would be:

    =TEXT(A1,"#,##0"&IF(B1=0,"","."&REPT("0",B1)))
A1=66
B1=5
Result=66.00000

    With the disadvantage that the number is then converted to a text. But maybe that doesn't bother you.

LesHughes's avatar
LesHughes
Copper Contributor
Oct 07, 2022

dscheikey 

That works a treat for shortening numbers to a number of decimal places but doesn't add trailing zeroes on, even if I store the number with eg. 4 decimal places say 66.0000 and ask it to truncate to 1 dp it just gives me 66 not 66.0

 

I've tried formatting the cells as 'general' and as 'number' and it doesn't seem to work either way.

dscheikey's avatar
dscheikey
Bronze Contributor
Oct 07, 2022

LesHughes 

Or even simpler:

=FIXED(A1,B1)
A1=60
B1=5
Result=60.00000

 

Resources