Advanced Excel functions

Excel spreadsheets are widely used among all companies for pursuing small and medium dataset analysis, it is a great tool to perform visualization, automation, and analysis of any sort. Even though there are other tools to perform an analysis similar to excel, excel stands out to be the top preferred tool for all types of analysis by most users.

If you are a beginner to excel you might have heard of the most commonly used functions in excel which help greatly to perform excel analysis such functions are

SUM | MIN | MAX | TRIM | CLEAN | VLOOKUP | HLOOKUP | INDEX, MATCH | IF | RIGHT | LEFT | MID | SEARCH | FIND.

There are other functions that help with data transformation and analysis, In this blog, I am going to provide some useful functions and tricks that can help in data transformation and analysis purposes and explain in detail where they can be used.

Information Function:

• N Function:

This particular function converts a boolean output into a numerical value.

Syntax:

N (Value)

There are other methods to convert a boolean or text numbers (in the form of text) into numerical, one such method is using a double minus sign and this method is also helpful in converting text date into a date data type.

Math and Trigonometry functions:

• SUMPRODUCT Function:

The output of this function will be the sum of the products of corresponding ranges or arrays. By default, the function performs the Multiplication process, but we can also perform addition, subtraction, and division as well.

Syntax:

=SUMPRODUCT ([array1], [array2], [array3]...)

We can also perform a calculation using arithmetic operations like +, -, /, *

The above example shows how to use the arithmetic operations for the function and arithmetic does work on most of the Math functions like Average, Sum, etc.

One particular arithmetic operation does do conditional calculations based on the requirement.

Wildcards in Excel:

Wildcards are most useful when searching the content using keywords. Wildcards can be used in almost all the functions in Excel and filters as required.

Types of wild cards:

USE	TO FIND
? (question Mark)	Any single character For Example, In?osys finds Infosys
* (asterisk)	Any number of characters For Example *at finds cat, pat, etc.
~ (tilde) followed by ?, *, or ~	A question mark, asterisk, or tilde, For Example, fy06~? finds "fy06?"

Some examples of wildcards as shown below.

Statistical Function:

• Frequency function:

Frequency functions are helpful to find any particular value that is repeated how many times and also provide a count of value in the bins.

Syntax:

FREQUENCY(data_array,bins_array)

The above examples identify the frequency within the range like the bin value is 15 the function counts several values from 0 to 15, Frequency function is used for grouping data and analyzing by visualizing the dataset.

Text functions:

• ARRAYTOTEXT function:

Arraytotext function returns an array of text values from any specified range, the function passes text values as same and converts the numerical values as text. The function does convert the column range or row range into a single cell output.

Syntax:

ARRAYTOTEXT (array, [y, [format])