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+...
excel
Formulas and Functions
Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
Nov 17, 2019

steve_techie 

 

Try this one:

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

  • 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.

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

        Thank you Sergei, I didn't realise you could customise that far. If someone else opens my spreadsheet and does not have that customisation set up will it still display okay?

Resources