CODE

Friends,

In my last two articles, I have discussed about the CHAR Function and how to use CODE function. 

But as you already know CHAR function’s output is one character which is easily identifiable by humans.

If you type =CHAR(97) in a cell, it will return you small ‘a’. But there might be a question in mind how did I know that ASCII code 97 represents ‘a’ ?

Is there any function which will tell us the ASCII code of a given alphabet or number or some special character ?

Yes, Microsoft Excel has provided us one function which will display the ASCII code of given character or number or special character. The function is CODE().

CODE function in excel works just opposite of CHAR function.

Today we will discuss about the CODE function.

Syntax

The syntax for the CODE function in Microsoft Excel is very simple.

=CODE(text/number/special character)

Parameters or Arguments

text/number/special character

The specified character to retrieve the ASCII value for. If there is more than one character, the function will return the ASCII value for the first character and ignore all the characters after the first.

Returns

The CODE function returns a numeric value.

Example :

I will give you two examples.

First, I will enter a character and this function will return the ASCII value of it.

Second, a little confusing, if you enter any number to get the ASCII code (e.g., you need the ASCII value of 10) you will get another number(49).

Why ASCII value is a number if I enter a ‘Number’ ?

Because ASCII value stores any character in number format. In an ASCII file, each alphabetic, numeric, or special character is represented with a 7-bit binary number (a string of seven 0s or 1s). 128 possible characters are defined.  

CODE Function in Excel to give ASCII value

Hope you have enjoyed this article.

Thank you for reading…. 

Uses of CHAR Function & CHAR(10) NOT Working - Solved

In my last article, I have discussed about the CHAR function. This function returns the character from the ASCII code.

We learned about how to use CHAR Function ?

Now the second question is

Where to use CHAR function ?

Today I will give you few examples where to use CHAR function in Excel.

Case 1 :

Suppose you need to print the alphabet ‘A’ to ‘Z’ in column. The simple technique is you select a cell and type ‘A’, press enter type ‘B’ and so on. Like numbers you can not drag the fill handler and get the alphabets. Here you can implement CHAR function.

How to get alphabets by dragging fill handler ?

There are various methods to it but we will use CHAR function. So lets do it step by step

1.       You need to know the ASCII code of ‘A’

2.       The ASCII code for capital ‘A’ is 65 and small ‘a’ is 97. How do I know ASCII code of ‘A’ is 65 ? In my next article I will show you how you can get the ASCII value of any character. For this purpose ASCII code of  capital ‘A’ is 65.

3.       Type 65 in any cell (suppose the cell is A1) in Excel then drag it by fill handler till 90. (Because there are 26 alphabets available.)

4.       Now, type =char(A1) and press enter.

5.       You will see A is printed here.

6.       Now you can drag the cell by fill handler, it will show you the alphabets.

Case 2 :

You may be noticed that using double quotes sometimes are very confusing. For example I want to concatenate a string “The movie “ and the movie name “XXX” and the last string “was good”.

Now you can do this by typing the formula

