Excel ROUND, ROUNDUP and ROUNDDOWN Function - Combination of VLOOKUP with ROUND in Microsoft Excel

Friends,

While dealing with data analysis sometimes we need to take only the integer part i.e., without the fraction from data i.e., rounded data or upto two decimal numbers for currency and ease of understanding by human beings. One method to get the rounded data is that we just click on the decrease decimal and we can see only the integer part but actually the decimal part or the fraction is not deleted, it is hidden, to get the actual integer part we need to use Microsoft Excel ROUND function.

Today we shall discuss about three functions ROUND, ROUNDUP & ROUNDDOWN. ROUND, ROUNDUP and ROUNDDOWN Function - Combination of  VLOOKUP with ROUND

You can use this three function in nested conditions i.e., with other function to create a compound formula. One example is given below, it is ROUND function with VLOOKUP function.

How to use the ROUND Function

Description

The Microsoft Excel ROUND function returns a number rounded to a specified number of digits.

The ROUND function is a built-in function in Excel that is categorized as a Math/Trig Function. As a worksheet function, the ROUND function can be entered as part of a formula in a cell of a worksheet.

Syntax

The syntax for the ROUND function in Microsoft Excel is

=ROUND(number, num_digits)

Parameters or Arguments

number

The number to round.

num_digits

The number of digits to round the number to.

Example

Let's look at some Excel ROUND function examples and explore how to use the ROUND function as a worksheet function in Microsoft Excel:

From the syntax of ROUND function we can use this function as =ROUND(C2,0) as shown in the below picture.

Round Function in Excel

Use of ROUND with VLOOKUP

Use nested ROUND function with VLOOKUP, the formula is like =ROUND(VLOOKUP($V2,$B$1:$C$10,2,0),0) as shown in the below picture.

ROUND and VLOOKUP Function

The use of ROUND function is very easy and simple, further you can select how many digits you need after the point or decimal by specifying num_digits like =ROUND(C2,2). This ROUND function will took the nearest value after num_digits, for example if we use ROUND function to  7.14885714 number by 2, the result will be 7.15 and if we apply ROUND function to 5.28371428, num_digits by , it will be 5.28.

How to use the ROUNDUP Function

Description

The Microsoft Excel ROUNDUP function returns a number rounded up to a specified number of digits. (Rounds away from 0.)

The ROUNDUP function is a built-in function in Excel that is categorized as a Math/Trig Function. As a worksheet function, the ROUNDUP function can be entered as part of a formula in a cell of a worksheet.

Syntax

The syntax for the ROUNDUP function in Microsoft Excel is:

=ROUNDUP(number, num_digits)

Parameters or Arguments

number

The number to round up.

num_digits

The number of digits to round the number up to.

Example

ROUNDUP function in Excel

How to use the ROUNDDOWN Function

Description

The Microsoft Excel ROUNDDOWN function returns a number rounded down to a specified number of digits. (Always rounds towards 0, just the reverse of ROUNDUP function).

The ROUNDDOWN function is a built-in function in Excel that is categorized as a Math/Trig Function. As a worksheet function, the ROUNDDOWN function can be entered as part of a formula in a cell of a worksheet.

Syntax

The syntax for the ROUNDDOWN function in Microsoft Excel is:

=ROUNDDOWN(number, num_digits)

Parameters or Arguments

number

The number to round down.

num_digits

The number of digits to round the number down to.

Example

Please follow the below picture, this function's working will be clear if you practice it.

ROUNDDOWN function in Excel

As I've shown one example above, you can always use these functions within  or with another function to make compound formula. 

Thank you for reading this article...

Drop Down List In Excel - Three Methods, Static and Dynamic - Delete Drop Down List

Sometimes we need to collect data from end-users in a specific format without giving them authority to write anything not related to the topic. Sometimes we also need to add data from some selective options in a specific Excel format. It is because if the data collected from the end-users varies on same topic and on same results then it becomes tedious job to work with that same data written differently. Therefore, we need some tool to make our format or more specifically the cells of an excel file to be selected from a drop down list.

So, friends, today our topic is how to create and utilize Drop-Down List in Microsoft Excel. It is very interesting because this technique will not allow users to enter random data in an uniform excel format. We shall create some constraint and validation rules to enter data in the Excel file.

Let me elaborate the requirement by the help of an example so that you can use it in proper place, suppose you have  an excel file with few questions and it needs to be filled by some different people in your country like, Do you like our sales unit ? Which is the best product in your view ? Which product needs up-gradation ? Will you use our product if we improve it ?.

