I might not post many Excel snippets, but I’m collecting them into a small Excel Snippets series to make them easy to find.
I do far more manipulation of data in SQL scripts than I do in Excel, but sometimes need to accomplish something in Excel for clients. This time I was working with a client using Excel to generate a Microsoft Dynamics GP macro to update some costs.
We realised when testing that the decimal places were being lost by Excel so the macro contained 100 for £100 instead of the 100.00 which is needed for the macro to run successfully. I tired a couple of things and eventually found that TEXT can be used to retain the decimals.
The example, below, also includes a ROUND to reduce the result of a calculation to only two decimals.
=TEXT(ROUND(B2,2),".00")
Click to show/hide the Excel Snippets Series Index
What should we write about next?
If there is a topic which fits the typical ones of this site, which you would like to see me write about, please use the form, below, to submit your idea.
1 thought on “Excel Snippets: Preserve Decimals in Result of Formula”