Financial Formulas in Excel

A significant part of the time for many economists or financiers is spent working on a computer with the MS Excel office application. This program has a considerable number of functions intended for creating reports, data analysis, information plans, mathematical calculations and much more. Knowledge of the most important Excel formulas and a combination of various functions greatly facilitates the solution of practical problems and reduces the time and effort spent on it.
Functions INDEX and MATCH
IF function combined with AND function
Combination of SUM and OFFSET functions
SUMIF and COUNTIF functions
MODE.SNGL function

Functions INDEX and MATCH

In financial calculations, a combination of the INDEX and MATCH functions is often used. Their joint action is similar to the operation of the VLOOKUP function, but has many advantages compared to it. First, we will consider these functions separately.

Function INDEX

The INDEX function finds the value of an element in a data block by the specified row number and column number. In general, its structure will look like this:

INDEX (array; row_number; [column_number]), where

  • array – this is a block of cells where the search will be performed.
  • row_number – this is the sequence number of the line in which the value is found. This parameter is required if the column number is not specified.
  • column_number – this is the serial number of the column in which the desired value is located. If the line number is not specified in the formula, this parameter is required.

If a row number and a column number are specified, the function returns the value of the cell located at the intersection of this data.

Consider an example. There is a table with a list of product numbers and their dimensions. In order to find the length of the product number 2, you need to write a formula of the form =INDEX(B2:D6;3;2)
Function INDEX
In this example, the result of the function will be the contents of the cell at the intersection of the third row and second column of this array.

Unfortunately, most often in calculations, the row or column number is unknown. Then the MATCH function comes to the rescue.

Function MATCH

The action of the MATCH function is similar to the action of the INDEX function, but MATCH returns not the cell value, but the cell position in the specified range. In the general case, the formula will look like:

MATCH (search_value; array; [match_view]), where

  • search_value – this is what you need to find. Here can be not only a textual or numerical value, but also a logical one, as well as a link to a cell.
  • array – this is the cell range being viewed.
  • match_view – this is an optional parameter that can be set to “1”, “0” or “-1”. It tells the function which value to find: exact or approximate. If the data in the array are decreasing in ascending order, parameter “1” will indicate that it is necessary to select the maximum value less than or equal to the desired one. The “-1” parameter is specified for a decreasing array. In this case, the function will select the minimum value greater than or equal to the desired one. Parameter “0” finds the first value equal to the desired one. It is this value of the matching type that is used in the combination of the INDEX and MATCH functions.

To illustrate the operation of this function, consider an example. In the above table, we find in the column “Product Number” what the account will be for product number 2. For this, we write the formula: =MATCH(2;B2:B6;0)
Function MATCH
Why do I need to know the position of an element in a table? It turns out that it is very convenient to use this value as an argument to the INDEX function.

Combination of INDEX and MATCH Functions

If we analyze both functions, it becomes clear that the INDEX function searches for cell values by row number and column number. At the same time, the MATCH function finds row numbers and column numbers. Thus, if you use these two functions in the same formula, MATCH will find the relative position of the desired value, and the INDEX function will use these values and return the contents of the calculated cells.

Consider an example. In the above table we find the width of the product at number 3. For this we use the following formula: =INDEX($D$2:$D$6;MATCH(3;$B$2:$B$6;0))
Combination of INDEX and MATCH
It is recommended that you use absolute links for these formulas so that search ranges are not confused when copying formulas.

Why is it better to use a combination of INDEX and MATCH functions rather than using the VLOOKUP function? Firstly, a formula based on INDEX and MATCH allows you to search for the desired data in the specified range from left to right and from right to left, while when using VLOOKUP, the desired value should always be in the leftmost column of the range.

Secondly, when using the VLOOKUP function, you cannot delete or add columns to the table. Otherwise, the result of the formula will be incorrect. This is because the syntax of this function involves specifying the entire range and the specific column number from which the data will be taken. When using the INDEX and MATCH functions, you can delete or add as many columns as you like.

Thirdly, when using a combination of INDEX and MATCH functions, there is no restriction on the size of the search value, while the VLOOKUP function limits the number of characters of the search value to 255 characters.

Fourth, when performing calculations in large data arrays, using the INDEX and MATCH functions significantly reduces the search time for values compared to the VLOOKUP function. This is because the VLOOKUP function is called for each value from the specified data range. In contrast, a formula based on the INDEX and MATCH functions simply performs a search and returns a result.

IF function combined with AND function

The IF boolean function checks whether the contents of the cells meet certain conditions. If it matches, the function returns one of the user-defined values. In case of discrepancy, returns another set value. The syntax of the function is as follows:

= IF (boolean expression; value_if_true; value_if_false), where

  • boolean expression – this is the data that needs to be checked and the conditions for verification. For example, А2>10.
  • value_if_true – this is the entry that will appear if the cell value meets the specified condition.
  • value_if_false – this is the record that will appear if the cell value does not satisfy the given condition.