="The movie """&A2&""" was good"

But you notice the double quotes are very confusing.

Therefore, we will use CHAR function to get an easily understandable formula like

="the movie "&CHAR(34)&A2&CHAR(34)&" was good"

Please compare these two formulas, you will easily find out which one is easy to use in Excel.

How to use CHAR Function in Excel

Case 3 :

Let suppose you got Address field like Address 1, Address 2 & Address 3. Now you need to concatenate it to print addresses in one cell.

Normally if you use CONCATENATE or CONCAT function you will get combined address but you have to insert spaces in between the address and then you need to COPY it and PASTE SPECIAL it. 

After that you should manually enter a line break to get the desired output.

Using CHAR function, you can do it very easily.

As shown in the below picture I have Address 1, Address 2 & Address 3, I have used CONCAT function to add this strings and used CHAR function with ASCII value 10 which gives us a ‘Line Break’. (For MAC use ASCI Code 13).

Formula : =CONCAT(A3,CHAR(10),B3,CHAR(10),C3)

CHAR(10) not working !!!

Kindly Note : Don’t get anxious if CHAR(10) don’t put any line break. The above formula works fine. You need to Click on Wrap Text button to get the result.

How to use CHAR Function in Excel

Like the above examples there are many formulae where you can use CHAR function. If any questions you are having in your mind, please feel free to contact me through Contact Us form.

Hope you have enjoyed this article.

Thank you for reading…. 

CHAR Function

Today I will discuss about a very small function but a very useful and user friendly function, the CHAR Function.

This small but powerful function returns the character from ASCII value. Let us first know what is ASCII Code very briefly then we will move to the CHAR function in detail.

What is the ASCII Code ?

ASCII (American Standard Code for Information Interchange) is the most common format for text files in computers and on the Internet. In an ASCII file, each alphabetic, numeric, or special character is represented with a 7-bit binary number (a string of seven 0s or 1s). 128 possible characters are defined.

Why do we need ASCII Code ?

When any key on a keyboard is pressed, it needs to be converted into a binary number so that it can be processed by the computer and the typed character can appear on the screen. A code where each number represents a character can be used to convert text into binary. One code we can use for this is called ASCII.

ASCII stores few special characters like @,#,$ etc., and numeric and alphabets. To sum up you can call ASCII code is such a code which stores all the available characters in a Keyboard in number form. 

If you are interested to know which number represents which character, you must use the CHAR function in Excel.

Syntax

The syntax for the CHAR function in Microsoft Excel is:

=CHAR(number)

Parameters or Arguments

number

A number from 1 to 255.

Returns

The CHAR function returns a string/text value.

How to use CHAR Function ?

As shown in the below image, write a number in any cell in Excel, in the next cell type ‘=CHAR(A1)’, press enter.

In the below image, I entered 100 in A1 cell and next to this cell I typed =cell(A1) which returns the character ‘d’. Therefore, we can conclude that the ASCII value of ‘d’ is 100.

How to Use CHAR Function in Excel

If you are interested to know characters that ASCII code represents you need to enter 1 to 255 in a column, then write the CHAR function and drag it. It will represent all the characters and their ASCII code.

For example, CHAR(10) represents a line break in Windows, CHAR(13) returns a line break in Mac.

CHAR(39) represents a single quote, CHAR(34) represents double quote. CHAR(149) represents a bullet.

In the next article I will show you how useful this function is !!!

Below I have given few useful ACSCII codes and their characters in a table format.

Some Useful ASCII Codes and the CHARACTERS they represent

Hope this article is enough for you to understand the CHAR function and ASCII Codes in Microsoft Excel. If any question arises, feel free to comment in the comment box below.

Thanks for reading…

6 Reasons why VLOOKUP is not working

VLOOKUP is not working !!! 

Don’t need to get panic. I have seen many persons complaining VLOOKUP not working or VLOOKUP not working with numbers or VLOOKUP not working properly or VLOOKUP not working between sheets or VLOOKUP not working for some cells etc.

You have tried a lot to fix it but all in vain. 

Calm down, there are very few reasons behind not working of VLOOKUP function.

In this section I will show you step by step how to detect where is the problem for not working of VLOOKUP. 

But before going I would like to review the SYNTAX of VLOOKUP once again.
=VLOOKUP( lookup_value, table or array, col_index_number, [range_lookup] ) 

Below is the few Check List to check why VLOOKUP is not working

1. Not Having 'Lookup_Value' in First Column :

If there is no LOOKUP VALUE (which you are searching for, the first parameter) is not available in the table or array from where you are getting or matching data, VLOOKUP will not work. It will display #N/A error.

Please watch carefully the below image. Here I want to match the employee column in the second table in the small table from the large table to get Sales Quantity of the given employees. Here I am getting #N/A error in the first row because “Employee 14” is not present in the first table.

This is a small example, therefore, what you need to do is search manually for the Lookup Value by pressing Ctrl+F in your Keyboard.

If Value is not found in the first table, then it’s OK, use IFERROR to mask the #N/A error.

2. Counting the Wrong Number of Columns for Col_index_number

Please recount the COLUMNS count from where you are fetching data, the first table

In this example I wrongly count the column number as 4 instead of  3 
=VLOOKUP(F4,B3:D14,4,0)

3. Extra Space or Characters

Please check for Extra spaces in your formula, it you have doubts use TRIM function to remove extra spaces. To learn more about TRIM click this.

4. Not Using FALSE for Exact Matching [Range_Lookup]

Although, sometimes VLOOKUP works perfectly but do not forget to use [Range_Lookup], False or 0 for exact matching and True or 1 for approximate matching. 

Its always better to make a good habit.

5. Forgetting Absolute References (F4) When Copying the Formula

While copy and paste VLOOKUP formula or just dragging the VLOOKUP formula you must put $ sign to restrict the column not to move down or side by side. This is called Absolute reference. 

There are two ways to do this, one you insert a $ sign manually or you can use F4 (Function Key) key. 

6. A new column has been inserted in the old table

In a previously calculated table where VLOOKUP has been used which was working, inserting a new column will create VLOKKUP unstable. 

To resolve this either you can recheck your VLOOKUP formula or you can use an additional function MATCH to be nested with VLOOKUP. Using MATCH with VLOOKUP will make your formula very dynamic.

Please click here to learn how to use MATCH function with VLOOKUP.


Hope your problem is resolved now. If not please mention your problem in the comment box.

Thank you for reading... 

AVERAGEIF

Hi Friends,

In my last article, I discussed about AVERAGE function in Microsoft Excel, a very basic statistical function (arithmetic mean).

But what if, if I need the average of some given numbers if some condition is met. Let me explain it with an example. 

Suppose I need the average of all the Sales and Target values of South zone from the data provided where North, East West and South zone’s figures are plotted one after another in consecutive rows (as given in the below picture).

Here I have two options, one, using IF function nested with Average or I need some special Function which will allow me to average out those numbers where one given condition met.Today, in this article, I will discuss about the built-in function in Excel which will provide me the average value of some number if certain condition is met. The function is AVERAGEIF.

Let us discuss about it today…

Description

The Microsoft Excel AVERAGEIF function returns the average (arithmetic mean) of all numbers in a range of cells, based on a given criteria.

The AVERAGEIF function is a built-in function in Excel that is categorized as a Statistical Function. The AVERAGEIF function can be entered as part of a formula in a cell of a worksheet.

Later, I will show you how to use this function nested with other functions.

Syntax

The syntax for the AVERAGEIF function in Microsoft Excel is very similar to SUMIF. If you want to know how to use SUMIF, please click in this link.

=AVERAGEIF(range, criteria, [average_range] )

Parameters or Arguments

range

The range of cells that you want to apply the criteria against.

criteria

The criteria used to determine which cells to average.

Note : If the Criteria is in text or if you are using any operator please use Double Quotes.

average_range

Optional. It is the cells to average. If average_range is omitted, it uses range as the value for this parameter.

Returns

The AVERAGEIF function returns a numeric value.

Example 1 :

How to use AVERAGEIF with TEXT/STRING data type ?

In the below image, I want to get the Average of the Sales & Target value of South Zone only. The formula for this is

=AVERAGEIF(B2:C15,"South",C2:C15)            For Target portion

=AVERAGEIF(B2:C15,"South",D2:D15)            For Sales portion

Please note I mentioned South within double quotes.

How to use AVERAGEIF with TEXT or STRING as Criteria

Example 2 :

How to use AVERAGEIF with OPERATOR like =, <, > etc. ?

In the same example, let suppose I need the AVERAGE of sales value where TARGET value is Greater than 150.

Note : Here the rage I provided to get the average is D2:E15, because I want my AVERAGE function to search for 150 in the Target column which is Column D and the average will come from the next column E which is the Sales column.

The below image will make it very clear.

How to use AVERAGEIF with OPERATORS as Criteria

Hope this article is enough for you to understand the AVERAGEIF function. If any question arises, feel free to comment in the comment box below.

Thanks for reading…