Basic Functions for Analyst in Excel

There are a lot of people who have to work with data. And Excel is one of the best programs that allows you to effectively analyze information. Today we will look at 13 features that every person should know before starting to use this program. It is highly recommended to practice before implementing knowledge in real life.

13 Analyst Functions in Excel

Regardless of the area in which you work: selling a product, compiling financial statements or compiling a quarterly marketing report, you need to clearly understand what tools this task will be performed with. Here are the most popular features that can be useful in different areas. Of course, there are many more, but these are the 20% that give 80% of the result. Let’s start with general mathematical functions, and then move on to more advanced ones, which are closer to programming.

SUM function

Let’s start our hit parade with the simplest function that adds numbers from a certain range of data. It’s called SUM. It has only one required argument, but it is theoretically possible to use many more parameters.

Each argument is an array of data that will be summed up, as well as individual cells and numbers that can be added together. Using this operator, you can, for example, calculate the total sales of goods in the table or the total budget revenues of the country for a certain period. The general syntax of the function is as follows: =SUM (Array 1, Array 2…..)

PRODUCT function

The syntax for this function is the same as for SUM, but it multiplies multiple values ​​that are passed as arguments. Just like in the previous function, you can use the following data types as parameters: range, number, cell with a numeric value. The general syntax for this function is as follows: =SUM (Array 1, Array 2…..)

Practical examples of using this function: analysis of accounting data, marketing calculations. It must be said that this function is not used on its own as often as SUM. As a rule, it goes together with other formulas. For example, get a range of data, which is then multiplied among themselves.

Formulas can also be used as arguments to both functions.

IF function

This is the first logic function in our hit parade. With its help, you can set a condition under which certain text will be displayed in the cell or certain calculations will be performed. The syntax of this function is already more complicated than the previous ones and includes three main arguments:

  1. Expression. The condition is written here. For example, cell A1 must be greater than or equal to one.
  2. The result if true. This argument specifies what will be executed if the expression evaluates to true. That is, in simple words, if the criterion is confirmed. Also here you can enter another formula that will be executed and even specify a repeated function IFto make it perform another check.
  3. Result if false. The data types that can be used in this argument are the same. And in the same way, you can program this function to perform a different action if the expression is false.

As a result of using this function, something like this tree can be obtained. In simple words, this function is needed in order to set algorithms for calculations or actions.

And let’s, for greater clarity, give an example of a formula that performs a sequence of checks, which is shown in this diagram. =ЕСЛИ(А22=1; ЕСЛИ(А23<0;5;10); ЕСЛИ(А24<0;8;6)). Due to the fact that Excel provides the ability to set a full-fledged algorithm of actions, these spreadsheets are often called programmable.

By the way, you can automate data analysis with the help of full-fledged programming. Such a program is called a macro, only it is launched not from a cell, but through a special menu.

SZNACH function

With this function, you can get the arithmetic mean of the values ​​that are entered in brackets. The arguments are the same as in the function SUM. Supported data types are numbers, value ranges, number format cells, and other functions that return a numeric value. The general syntax of the function is as follows: =AVERAGE(array 1, array 2).

This function is very actively used for statistical calculations. It is especially popular for data analysis in socionomic sciences, such as psychology, sociology, and others.

SUMPRODUCT function

This feature, as the name suggests, is a combination of features SUM и MANUFACTURING. It first multiplies the values ​​contained in the arguments, and then gets the sum of the resulting values. For example, we have a table with three arrays of data.

To get products, you need to multiply the values ​​​​in each of the columns among themselves. After that, sum the resulting values. This takes a lot of time, so it’s much easier to use the function SUMPRODUCT.

In this case, it is enough to specify arrays as arguments, separated by a semicolon from each other. Everything else the program will do for the person himself.

MAX function

With this function, you can get the maximum value from a dataset. Also often used in statistics. It is also very popular among traders who have to analyze large amounts of digital data and determine high and low points. Or to analyze the effectiveness of any other business. For example, if a person has an online store, and he wants to determine in which month the revenue was the largest. To do this, you do not need to look at a huge amount of data, just use the corresponding function.

The syntax is very simple: in brackets you need to specify numbers, ranges or functions that return numbers, among which there will be the largest value.

MIN function

