Some functions in Excel can be used to give the sheet a formal look. In this module we will look at two such functions. ROUND is a Math & Trig Function that can be used to round off decimal numbers to nearest whole number and PROPER is a Text Function is a function that capitalizes the first letter of each word in the cells.
ROUND Function in Excel
Sahana has a sheet that has a list of average work done by the employee per day. She wants to convert these to a whole number so that she can set a better daily goal for the group of employees. This function will come of use to her now.
Syntax:
ROUND(number,num_digits)
Here, ‘number’ is the cell reference which contains the number to be rounded off to and ‘num_digits’ is the number of decimal places the number should be rounded to.
She can use the following formula.
=ROUND(B2,0)
to get her whole numbers with no decimal places at all. She can then drag the formula to apply in the other cells. The results will appear as follows.
ROUND follows these rules while rounding:
- The function rounds off any digit below the number 5 to a place lower than its value.
For Example, 14.32 will be rounded off to 14.3 and subsequently to 14.
- The function round off any digit above the number 5 to a place greater than its value.
For Example, 14.67 will be rounded off to 14.7 and subsequently to 15.
If the num_digits value is a negative number, then the number is rounded to the nearest tens, hundreds, thousands, etc. place. For example, 15.6 if rounded off with a formula like =ROUND(B2,-2) the result displayed will be 20.
Alternatively, clicking on the ‘ROUND’ option from ‘Math & Trig’ option in the ‘Function Library’ of ‘Formula’ menu and giving the cell reference and number of digits in the dialogue box that appears will also insert this function.
She should click ‘OK’ once done.
PROPER Function in Excel
Sarath has the following sheet.
His boss tells him that it looks unprofessional as it doesn’t have the names of the people in the correct format. Instead of changing the content of each cell, Sarath can just use the PROPER function to bring it to the correct format.
Syntax:
PROPER(text)
where ‘text’ refers to the cell reference in which the data to be modified is present. He can use the following formula.
=PROPER(A2)
and drag it to the rest of the cells. The result will appear as follows.
If the text given in the cell was all in the uppercase, the result will still be the same with only the first letter capitalized.
If instead of text, Sarath had a string of numbers, the PROPER function will leave it untouched and not bring any changes to it.
Alternatively, clicking on the ‘PROPER’ option from ‘Text’ option in the ‘Function Library’ of ‘Formula’ menu and giving the cell reference in the dialogue box that appears, will also insert this function.
He should click ‘OK’ once done.