You are here:

Excel/Trim numbers after decimal

Advertisement


Question
Hi

I am working on an excel file in my project. It returns a numeric value with 3-4 digits after decimal (example 999.1234).

I want to show in next column to it with only first 2 digits after decimal ( example 999.12). The important thing here is to keep the same numbers and don't round up.

For example:
Column A   Column B
999.1234   9993.12
999.1268   999.12 (no change due to round up)

I am not able to use VB due to restrictions set up in the work environment so only relying on formulas directly in cells.
Please suggest

Answer
Hi Steve,

What you want cannot be done via cell number formatting.  Thus there is no way to round the value DOWN to two decimal places and keep the underlying cell value (e.g., 999.1268) in column B.

However, you can use the ROUNDDOWN worksheet formula ( =ROUNDDOWN(A2,2) ) to round down to two places, but of course 999.1268 would then yield a value in column B of 999.12, and this would then be this cell's underlying value as well as the displayed value.

I hope this helps.

Damon
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Damon Ostrander

Expertise

I have extensive experience with VBA programming in Excel 5 through Excel 2013. As a former aerospace engineer with a large aerospace corporation and consultant in a small defense technology services company, I have developed a wide range of applications in VBA, including simulations involving mixed-language programming, satellite orbit mechanics, graphics and animation, and real-time applications. I am interested in moderate to hard VBA-related questions only.

Experience

I have developed and taught several courses in Excel VBA programming and also VBA programming in Office 97, 2000, and 2007. I have developed a number of large technical applications in Excel VBA for use within the aerospace industry.

Education/Credentials
B.S. in Electrical Engineering and Computer Science, University of California, Berkeley.

©2016 About.com. All rights reserved.