Share in Facebook


20 August 2018

AND Function


Today I will discuss about a logical function ‘AND’ which is widely used in Microsoft Excel with other Logical functions like ‘IF’ as well as other functions.









We had already discussed about this function while discussing IF function, i.e., nested IF with AND function but we will discuss it here in detail.

Sometimes you may face situations where you should take decision based on the output of two or more conditions return TRUE otherwise the decision will be another.

Let’s take an example of an Examination in three subjects, if any student gets the pass marks (50) in all (three) subject in a test then only he will be declared as ‘PASSED’ else he would be declared as ‘FAILED’.

So normally we say that if Harry, a student, gets 50 marks in Subject 1 and 50 marks in Subject 2 and 50 marks in Subject 3 then he has ‘PASSED’ the test.

What we are doing here is we are testing each subject one after another whether the marks obtained is greater than 50 or not, if marks of any subject are less than 50 then the student will be called ‘FAILED’.

Here we can use AND function.

So, let’s discuss about AND function.

The Microsoft Excel AND function returns TRUE if all conditions are TRUE. It returns FALSE if any of the conditions are FALSE.









The AND function is a built-in function in Excel that is categorized as a Logical Function.

Syntax

The syntax for the AND function in Microsoft Excel is very simple

=AND(logical1, logical2, ... )

Parameters or Arguments

logical1

The first condition to test whether it is TRUE or FALSE.

logical2,

Returns

The AND function returns TRUE if all conditions are TRUE.

The AND function returns FALSE if any of the conditions are FALSE.

The below image will make it more clear.

How AND function Works
How AND function Works


Example :

