Home

2 decimal places - help please

%3CLINGO-SUB%20id%3D%22lingo-sub-1013601%22%20slang%3D%22en-US%22%3E2%20decimal%20places%20-%20help%20please%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1013601%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20trying%20ti%20shortcut%20a%20solution%20yet%20cannot%20get%20the%20result%20to%20display%20correctly.%20I%20am%20adding%20text%20(%3CEM%3ESubtotal%20%C2%A3.....%3C%2FEM%3E)%20to%20the%20equation%20of%20adding%20one%20number%20to%20another%20from%20another%20spreadsheet%20tab%20(D9%2BD10)%20and%20trying%20to%20get%20the%20answer%20to%20display%20to%202%20decimal%20places.%20This%20works%20fine%20when%20the%20answer%20is%20not%20a%20whole%20number.%20FOr%20example.%20If%20the%20answer%20is%20%C2%A34156.87%20then%20this%20works%20fine%20however%2C%20if%20the%20answer%20is%20%C2%A32250.00%20the%20result%20only%20shows%20as%20%22Subtotal%20%C2%A3%202250%22%20not%20the%20%22Subtotal%20%C2%A32250.00%22%20that%20I%20want%20to%20see.%20I%20tried%20setting%20the%20cell%20to%20number%2C%20currency%2C%20etc.%20etc.%20but%20this%20does%20not%20help%2C%20any%20suggestions%20please%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EACTIAL%20FORMU%3C%2FP%3E%3CP%3E%3D%22Subtotal%26nbsp%3B%26nbsp%3B%20%C2%A3%20%22%26amp%3BROUND(('Summary%20and%20rates'!D9%2B'Summary%20and%20rates'!D10)%2C2)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1013601%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1013623%22%20slang%3D%22en-US%22%3ERe%3A%202%20decimal%20places%20-%20help%20please%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1013623%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F460019%22%20target%3D%22_blank%22%3E%40steve_techie%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETry%20this%20one%3A%3C%2FP%3E%3CP%3E%3D%22Subtotal%20%C2%A3%20%22%26amp%3BTEXT('Summary%20and%20rates'!D9%2B'Summary%20and%20rates'!D10%2C%220.00%22)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1013731%22%20slang%3D%22en-US%22%3ERe%3A%202%20decimal%20places%20-%20help%20please%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1013731%22%20slang%3D%22en-US%22%3E%3CP%3EPerfect%2C%20thank%20you.%20I%20had%20just%20discovered%20the%20answer%20when%20you%20replied%20although%20the%20other%20solution%20says%20use%20%22%23%23.%23%23%22%20instead%20of%20your%20zeros%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1013894%22%20slang%3D%22en-US%22%3ERe%3A%202%20decimal%20places%20-%20help%20please%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1013894%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F460019%22%20target%3D%22_blank%22%3E%40steve_techie%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAnother%20variant%20is%20to%20keep%20resulting%20cell%20value%20as%20the%20number%20and%20apply%20to%20it%20custom%20number%20format%20as%20desired.%20That%20allows%20to%20use%20this%20number%20in%20other%20calculations%20without%20back%20transforming%20from%20text%20to%20number.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFor%20example%2C%20you%20have%202250%20as%20result%20of%20some%20calculations%3A%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20661px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F157583i4880D953973D6C5C%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EClick%20Ctrl%2B1%20on%20the%20cell%20and%20apply%20custom%20format%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%22Subtotal%20%22%C2%A3%23%2C%23%230.00%3B%20%22Subtotal%20-%22%C2%A3%23%2C%23%230.00%3B%22Subtotal%22%20%C2%A3%23%2C%23%230.00%3B%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EFirst%20group%20separated%20by%20semicolon%20is%20for%20positive%20numbers%2C%20next%20to%20negative%20and%20when%20for%20zero.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1014335%22%20slang%3D%22en-US%22%3ERe%3A%202%20decimal%20places%20-%20help%20please%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1014335%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20Sergei%2C%20I%20didn't%20realise%20you%20could%20customise%20that%20far.%20If%20someone%20else%20opens%20my%20spreadsheet%20and%20does%20not%20have%20that%20customisation%20set%20up%20will%20it%20still%20display%20okay%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
steve_techie
New Contributor

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)

5 Replies

@steve_techie 

 

Try this one:

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

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

@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:

image.png

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.

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?

@steve_techie 

Yes, custom number format is available for everyone as any other format. The only difference you may apply format from menu (e.g. Currency, Number, etc.), when format code will be generated automatically. Or you may add it manually using Custom, in this case you have more options for formatting.

 

That's a lot of info about custom number formats with samples, first what Google returns is https://exceljet.net/custom-number-formats

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies