Forum Discussion

steve_techie's avatar
steve_techie
Copper Contributor
Nov 17, 2019

2 decimal places - help please

I am trying ti shortcut a solution yet cannot get the result to display correctly. I am adding text (Subtotal £.....) to the equation of adding one number to another from another spreadsheet tab (D9+D10) and trying to get the answer to display to 2 decimal places. This works fine when the answer is not a whole number. FOr example. If the answer is £4156.87 then this works fine however, if the answer is £2250.00 the result only shows as "Subtotal £ 2250" not the "Subtotal £2250.00" that I want to see. I tried setting the cell to number, currency, etc. etc. but this does not help, any suggestions please?

 

ACTIAL FORMU

="Subtotal   £ "&ROUND(('Summary and rates'!D9+'Summary and rates'!D10),2)

excel
Formulas and Functions

5 Replies

    • steve_techie's avatar
      steve_techie
      Copper Contributor
      Nov 17, 2019

      Perfect, thank you. I had just discovered the answer when you replied although the other solution says use "##.##" instead of your zeros

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Nov 17, 2019

        steve_techie 

        Another variant is to keep resulting cell value as the number and apply to it custom number format as desired. That allows to use this number in other calculations without back transforming from text to number.

         

        For example, you have 2250 as result of some calculations:

        Click Ctrl+1 on the cell and apply custom format

        "Subtotal "£#,##0.00; "Subtotal -"£#,##0.00;"Subtotal" £#,##0.00;

        First group separated by semicolon is for positive numbers, next to negative and when for zero.

Resources