Excel Snippet: Preserve Decimals in Result of Formula

Microsoft ExcelI 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")

Can ISC Software Solutions help?

ISC Software Solutions are UK and Ireland based experts on Microsoft Dynamics GP. If you'd like to speak to someone about support, consultancy, upgrades, implementation, development, GP Elementz add-ons or portals, or anything else Dynamics GP related, you can use the form below.





Your Name (required):
Your Email (required):
Subject:
Enquiry message:

What should we write about next?





Your Name (required) -
Your Email (required) -

Leave a Reply

Your email address will not be published. Required fields are marked *