If you analyze carefully the basic questions here, you can easily understand that these questions has specific answers, like 'Yes', 'Product 2', 'Product 5' etc., but in real life you will get numerous different types of answers and you have to work on this basic data only to rectify.

Here, we use constraints by using Drop down list. Hope you have understand the need and now we'll move to the discussion.

Description

Excel drop-down list or drop down box or combo box, is used to enter data in a spreadsheet from a pre-defined items list. The main purpose of using drop down lists in Excel is to limit the number of choices available for the user. Apart from that, a dropdown prevents spelling mistakes and makes data input faster.

How to create an Excel Drop-down list

There are two types of Drop-down Lists in my opinion, Static and Dynamic, both are equally useful. Both are very easy to use. There are some steps to follow and we are done.

Where you need to use such an option list that might change over time use Dynamic and if the list might not change in future use Static method.

1.     Select a cell or range for your drop-down list.

Start by selecting a cell or cells where you want a drop-down box to appear. This can be a single cell, a range of cells or the entire column. If you select the whole column, a drop down menu will be created in each cell of that column, which is a real time-saver, for example, when you are creating a questionnaire. You can even select non-contiguous cells by pressing and holding the Ctrl key while selecting the cells with the mouse.

2.     Use Excel Data Validation to create a drop-down list.

On the Excel ribbon, go to the Data tab > Data Tools group and click Data Validation.

3.     Enter the list items and choose the options.

In the Data Validation window, on the Settings tab, do the following:

a)     In the Allow box, select List.

b)    In the Source box, type the items you want to appear in your drop-down menu separated by a comma (with or without spaces).

c)     Make sure the In-cell drop down box is checked; otherwise the drop-down arrow won't appear next to the cell.

d)    Select or clear the Ignore blank depending on how you want to handle empty cells.

e)     Click OK and you are done!

End-users can simply click an arrow next to a cell containing a dropdown box, and then select the entry they want from the drop down menu no deviation will occur.

4.     If you want to make this a more beautiful, you can add some information in the cells in the Data Validation Input message.

It is very simple add the message title (I wrote 'Note' in the image) and few words in the input message box.

IMAGE 7

5.     You can enter Error Alert also, if users tries to enter data using his/her own words there will be an error message.

Select Style, Enter Title and Error Message and done.

This was the Static Method, most of the job will be done through this method but Dynamic Drop down list is also has its' own importance where you need to update the list periodically.

Now my view is that the Dynamic Drop down list can be of two types, using two methods, Using Named List (Using Name Manager, already discussed in my previous article) and by creating Table..        

Dynamic Drop down list Using Name Manager:

Name Manager is a simple tool in excel which is used to name some collection of data, shortcut key is Ctrl+F3.

Follow the following steps to use Name Manager :

i.   Select the entire column.

ii.   Press Ctrl+F3, a new window will appear Name Manager

iii.  Click on New and this will open another small window New Name

iv.  Enter a name for this column, mine is Drop_down_List.

v.   Select the Scope whether it will be applicable to Sheet1 or Workbook.

vi.  Notice Refers to box is taking the selected column.

vii. Click OK and close the Name Manager window.

Now select the cell(s) where you want to put Drop Down list. Click Data from the menu, select Data Validation, click on Data Validation, Data validation window will appear.

Select List and in source box type '=' and your column name which you given in Name Manager, mine is "=Drop_down_list".

Do not uncheck any check box.

And you are done.

Dynamic Drop down list Using Table & Name Manager:

The concept of using Table in Drop Down List is using 

the property of the table of inheritance (I can say because) whenever you put a data below the table in normal cell, as you hit the enter it is converted to a part of table and if we use this property for list item generation it will be automatically added to Drop Down List. Lets' see how to do it.

i.   Select the Data to be listed.

ii.  Press Ctrl+T, this will open a new window, Create Table.

iii. Click OK.

iv.  Name this table Using Name Manager Ctrl+F3 (mine is Table_Test)

v.   Now select the cell(s) where you want to put Drop Down list. Click Data from the menu, select Data Validation, click on Data Validation, Data validation window will appear.

vi.  Select List in Allow box and type your table name in Source box.

vii. Hit Enter and you are done.

One more thing I would like to inform you additionally that while appling Data Validation rule to cell(s), either you can select all the cells or you can select one cell first and then drag them, this will work fine.

