Excel 2 Decimal Places without Rounding (4 Efficient Ways)

py down the numbers up to cell C10

Sometimes we may need to get values up to two decimal places. There are several ways to do that. In this article, I will show how to get two decimal places without rounding.

Download the Practice Workbook

4 Ways to Show Two Decimal Places without Rounding

1. Using TRUNC Function

Let’s assume we have a dataset of some Fruits with their Prices. Now I will show how to get the Prices up to two decimal points using the format cell option. Now we will update the Princess using the TRUNC function. First, we need to understand the basic syntax of this function. The syntax of the function: 

TRUNC (number, [num_digits])

First, we need to pass the number which we want to truncate in the first portion of the parameter. In the second portion, it contains the precision of the truncation which is by default 0. Depending on our number digits it will truncate our given number. Like if the formula is like this

=TRUNC(5.889) then this formula will return 5.88.

Step 1: Enter the formula below in cell C4.

=TRUNC(B4,2)

Formula Explanation

In the first part, I passed the number cell value which is B4. Then as per our requirements, 2 is passed to get the two decimal places value.

Enter the below formula in cell C4

Step 2: Copy down the formula up to C10.

Copy down the formula up to C10

2. Using Formula on Different Sizes Numbers (IF Function)

In this section let’s see how to do the formation using a formula with the INT function. Before going to the formula let’s see the details about the function. The syntax of this function is like this:

INT (number)

The main purpose of the function is to get the integer part of a decimal by rounding it down. Simply we need to pass the number then it will return the value.

➥ Read More: How to Round up Decimals in Excel (4 Simple Ways)

Step 1: Enter the formula in cell C4.

=INT(B4*100)/100

Formula Explanation

Here in the first section, we are getting integer values by using this formula INT(B4*100). We will get values like this 2,789.00, then divided by 100 we will get values up to 2 decimals like 27.89.

 Enter the formula in cell C4

Step 2: Copy down the formula up to C10.

Copy down the formula up to C10

[ Note: we multiply and divide by 10 for 1 decimal place, 1000 for 3 decimal places, and so on ]

3. Using ROUNDDOWN Function

Another common function is ROUNDDOWN which can be used to get numbers up to two decimal places in Excel. ROUNDDOWN is really not the most advisable method, but it works well in these conditions. The syntax of the function is like this:

ROUNDDOWN(number, num_digits)

Here firstly we will pass the number you want to round down and then pass the positive numbers, negative numbers, or 0 depending on the number of digits to which you want to round the number

[ Note it will find out the lowest possible round number from the current position. ]

Step 1: Enter the formula in cell C4.

=ROUNDDOWN(B4,2)

Formula Explanation

In the first section, I have passed the number we want to format which is in the B4 cell. After that 2 is passed to get the lowest possible two decimal places.

 Enter the formula using ROUNDDOWN in cell C4

Step 2: Copy down the formula up to the last cell.

 Copy down the formula up to the last cell.

4. Using Formula (FLOOR and IF Function) 

Using two of the most commonly used functions FLOOR and IF we can easily do that. In this section, I will show how to do that using formulas for the previous dataset. Let’s see the fundamentals of both functions first.

The syntax of the FLOOR function is like this:

FLOOR (number, significance)

This is the basic structure of this function. In the first portion, it takes the number which is going to be rounded. Then the second portion contains the multiple to which you want to round. But it will round the lowest possible round value as much as it can.

The syntax of the IF function is like this:

IF (logical_Condition, [value_if_true], [value_if_false])

In the first portion of the parameter, we need to pass our condition based on which we are going to compare. Then the second and third part defines what will be if the values after comparison get True or False.

Step 1: Enter the formula in cell C4.

=FLOOR(B4, IF(B4>0, 0.01, -0.01))

Formula Breakdown

Firstly, in the inner function which is IF(B4>0, 0.01, -0.01), retrieve the value from the given numbers up to two decimal places. If the value in B4 is greater than 0 which is positive then it will return 0.01 other than -0.01.

Then using the FLOOR function again we are passing the cell number which is B4 and the return value of the IF function which will be used as significance here.

Enter the formula using FLOOR and IF function in cell C4

Step 2: Copy down the numbers up to cell C10.

Copy down the numbers up to cell C10

Conclusion

These are the ways to show numbers up to two decimal places without rounding in Excel. I have shown all the methods with their respective examples. Also, I have discussed the fundamentals of these functions and the most commonly used format codes of this function. If you have any other method of achieving this then please feel free to share it with us.


Further Readings

Md. Abdullah Al Murad

Hello! Welcome to my Profile. Currently, I am working and researching Microsoft Excel, and here I will be posting articles related to this. My last educational degree is BSc, and my program was Computer Science and Engineering from American International University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. Always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo