Start networking and exchanging professional insights

Register now or log in to join your professional community.

Follow

Which formula in excel I can use to control the decimals digits for the numeric values?

user-image
Question added by Ahmed Maher allam , Senior IT Consultant & System Admin (Lecturer & Trainier) , KGL
Date Posted: 2015/06/11
Mohammad Basheer abu ahmad
by Mohammad Basheer abu ahmad , مشرف كمبيوتر ، الإشراف على الموظفين , قطاع عام

Round Function

1- Select - Formulas - from the lists

2- Choose - Insert Function - Option

3- Select a category - Math & Trig - from function box

4- click on - Round Function -

 You can select also - RoundUp or RoundDown - Functions to control decimals digits

Vinod Jetley
by Vinod Jetley , Assistant General Manager , State Bank of India

Many times I want a description for my data. One approach is to put the description—a simple text string—near the cell containing the data that needs describing. For instance, a numeric value could go in cell B3, and the unit description in cell C3, which read together may be something like "3.27 miles."

Another approach is to put the description text and the numeric value together. Creating text strings easily accomplishes this feat. Here's a very simple example that displays "1 +1 is2."

="1 +1 is " &1+1

The quotation marks are important. By making the text string part of a formula, you can combine the description and the value within one cell.

The disadvantage of this approach is formatting the value takes more effort; since the result is a text string, numeric cell formatting does not apply. For example, consider the above formula and the need to display two decimal places. One might naturally display the Format Cell dialog box and then choose a Number format that has two decimal places, but the results would not change. (Remember, the result of the formula is text, not a number.)

To affect the value formatting, use the TEXT function. To force the above results to display the value to two decimal places, use the following formula.

="1 +1 is " & TEXT(1+1, "0.00")

The different formats you can use with the TEXT function have been covered in other issues of ExcelTips, and you can also find more info in Excel's Help system. Here's an example that displays "Today is " along with today's date. Enter the following formula in some cell:

="Today is " & TEXT(NOW(),"dddd, mmm dd, yyyy")

Again, the quotation marks are important, as you are constructing a text string.

Helmy Mohamed
by Helmy Mohamed , SalesAgent , LightClub

you'll find a button to increase a decimal digit and another one to decrease it in the toolbar.

 

or 

 

1- Right click on the desired cell and choose "Format Cell" 

2- Select "Number" then Number.

3- Set the Value of Decimal Places as you want

 

or use this function

 

=Round(number, decimal places number)

 

Note: decimal places number is a positive when you want the round to be after the decimal point or negative if you want it before the point.

Nasir Hussain
by Nasir Hussain , Sales And Marketing Manager , Pakistan Pharmaceutical Products Pvt. Ltd.

Out of  my expertise........... sorry for inconvenience. I am with the answer of experts........................ 

Emad Mohammed said abdalla
by Emad Mohammed said abdalla , ERP & IT Software, operation general manager . , AL DOHA Company

Rounding Numbers with the ROUND Function.   

Roundng Numbers Overview

The ROUND function can be used to reduce a number by a specific number of digits on either side of the decimal point.

In the process, the final digit, the rounding digit, is rounded up or down based on the rules for rounding numbers that Excel Online follows.

The ROUND Function's Syntax and Arguments

A function's syntax refers to the layout of the function and includes the function's name, brackets, and arguments.

   

The syntax for the ROUNDDOWN function is:

= ROUND ( number, num_digits )

The arguments for the function are:

number - (required) the value to be rounded

num_digits - (required) the number of digits to leave in the value specified in the number argument- if num_digits is a positive value, it only rounds digits in the number argument that are located to the right of the decimal point- if num_digits is negative, it only rounds digits in the number argument that are located to the left of the decimal point

Examples:

  • if num_digits is set to1 the function leaves only one digit to the right of the decimal point and rounds it up or down
  • if num_digits is set to2 the function leaves two digits to the right of the decimal point and rounds the last one up or down
  • if num_digits is set to -1, the function will remove all digits to the right of the decimal point and round the first digit to the left of the decimal point up or down to the nearest multiple of10
 
  • if num_digits is set to -2, the function will remove all digits to the right of the decimal point and round the first and second digits to the left of the decimal point up or down to nearest multiple of100

Round Numbers in Excel Online Example

As seen in the image above, this example will use the ROUND function to round the number324.237 in cells A1 up or down four different ways - depending upon the value for the num_digits argument.

The four versions of the function will be:

cell B1: =ROUND(A1,1)cell B2: =ROUND(A1,2)cell B3: =ROUND(A1,-1)cell B4: =ROUND(A1,-2)

Entering the Data

Enter the number324.237 into cell A1 as shown in the image above.

Entering the First ROUND Function

Excel Online does not use dialog boxes to enter a function's arguments as can be found in the regular version of Excel. Instead, it has an auto-suggest box that pops up as the name of the function is typed into a cell.

  1. Click on cell B1 to make it the active cell - this is where the results of the first ROUND function will be displayed
  2. Type the equal sign ( = ) followed by the name of the function round
  3. As you type, the auto-suggest box appears with the names of functions that begin with the letter R
  4. When the name ROUND appears in the box, click on the name with the mouse pointer to enter the function name and open round bracket into cell B1
  5. With the cursor located after the open round bracket, click on cell A1 in the worksheet to enter that cell reference into the function as the number argument
  6. Following the cell reference, type a comma ( , ) to act as a separator between the arguments
  7. After the comma type a one "1 " as the num_digits argument to reduce the number of decimal places for the value in A1 to one
  8. Type a closing round bracket " ) " to complete the function's arguments
  9. Press the ENTER key on the keyboard to complete the function
  10. The answer324.2 should appear in cell B1
  11. When you click on cell B1 the complete function = ROUND ( A1 ,1 ) appears in the formula bar above the worksheet

Entering the ROUND function in cells B2 to B4

To enter the ROUND function into each of cells B2 to B4 with a different num_digits argument:

  1. Click on cell B2 to make it the active cell and repeat steps2 to9 above, except use   for the num_digits argument to reduce the number of decimal places for the value in A1 to two - the answer324.24 should appear in cell B2.
  2. Click on cell B3 to make it the active cell and repeat steps2 to9 above, except use   -1  as the num_digits argument to remove all decimal places and round the number to the nearest multiple of10 - the answer320 should appear in cell B3.
  3. Click on cell B4 to make it the active cell and repeat steps2 to9 above, except use   -2  as the num_digits argument to remove all decimal places and round the number to the nearest multiple of100 - the answer300 should appear in cell B4.

The ROUND function and Calculations

Unlike formatting options that allow you change the number of decimal places displayed without actually changing the value in the cell, the ROUND function, alters the value of the data.

 

Using this function to round data will, therefore, affect the results of calculations

Firas Shahadi
by Firas Shahadi , Human Resources Director , SAUDI UNION

You have2 options:

option1 to display only number of digits for the decimal part. You can use the Format cell to control how the number will appear n the cell.

 

Option2 to control the cell content to the exact number of decimal digits (not only display) you can use the Round() function. Pretty slight difference but very confusing for a lot on people.Regards

د Waleed
by د Waleed , Management - Leadership-Business Administration-HR&Training-Customer Service/Retention -Call Center , Multi Companies Categories: Auditing -Trade -Customer service -HR-IT&Internet -Training&Consultation

Mr Mohammed and Emad gave enough info... Thank You

More Questions Like This