There is another Dynamic method is there using formula which I shall discuss in another article.

How to Delete Drop Down List ?

The procedure to delete a Drop Down List is also important because you might encounter problem while appending list or wrongly data entered or modification require in the excel format.

It is very easy. Go to Data Validation, Click on Clear All and click OK.

PROPER function Capitalize Each Word

Hi friends,

In my last article I discussed about the two functions UPPER and LOWER and at the end of that article I formulated one compound formula using five functions UPPER, LOWER, LEN, LEFT and RIGHT to get the first letter capitalized of a given word. The formula was =UPPER(LEFT(D3,1))&LOWER(RIGHT(D3,(LEN(D3)-1))).
It's a lengthy complicated function to get the first alphabet capitalized and the rest in lower case. Now if I need to convert a name with first name and last name then my formula will be more complicated and lengthy. To ease this task Microsoft Excel has provided one function PROPER. By using this function we can easily reduce our formula as well as time.

Lets' discuss about this function PROPER Function.

 Description

The Microsoft Excel PROPER function sets the first character in each word to uppercase and the rest to lowercase.

The PROPER function is a built-in function in Excel that is categorized as a String/Text Function. PROPER function can be entered as part of a formula in a cell of a worksheet.

Syntax

The syntax for the PROPER function is very simple

=PROPER(text)

Parameters or Arguments

text

Text whose first character in each word will be converted to uppercase and all remaining characters converted to lowercase. It may be one or more than one word.

How to use PROPER  function?

Let's look at example of Excel PROPER function, how to use PROPER  function.

Please follow the below image, you just need to type '=' PROPER and press tab to get opening bracket and select the cell you need to get rectified, hit enter and done.

Proper Function in Excel

One simple function will change combination of five functions. Have look at the function marked in yellow colored in 'F' column, such a long complicated nested function - one simple function PROPER replaces the entire formula and its better then this complicated formula. Isn't it a useful function ? Yes it is.

I hope you have enjoyed this article also, thank you for reading.

Use UPPER LOWER Function in Excel - convert text from upper to lower case or from lower to upper case

Hi friends,

Today in this article I am going to show you two Microsoft Excel functions that are very useful if you need to convert texts from one case to another i.e., from upper to lower and vice versa. We'll discuss about these two functions and we shall use these function combined to get desired result by the help of examples. So, let us discuss all these three functions step by step, one after another.

How to use the UPPER Function

Description

The Microsoft Excel UPPER function allows you to convert all text to all uppercase.

The UPPER function is a built-in function in Excel that is categorized as a String/Text Function. The UPPER function can be entered as part of a formula in a cell of a worksheet.

Syntax

The syntax for the UPPER function in Microsoft Excel is:

=UPPER(text)

Parameters or Arguments

text

The string that you wish to convert to uppercase.

Example UPPER Function

Let's look at some Excel UPPER function examples and explore how to use the UPPER function as a worksheet function in Microsoft Excel:

Just type '=' sign and UPPER and then select the cell that you want to convert to upper case.

This function works fine in with numbers, spaces, special characters etc. Very simple function, the below picture will make it very clear.

How to use the LOWER Function

Description

The Microsoft Excel LOWER function converts all letters in the specified string to lowercase. If there are characters in the string that are not letters, they are unaffected by this function.

The LOWER function is also a built-in function in Excel that is categorized as a String/Text Function. The LOWER function can be entered as part of a formula in a cell of a worksheet.

Syntax

The syntax for the LOWER function in Microsoft Excel is:

=LOWER(text)

Parameters or Arguments

text

The text/string to convert to lowercase.

Example LOWER Function

This function also works similar to UPPER function only the case is reversed, lower case. Let's look at Excel LOWER function example and explore how to use the LOWER function in Microsoft Excel:

Just type '=' sign and LOWER, hit tab in your keyboard and then select the cell that you want to convert to lower case.

This function also works fine with numbers, spaces, special characters etc. Very simple function, Lets' have a look at the below picture.

Now we'll try to change names in exact case from random upper or lower case, i.e., the characters in the name field could be in any case but  we'll capitalize the first character and the rest in lower case. So, lets' begin.

In the below example I've taken few names in first column and the next three columns are to convert it into upper, lower and in the last one to get the desired result, first character to be capitalized and the rest should be in lower case.

To get the output what I have done is first I converted the first character in capital letter using UPPER and LEFT function, then used LOWER and used LEN function to convert all the letters in lower case except the first one, and finally I combined these functions using '&' operator, you can use CONCATENATE function.

Lets break this example into step by step.
    1.    Use UPPER and LEFT function to convert first character into Capital Letter. Formula is =UPPER(LEFT(D3,1)) then
    2.    Get the remaining characters i.e., except first letter. Use RIGHT(D3,(LEN(D3)-1)), RIGHT function will take the characters from right side, LEN functions decides the number of characters in RIGHT function.
    3.    Cover it by LOWER function, formula now is            LOWER(RIGHT(D3,(LEN(D3)-1)))
    4.    Lastly, combine these two functions by & sign or use CONCATENATE function. The final formula is : =UPPER(LEFT(D3,1))&LOWER(RIGHT(D3,(LEN(D3)-1)))

Now, we have got one formula to capitalize the first character of a word, but what about a name space in between first and last name or middle name or one big paragraph and if we need to capitalize each and every first character of every word ?

Watch my next article on this problem. Hope you have enjoined a lot reading this article, thank you for reading...

Use TRIM Function in Excel to Remove Extra Spaces from Words Numbers Non printable Characters within a cell

In this article we shall discuss about the TRIM function in Microsoft Excel, sometimes working with data you might be noticed that some formula does not work although everything is right or proper . This is because some formula will not work in excel if there is extra space before a cell's data or after a cell's data (Leading or Trailing space) while trying to match columns or cells. Space(s) in excel is also a character which is not ignored by formulas. The problem with spaces are they are invisible to human eye, you need to take special care to detect them.

In this article we shall learn how to use TRIM function with some function combined or nested and will resolve or remove possible reasons of formula not working in Excel.

Description

Excel TRIM function returns a text value with the leading and trailing spaces removed. You can also use the TRIM function to remove unnecessary spaces between words in a string.

The TRIM function is a built-in function in Excel that is categorized as a String/Text Function.

Syntax

The syntax for the TRIM function in Microsoft Excel is:

=TRIM( text )

Parameters or Arguments

text

The text value to remove the leading and trailing spaces from.

Before going to several examples let us first look at nature of the problem with spaces.

Space Before or After a Data Creates Wrong Results

As you can see from the above image although both the texts are same in C and in D cell but when I ask excel to check whether these texts are equal or not, it replies me 'No' or 'False'. Human eye is showing these texts are same because we couldn't able to see the space before this text. Removing this extra space will resolve this problem. Or we can use TRIM function to remove the space.

Examples

Remove Spaces in Excel - Leading, Trailing, in between words

One more thing I would like to add here is that you can use LEN function to check the length of the text or contents in the cell and then you can count manually number of characters in that cell just to check it. 
Let me show you the working of TRIM function by the help of the below image.


To use TRIM function you need to type an '=' sign and then TRIM, press tab and move your cursor to the cell to trim. In the below picture I am showing you how to trim a word, spaces within words or spacing between words, spaces before numbers, spaces after numbers and both.

Use of TRIM Function in Excel

Please note the difference in number of of characters in the B column and in the E column, before and after use of TRIM function. It is very easy function but it can save your time of troubleshooting a problem in formula. Remember a trailing or leading spaces in any cell will make your formula inconsistent.

Remove Non printable characters

When you import data from external sources, it's not only extra spaces that come along, but also various non-printing characters like carriage return, line feed, vertical or horizontal tab, etc.

The TRIM function perfectly removes white spaces, but it cannot eliminate non-printing characters. Technically, Excel TRIM is designed to only delete value 32 in the 7-bit ASCII system, which is the space character.

To remove spaces and non-printing characters in Excel, use TRIM in combination with the CLEAN function. As its names suggests, CLEAN is purposed for cleaning data, and it can delete any and all of the first 32 non-printing characters in the in the 7-bit ASCII set (values 0 through 31) including line break (value 10).

Syntax of CLEAN

=CLEAN(text)

It removes all the non printable characters in a cell. Let us have an example. In the below example I have typed few non printable characters. This type of or other non printable characters you may get while importing data from other sources. In this situation what I have done is that I used CLEAN function to remove these non printable characters and cover it by TRIM function to remove the extra trailing of leading spaces. Please follow the below image to understand this small but useful function.

How to use of TRIM and CLEAN Function in Excel

Please note that yellow colored non printable characters, remove it by CLEAN and TRIM function. For the above example the formula is =TRIM(CLEAN(B3)).

I hope you liked this article, thank you for reading....