In its structure, this function is the same as almost any other statistical operator. At the same time, it performs another function – it calculates the smallest value in the range. Very often features MIN и MAX are used in pairs. So, in the same trading, they are needed in order to find the entry point and the point of sale of securities or cryptocurrencies.

Of course, now this task can be performed with the help of other programs online, but if we are talking about positional trading (that is, long-term trading), then the standard Excel spreadsheet is still actively used. These two functions are the guarantor of the trader’s safety, since they allow you to enter the market in time to make quick money and exit it in time when one of the previous highs is broken by the price.

Of course, these functions also have some limitations in trading on financial markets, but this is a completely different topic for discussion.

GREATEST function

At first glance, it might seem that this function performs exactly the same task as the operator MAX. But it is not. Using the function LARGE you can return the smallest desired number after the maximum. The syntax is a bit hard to understand. It contains the following arguments:

  1. Array. The range of values ​​that are used by this function.
  2. Coefficient. This is the number, from the side of the largest, from which you want to count the value.

If we put the number k – one, then this function behaves exactly the same as the operator MAX. But if you put a different value, then the operator will determine the second, third, and so on value by value.

Let’s take a small example to illustrate. Let’s say we have a number series 1;3;6;5;10. If we set the coefficient to 1, then the value returned by the function will be similar to that returned by the function MAX – 10. If the number k is set to level 2, then the function will return the value 6, if the number k is equal to three, then the value 5 will be returned, and so on.

SMALL function

This function works exactly the same as LARGE, but counting starts from the smallest value. Similar to the previous statement, when the value of the number k is equal to one, the return value is identical to the function MIN. With each subsequent increase in this coefficient, the minimum value becomes larger.

Thus, one can understand the second cheapest product on the market as one of the possible applications of this function. Or find out the third bottom on the quotes chart.

VLOOKUP function

Another very advanced feature that allows you to search for the desired value in situations where a person does not know exactly where in the table it can be contained. This function scans the range horizontally and takes the following arguments:

  1. The desired value. This is the string or number to be found.
  2. Table. This argument must specify the range of values ​​that can be parsed.
  3. Column number.
  4. Interval viewing. This is a boolean value that can be either zero or one. If the value of this argument is zero, then the match must be exact for the function to work. If the value is equal to one, then the exact value is not required.

Now let’s give an example of using this function. Suppose we have a table with such data.

According to the condition of our problem, we need to find:

  1. How old is employee Ivanov.
  2. Which university did employee Petrov graduate from.

An example of formulas that can solve each of these problems:

  1. =ВПР(А6; А1:С4; 3;0). This formula searches the first column of the table and returns the value in the third column corresponding to the found value. As a result, we learn that Ivanov is 22 years old.
  2. =ВПР(А6; А1:С4; 2;0). This formula will analyze the first column to see if the value “Petrov” is there. If yes, then it looks for what value of the second column corresponds to it. As a result, we got the value HSE.

The function VPR there is an analogue that performs a horizontal search – GPR. Its syntax is the same, only the principle of operation is slightly different.

INDEX function

With this function, the user can find the value that corresponds to the corresponding row and column of the specified data range. Question: why can’t you immediately set the address of the cell? The fact is that this function allows you to search for data by row and column number, this is its main advantage. Syntax: INDEX(Array;Row number;Column number); Suppose we have the following table with data:

Suppose we are interested in the last name of a person, which is located at the intersection of the third row and the first column. Therefore, we need to write a formula like this to achieve our goal: =INDEX(A1;S4;3;1).

SUM function

This function, as the name implies, combines the functions SUM и IF. Namely, subject to a certain requirement, this function sums certain values. The operator syntax includes three required arguments:

  1. The range for the criterion. Required argument. This is a set of data that will be checked against a certain condition.
  2. Criterion. This is the condition against which the data set specified in the first argument is performed.
  3. Summation range. This argument is optional, but is required if the first argument is a text range.

COMBINATION function

Similar to the previous function, which can check the range for compliance with several criteria at once. Therefore, its syntax is also slightly different:

  1. Summation range.
  2. Criterion range 1.
  3. 1 Criterion.
  4. Criterion range 2.
  5. 2 Criterion.

And so on. You can specify a lot of criteria, but in practice 3-5 are enough. As you can see, there are quite a lot of functions. But in reality there are even more. But you need to master these first.

Leave a Reply