It is very easy to use AND function, type =AND( and then write the conditions separated by comma.

Suppose we have two numbers 101 & 111 in two cells C4 & D4, now if we write AND function the formula will be =AND(C4>100,C5>100) which will return TRUE because both the numbers are greater than 100.

If we write any number less than 100, the output will be FALSE.

This was simple,

now we will use this function with IF function, suppose we need to print “OK”, if both the values are greater than 100 and “Not OK” if any of the value is less than 100.

We will do it in simpler way, we will add IF function in the beginning of the above formula
=AND(C4>100,C5>100)
and the formula will be
=IF(AND(C4>100,C5>100),"OK","Not OK").

In same way, you can use AND function in other function (like SEARCH, FIND etc.) to make more complex formula as per requirement.

In the next article, we will discuss about OR function and there will make some more complex formula using AND & OR functions.

I hope you have enjoyed this article. Take care…
Thank you for reading….

03 August 2018

TEXTJOIN Function


Today in this article I will discuss about a new excel function TEXTJOIN, an advanced version of CONCAT or CONCATENATE.

As the name indicates it is used to join texts or strings from more than one cell and you can use a separator if require. It works in the same manner the function CONCAT or CONCATENATE works.
One obvious question arises in our mind that if CONCATENATE & CONCAT is available in Microsoft Excel then what is the justification to introduce another function which works same, joining texts ?

There are differences between these three functions although they are used to get the same result.
Let’s discuss the function.








Description

The Microsoft Excel TEXTJOIN function allows you to join 2 or more strings together with each value separated by a delimiter.

The TEXTJOIN function is a built-in function in Excel categorized as a String/Text Function and TEXTJOIN function allows you to join 2 or more strings together with each value separated by a delimiter.

Syntax

The syntax for the TEXTJOIN function in Microsoft Excel is
=TEXTJOIN(delimiter, ignore_empty, text1, text2, ...)

Parameters or Arguments

delimiter
It is your separator, string or a single character inserted between each text value in the resulting string. Most commonly, a comma or space character or a hyphen.

ignore_empty
Determines whether empty values are included in the resulting string.

TRUE ignores empty values and FALSE includes empty values in the result.

This is most interesting and useful parameter in TEXTJOIN function compared to CONCAT or CONCCATENATE function, there was no such scope to avoid blank cells. Our example will clarify the usefulness of this parameter.




text1, text2, ... text_n
The strings that you wish to join. There can be up to 252 strings that are joined together.

Returns
The TEXTJOIN function returns a string/text value.

Note : This function is only available to Excel 2016, not before this version.

Example : 

Suppose we  need to join the characters as shown in the below table. There are two functions CONCAT and TEXTJOIN.

TEXTJOIN Function in Microsoft Excel
TEXTJOIN Function in Microsoft Excel


If we use CONCAT function the formula will be like :
=CONCAT(A6,",",B6,",",C6,",",D6,",",E6)

whereas if we use TEXTJOIN function the formula becomes :
=TEXTJOIN(",",TRUE,A5,B5,C5,D5,E5)

Very simple, lets analyze these two functions. As you are aware CONCAT is simple, type =CONCAT and an opening bracket then select the CELLs and after one selection of cell you must type your separator (for my case it is “,”). After you complete selecting the range press enter and you are done.

In TEXTJOIN function what you need to do is type =TEXTJOIN and an opening bracket then

1.   First type your SEPERATOR (may be comma or a hyphen)

2.   Put a comma, you will get two options,

a.   TRUE – Ignore empty cells
b.   FALSE – Include empty cells

TEXTJOIN Function in Microsoft Excel
TEXTJOIN Function in Microsoft Excel


3.   These options are self-explanatory, if you want to ignore the empty cells, it will not be displayed including the SEPERATOR. But if you select the FALSE statement then this cell content will be counted and a separator will be visible which CONCAT function normally does.

4.   Now select the ranges you want to join.

From the above example, we can notice that the using TEXTJOIN is much easier than the usual CONCAT or CONCATENATE function.

Difference between CONCAT or CONCATENATE and TEXTJOIN

1.   you don’t to type the separator repetitively but if you need two different separators then this function will not help, you must use CONCAT function.

2.   Unlike CONCAT function you are provided by an option to exclude or include the blank cells. If there is a blank cell and you use CONCAT function the output gives us TWO SEPERATOR one after another. But if you use TEXTJOIN function this problem will not occur.

3.   The most useful parameter is the RANGE selection. There is no way to select the whole range in a row or column using CONCAT or CONCATENATE function. The beauty of TEXTJOIN is that if the data is available in a row or column you can easily provide the range as we do for other functions like SUM function, the formula then becomes, =TEXTJOIN(",",TRUE,A5:E5).

4.   TEXTJOIN allows to use the ARRAY within this function, I will discuss it latter in another article.







I hope you have enjoyed this article.
Thank you for reading….

20 July 2018

SMALL function


In this article I will discuss about a very tiny function, SMALL(). As the name of the function indicates it is used to find or display the n’th smallest number.

Please note it should not be confused with the MIN function. MIN function returns the smallest number from a given range, SMALL function also do the same but the difference is that you can specify the n’th smallest number to display. For example, suppose given a number series as 24, 65, 33, 89; and I use MIN function to get the smallest number from these numbers, I will get 24 as the smallest number.








But, suppose if I need the second smallest number out of these numbers then what should I do ?

The answer is 33, the second smallest number out of these numbers 24, 65, 33, 89. It is easily identifiable by judging these numbers but suppose there are one thousand numbers and out of these we need the second smallest number; it is difficult.

The working of SMALL function is very similar to LARGE function, the only difference is LARGE function displays the largest data among set of numbers or array whereas the SMALL function displays the smallest number.

Here we will use the function SMALL. Let’s discuss the function.

Syntax :

=SMALL(array, k)

Parameters or Arguments

array

A range or array from which you want to return the nth smallest value.

k (nth_position)

The position from the smallest to return.

Example :

Let suppose we have four Zone’s target as shown below; now, if you need to find out that Zone which has the second smallest target value, you must use the SMALL function. The below picture will clarify the this.






How to use SMALL function
How to use SMALL function


Hope this article was helpful. 
Thank you for reading... Take care...

02 July 2018

Count Blank Cells - COUNTBLANK Function


Today in this article I will discuss about the excel function COUNTBLANK, a very small inbuilt function in Microsoft Excel.




In the last article, we discussed about How to Count Not Blank Cells in Excel.

In this article, we will learn How to Count Blank Cells, in some compound formula it becomes necessary to learn these small functions to simplify the existing excel formula.

Let us first discuss about COUNTBLANK then we will discuss where can we use it and how we can implement this function.

Description

As the name suggest the Microsoft Excel COUNTBLANK function counts the number of cells that are empty.

Note : As COUNTBLANK count the cells that are blank, if any space is present in any cell it will not be counted because space is also a character and space ‘alone’ is not visible to bare eyes. Therefore, it is better to use TRIM function while using COUNTBLANK Function if you are not sure that all visible empty cells are not containing any ‘space’.

Syntax

The syntax for the COUNTBLANK function in Microsoft Excel is:
=COUNTBLANK(range)

Parameters or Arguments

range

The range of cells to count empty cells.

Returns

The COUNTBLANK function returns a numeric value.

How to use COUNTBLANK Function in Excel ?

1.   Select a cell, then

2.   Type ‘=’ sign in that cell or in the formula bar

3.   Type ‘COUNTBLANK(‘ or type COU, a drop down list will appear, select COUNTBLANK, press TAB button

4.   Select the range or alternatively you can type the range as we do in the basic SUM function. Hit enter.






5.   Done.

How to use COUNTBLANK function to count Blank cells in Excel
How to use COUNTBLANK function to count Blank cells in Excel


How to use COUNTBLANK & COUNTA function to count Blank and Not Blank cells in Excel
How to use COUNTBLANK & COUNTA function to count Blank and Not Blank cells in Excel


We can use COUNTBLANK nested with other functions like INDEX or SUMPRODUCT, we will discuss it in another article in details with examples.


I hope this article is informative & you enjoyed this article.
Thank you for reading…


24 June 2018

Count Not Blank Cells - COUNTA Function


Today in this article I will discuss about the excel function COUNTA, a very small inbuilt function in Microsoft Excel.






Let us first discuss about this function then we will discuss where can we use it and how we can implement this function.

Description

As the name suggest the Microsoft Excel COUNTA function counts the number of cells that are not empty as well as the number of value arguments provided.




Syntax

The syntax for the COUNTA function in Microsoft Excel is:

=COUNTA(value1, value2, ...)

Parameters or Arguments

value1, value2, ...
Each argument can be a range, a cell or a value. There can be up to 255 arguments.

Returns

The COUNTA function returns a numeric value.

Note : Please remember this function returns not empty cell count, therefore, a blank space will also be counted. I strongly recommend to use TRIM function (if the data is very big) before applying COUNTA or use TRIM nested.






How to use COUNTA function ?


1.   Select a cell, then

2.   Type ‘=’ sign in that cell or in the formula bar

3.   Type ‘COUNTA(‘ or type COU, a drop down list will appear, select COUNTA, press TAB button

4.   Select the range or alternatively you can type the range as we do in the basic SUM function. Hit enter.

5.   Done.

6.   This is simple. Now to select multiple columns in a single function you need to put a comma then repeat the step 4

COUNTA Function
COUNTA Function


Where to use COUNTA ?


Examples of COUNTA function :


Everybody needs a tracker to maintain tasks. We first schedule our tasks and then complete these one after another and when one task is completed we mark a tick or ‘OK’ or some other notation to track how much closer we are to complete the overall task.

Here I have given a fictitious task tracker of a day, if any task is completed I mark it as ‘Done’.


COUNTA Function
COUNTA Function



We can use COUNTA nested with other functions like INDEX or SUMPRODUCT, we will discuss it in another article.

I hope this article is informative & you enjoyed this article.
Thank you for reading…



10 June 2018

Find & Replace - Search By Color Format and Wildcard Search


In this article, we will discuss about the search methods in 'Excel FIND & REPLACE'. The FIND & REPLACE option in Excel can do lot of things not only to FIND and then (if necessary) REPLACE but also you can search your ‘search items’ by their color, cell formatting, you can even use wild characters like ? or *.






The shortcut is Ctrl+F to open the FIND window and Ctrl+H to open the FIND & REPLACE window, the title of both the window is FIND & REPLACE.

You can also get this window from menus in the ribbon, you need to click in the Home tab, under Editing you will see an icon of Magnifying Glass, click in this icon, a drop-down menu will open and you will find these two options, FIND and REPLACE.

Find and Replace from Menu
Find and Replace from Menu
Find and Replace from Menu
Find and Replace from Menu


You will get the window of FIND & REPLACE as below.

 Find and Replace Window
Find and Replace Window


How to use FIND & REPLACE ?


As described above use either shortcut or from the menu click get the window FIND & REPLACE, if you want to find some item, type the item in the input box named as ‘Find what:’, press enter or click Find Next button. 

As soon as you click the Find Next button, the search term if available will be selected i.e., the cell containing this search term will be selected, if not available a warning window will appear saying item not found.

Error Message when Search Term not found
Error Message when Search Term not found






To replace a string or any other character we use the Replace tab in the Find and Replace window, you can get this option by the  using shortcut Ctrl+H or from menu or by clicking the Replace tab available in the FIND and REPLACE window. It displays two inputs boxes, ‘Find what:’ & ‘Replace with:’

In the first input box type that you want to replace, in the second input box type the replacement of it, now to replace one by one click Replace button and to replace all the matching text or characters at a time click Replace All button.

We all know these methods very well, I hope, but in the next paragraphs you will find the real power of FIND & REPLACE option in Excel.

The OPTIONS Button in the FIND & REPLACE window : Advanced Search

You might notice that there is an Options button above the Close button. If you click this button, the FIND & REPLACE window will expand and will show you few more options as shown in the below picture.

Options Button in Find and Replace
Options Button in Find and Replace


The available options are :

v     Match Case : Check (tick) this option if you want to find the cells that matches with the search term provided, it might be a part of the cell content.

v     Match entire cell content : Check this option to find the cell that has exactly same data as provided by the search term

v     Within : Drop down menu : By default Workbook is selected, if you want to search in the current Sheet where you are working, select from the drop down menu.

v     Search : Drop down menu : Here you implicitly instructs Excel to search by columns or rows, we rarely use this option, for very large and big data we may use it to reduce the search time.

v     Formulas : Drop down menu : This is most important part because it relates to FORMULAS. There are three options in this drop down menu, Formulas, Values & Comments.

Ø     Formulas : We use simple to complex formula in Excel to process data, sometimes we need to find or replace part of formula. If you select this option and try to search for example, $D - which is generally a part of formula the cell containing $D will be selected. Now you can change your formula easily using this option. For example, suppose we need to replace the $D by $E in a sum function as =SUM($D:$G), it can be replaced using this option.

Ø     Values : The FIND will work on the value of the cell ignoring what formula is there in the backend.

          Sometimes you may be surprised why you cannot find the search string using FIND and Replace option although the data is existing. This is one of the reasons behind the not getting your search term although exists, therefore, always check what option is selected in the Search option, if you are searching for a value and Search option is set as Formulas, this will not work.

Ø     Comments : If you want to find something from the comment section in the Excel, this option must be selected. I hope you know what is a comment in Excel, I will discuss it latter. A cell is marked in red in a corner of a cell if any comment is present and normally the is hidden, it displays when we hoover over that red triangle.

v     Format button (in the right upper corner) : You will find Format button in Find option but two Format button in Find & Replace option, this option is used to find the data based upon the formatting of the cell. Therefore, you can guess that if you click on this button a new window will appear, the well-known Format window, only the name if Find Format. Select any format that you want to search by the format already formatted.






Let us have some examples with pictures to make things clear.


How to use Find and Replace ?


Press Ctrl+F, type your search term, click on Find Next button.

How to use Find and Replace
How to use Find and Replace 


How to use Find and Replace to find all the occurrences of given Search term ?


Same as the above process, only you need to click on the Find All button and a list of occurrences will appear in the below list, if more than one row is visible click on the link that cell will be selected. In the below example two times the name is coming Donald Trump and two lines are visible in the box highlighted by Blue Color.

Multiple Search Result in Find and Replace
Multiple Search Result in Find and Replace


How to Find Data or String using Cell Color or Format ?


Click on the Options button, now click on the Format button which will open another window Find Format, select the format as per your search format, for example, the cell color is yellow containing the name Donald Trump, so I select the yellow color from the Fill option in the Find Format window and clicked Find Next, the cell is highlighted as shown below.

Search by Format
Search by Format


Note : Find by color will take more time than normal search.

Search by Cell Color or Format
Search by Cell Color or Format


When you want to replace your string you can follow the same method the only difference is that you must use Replace window by pressing Ctrl+H and use your replacement text or data. The rest is same, so I will not discuss it here, if you find problem please comment on the COMMENT box, I will clarify.

How to Search using Wild Card Character ?


There are two wildcard characters available ‘?’ and ‘*’.

v ‘?’ is used for any single character, For example, sm?th finds "smith" and "smyth"

v ‘*’ is used for any number of characters, For example, *east finds "Northeast" and "Southeast"

WildCard Search
WildCard Search


Now the obvious question is how to search for these ‘?’ and ‘*’ wildcard characters?

Lets’ discuss about it.

Search for Wild Card Characters


Did you ever try to find ‘?’ or ‘*’ the Wild Card characters’ using Find and Replace ? If yes, then you have noticed that it gives nothing !!! It just moves selected cells by one; like you are pressing Enter key in Excel. As you know these (‘?’ or ‘*’) are wild card characters, you can use these characters to find ‘partially known’ search terms.

How to search for Wild Card Characters ?


To search for ‘?’ or ‘*’ you must use a tiled (~) before these characters as shown in the below picture; when tiled sign is used before ‘?’ or ‘*’, Excel treats these as normal characters.

Search for Wildcard Characters
Search for Wildcard Characters


Hope you have enjoyed this article.
Thanks for reading…