Many users who have performed complex financial calculations know how difficult it is to understand formulas that use nested loops using the IF statement. It turns out that these formulas can be simplified if you use the IF function in combination with the AND / OR functions. The combination of the And and IF functions works as follows. If A = 1 AND A = 2, the formula returns the value B, otherwise it returns C. For the OR function, the formula does not work that way. If A = 1 OR A = 2, then the formula returns the value B, otherwise – the value C.

Consider an example. Create a formula that checks the contents of cell C2, equal to 110. If the number is in the range from 90 to 300, the result will be 1, otherwise 0. The formula will look like this: = IF (AND(C2>=C4;C2<=C5);C7;C8)
IF function
As can be seen from the figure, a value equal to 1 will be placed in the cell with the result of the function. The number 110 is really in the range from 90 to 300.

The total value can be not only a number, but also a text, for example, the words “YES” or “No” or any other phrase.

Combination of SUM and OFFSET functions

The OFFSET function itself is rarely used, but combining it with other functions can produce very good results. For example, the combined use of the SUM and OFFSET functions allows you to create fairly complex formulas when creating a dynamic function that sums a variable number of cells. To solve this problem, the SUM function is used, and instead of the final cell, the OFFSET function is specified, that is, the formula becomes dynamic.

The resulting formula will look like this:

= SUM (range_start: OFFSET (reference, row_number, column_number)), where

  • range_start – this is the starting point of the cell range used by the SUM function.
  • reference – this is a reference to the cell that is used to calculate the endpoint of the range.
  • row_number – this is the number of rows used in calculating the cell offset. This value can be positive, negative and equal to zero.
  • column_number – this is the number of columns to the right or left of the given cell reference. Used when calculating the offset. When shifted to the left, this value is negative. When shifted to the right, the value is positive. If the calculated data is in the same column, then this parameter is zero.

Consider an example. There is a table with the serial numbers of the days of the month and the income received on each day. Every day, the information in the table is updated by adding a row with the income received per day. We compose the formula in the final cell after the fourth day of sales: =SUM(B2:OFFSET(B6;-1;0))
OFFSET functions
In order to add information about the fifth day of sales, you need to add an empty line after the fourth day and enter the necessary information into it. In this case, the formula will take the form: =SUM(B2:OFFSET(B7;-1;0)), and total income will increase by the amount of income received on the fifth day.

SUMIF and COUNTIF functions

These two functions are very often used in financial calculations. SUMIF finds the sum in a given range of cells by a certain condition. COUNTIF counts all cells matching the given condition.
The SUMIF function has the following structure:

SUMIF (range; criterion; sum_range), where

  • range – this is an array of cells in which the compliance with the specified criteria will be checked.
  • criterion – this is a condition for selecting cells. This can be a number, text, expression, or cell reference.
  • sum_range – This is an optional parameter. If you do not specify it, the summation will be performed taking into account the argument “Range”.

The action of this function is easy to understand with an example. There is a table with a list of goods and their quantity. You need to find the amount of “Product 1”. The formula for the calculation will be as follows: =SUMIF(B2:B7;”Product 1″;C2:C7)
SUMIF functions
In the same example, you can calculate the number of goods excluding “Product 1″ by the following formula: =SUMIF(B2:B7;”<>Product 1″;C2:C7)
The COUNTIF function has the following structure:

COUNTIF(range; criterion), where

range and criterion are similar to the SUMIF function.

In the same example, we count the number of lines “Product 1”. The formula for calculating will look like: =COUNTIF(B2:B7;”Product 1”).
COUNTIF functions
Using this formula, you can calculate the number of rows that satisfy certain conditions. For example, in order to calculate the number of rows in this table with the number of products greater than 10, you need to create the following formula: =COUNTIF(C2:C7;”>10″).

MODE.SNGL function

The statistical function MODE.SNGL and its obsolete version MODE finds the most frequently occurring value in the data range (array) and returns this value. Function Syntax:
=MODE((number 1;[number 2];…), where

  • number 1 – ithis is a required argument, which is a number, a reference to a number cell, an array, or a range of cells.
  • number 2 – this is an optional argument. Such arguments can be from 1 to 255.

Arguments that cannot be converted to numbers cause a formula error. If there are no identical numbers in the specified range, the result of the function will be the error value # N / A.

Why can I use this function in financial calculations? For example, in order to find out what products are most often bought on the basis of summary data. The selection criterion may be the price of the product, size, volume, dimensions and so on. As an illustration, we consider a table that reflects sales of goods with the date of sale, size of the product and its price. In order to find out what sizes of products are most often sold, we use the formula : =MODE(C2:C6)
MODE function
The figure shows that products with a size of 36 are in greatest demand. Thus, the MODA function determines the most frequently occurring event in the range of events.

Thus, the use of the financial functions of the Excel office application greatly facilitates the conduct of financial and commercial calculations for loans and borrowings, financial and investment analysis and work with securities. A significant advantage of the application is the ability to set a password to protect important documents. This prevents unauthorized third parties from accessing financial and economic reports. Unfortunately, passwords are sometimes lost, but Excel password recovery is possible using special programs.