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 is indexed by filename, and produce multiple reports that need certain numbers in either 1, 2 or 4 decimal places. I'd be really happy to add a column in my 'data' tab that would save me going through and changing the format manually each time.

 

 

** As an aside, if I try to post in this forum, it tells me I have an error (I didn't), then try to post again and get the same non-existent error, then try a third time it refuses because "I have posted more than three times in 60 seconds", do we think that is a sensible thing?

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.

5 Replies

  • dscheikey's avatar
    dscheikey
    Bronze Contributor
    Oct 07, 2022

    LesHughes 

    Of course, you can set the number of descendants displayed with a reference to a cell. However, this also changes the value. It is not pure formatting.

    For example: TRUNC():

    =TRUNC(1.66,A1)
A1=1
Result = 1.6

    The same is true with ROUND() etc.


    I hope this has helped 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.

Resources