10 Excel formulas that everyone should know

0 109

Does Microsoft Excel seem too complicated? In this article we present you 10 useful and interesting Excel formulas , easy to remember and ready to apply in your spreadsheets.

Microsoft Excel is the spreadsheet program par excellence. Praised for its wide variety of functions, it can be a bit challenging for all those who use it for the first time.

This labyrinthine program can perform almost any task and offers numerous options. To make matters worse, in Office  this application has become even more powerful.

The simple sum (SUM)

It allows adding various numerical data, such as at school. There are two ways to do it.

The first allows you to add contiguous cells (in a row or in a column):

Type = SUM (first_cell: last_cell) in the cell where you want the result to appear.

Variant: This formula also allows you to add all the numbers contained in a specific rectangular area (formed by several rows and columns), if you consider as the first cell the one located in the upper left corner and as the last one that is in the lower vertex law.

In the following examples, “first_cell: last_cell” will always be designated with the term “range”.

The second method will allow you to add individual (non-contiguous) cells:

Type = SUM (cell1; cell2; cell3; cell4) in the cell where you want the result to appear.

Add according to a certain criterion (SUMAR.SI)

Note that this feature is only available as of Excel 2007 .

A table can be presented in the form of a list with different data, even if you do not want to work with all of them at the same time.

If this list is long, it can be tedious to select the identical items one by one to add them up. In this case, it is possible to order Excel to find them and then add them with the following formula:

= SUM.SI (range; “criterion”; range_sum) .

The “criterion” is the condition to look for in the table. The range is the range of cells that contain the criteria to be evaluated. Sum_range is the range of cells where the numbers you want to add are found.

Add according to several criteria (SUMAR.SI.CONJUNTO)

A table can provide a wide variety of information. Excel allows you to add numerical data that meet different criteria, thanks to this special formula:

= SUMAR.SI.CONJUNTO (suma_suma; range_criterios1; criteria1; range_criterios2; criteria2; ….) .

Sum_range is the range of cells where the numbers you want to add are found.

Criteria_range1 represents the range of cells that contain the data referred to the first criterion, criteria_range2 refers to the second criterion, etc.

Criterion1 is the first condition to look for in the table, etc.

Count the cells that meet a criterion (COUNTIF)

When it comes to statistics, you may want to know how many times information is repeated throughout a column. In this case, we use the following formula:

= COUNTIF (range; “criterion”) .

The range represents the range of cells that contain the numerical data to analyze. Criterion is the condition to look for in the table.

Add the cells that meet various conditions (SUMPRODUCT)

This formula gives you an exact statistic to answer this question: How many times a series of specific conditions are fulfilled in your table . This formula will give you the answer:

= SUMAPRODUCT ((range_criteria1 = “criterion1”) * (range_criteria2 = “criterion2”)) .

Criteria_range1 represents the range of cells that contain the data referring to the first criterion, criteria_range2 refers to the second criterion, etc. Criterion1 is the first condition to look for in the table, etc.

Add the cells that verify two specific conditions (SUMAPRODUCT)

As we have already seen, SUMAPRODUCT is mainly a statistical tool capable of determining how many times certain conditions are met. It will allow you to know how many numbers are included between two specific values, thanks to the following formula: = SUMAPRODUCT ((range> = minimum) * (range <= maximum)) .

Range represents the range of cells that contain the numeric data to analyze. Minimum and maximum are the values between which the numbers we are looking for should be.

The average (AVERAGE)

It would be a waste of time to repeat the definition of average. Mainly, because it is very simple to calculate it with the following formula: = AVERAGE (rank)

The range represents the range of cells that contain the numerical data to analyze.

Maximum and minimum (MAX and MIN)

Finding the largest or smallest value among a large amount of data can be a real nuisance. Happily, Excel provides a formula that does the work for you. It is very easy to use and is written as follows:

= MAX (range)

= MIN (range)

The range represents the range of cells that contain the numerical data to analyze.

Variant: You can apply the search to several cell ranges. In that case, the formulas would be:

= MAX (rank1; rank2)

= MIN (rank1, rank2)

Bonus Track: How to calculate percentages in Excel

Before finishing we will make a brief review of the world of percentages in Excel. This section does not intend to delve deeply into the subject, but we invite you to leave any doubt about it in the comments. If we see enough interest we will consider making a specific tutorial.

If you want to obtain the percentage of a certain amount it is as easy as multiplying it by the percentage that we want to obtain.

To end

Although at first glance they may seem totally inscrutable, these Excel formulas will save you an incredible amount of time if you regularly use this program.

Do not forget to download Excel for your multiple devices!

Download  Microsoft Excel  for Mac

Download  Microsoft Excel

Leave A Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.