Advanced Excel 10 Formulas You Must Know 2021

Excel Formulas You Must Know About

Excel formulas 2021

Primarily designed as a spreadsheet program, Microsoft Excel is extremely powerful and versatile. It calculates numbers, solves math and engineering problems.

This helps you to find the total or average numbers of the column in an instant. Additionally, you can count compound interest and weighted average, get the best budget for your advertising campaign, reduce shipment costs, or schedule work for your employees.

To do all this, you have to enter the formula in the cell.

Don’t waste any more time manually doing all these things in Microsoft Excel. There are many ways to use Excel formulas to reduce the amount of time you spend in Excel and increase the accuracy of your data and reports.

The purpose of this tutorial is to teach you the essentials of Excel functions and how to use basic formulas in Excel.

Topics

• Excel formula:
• 1) SUM:
• 2) AVERAGE:
• 3) MAX & MIN:
• 4) COUNT & COUNTA:
• 5) IF:
• 6) TRIM:
• 7) LEN:
• 8) AND & OR:
• 9) CONCATENATE:

Excel formula 2021:

1) SUM:

The first Excel function that you should be familiar with is SUM which performs basic arithmetic operations of addition.
Syntax Of SUM Function:

The syntax of the SUM function is as follows:

SUM (number1, [number2],…)

The first argument is required in the SUM function, the other numbers are optional, and you can enter 255 numbers in a formula.
Meaning, your SUM formula must include at least 1 number, cell or cell range reference.

Example of SUM Function:

for example:

= SUM (A1: A5) – This cell adds values ​​of cells from A1 to A5.

= SUM (A2, A5) – This cell adds values ​​of A2 and A5 cells.

= SUM (A2: A5) / 5 – This cell adds values ​​of cells from A1 to A5 and divides this sum by 5.

In your Excel worksheet, this formula might look like this:

Tip – The fastest way to sum a column or row is to select the next cells in these cells and click the AutoSum button on the Home tab.

2) AVERAGE:

Excel’s AVERAGE function looks for the average (arithmetic mean) of numbers.

Syntax Of AVERAGE Function:

The syntax of the AVERAGE function is as follows:

AVERAGE (number1, [number2],…)

Here number1, [number2], etc. are one or more numbers (or references to cells with numbers) whose average calculation you want to do.

Example of AVERAGE Function :

for example:

= AVERAGE (A1: A5)

3) MAX & MIN:

The MAX and MIN formulas in Excel are the largest and smallest values ​​of a set of numbers, respectively.

Example of MAX & MIN Function :

for example:

= MAX (A2: A5)

= MIN (A2: A5)

4) COUNT & COUNTA:

If you want to know how many cells in the sales range have numeric values, do not waste your time in counting them manually. Excel’s COUNT function will count in a single second.

Syntax Of COUNT Function:

The syntax of the COUNT function is as follows:

COUNT (value1, [value2],…)

While the COUNT function counts only those cells that contain numbers, Excel’s COUNTA function counts all cells that are not blank, regardless of the numbers, dates, times, text, logical values ​​of TRUE, and FALSE. Values ​​can be errors or empty text strings (“”).

Syntax Of COUNTA Function:

The syntax of the COUNTA function is as follows:

COUNTA (value1, [value2],…)

Example of COUNT & COUNTA Function:

For example, to find out how many numbers are included in column A, use this formula:

= COUNT (A: A)

To count all non-empty cells in column A, use this formula:

= COUNTA (A: A)

In both formulas, you are referring to cells in the entire A column.

5) IF:

When you use the IF formula in Excel, you ask Excel to test certain conditions and when this condition is met, you give an Excel value or perform a calculation and if this condition is not met then the excel Performs values ​​or other calculations.

Syntax Of IF Function:

The syntax of the IF function is as follows:

IF (logical_test, [value_if_true], [value_if_false])

In easy language –

IF (if something is true, do something, otherwise do something else)

So an IF statement can have two results. If your comparison is correct then the first result, or the second result.

Example of IF Function :

For example, if students have more than 35 marks then they are PASS and if they have less than 35 marks then they are FAIL.

= IF (A2> = 35, “PASS”, “FAIL”)

6) TRIM:

Excel’s TRIM function removes extra space from words and does not place a single space character in the start or end of the text.

There are several ways to remove unwanted spaces in Excel, with the TRIM function being the easiest way:

Syntax Of TRIM Function:

The syntax of the TRIM function is as follows:

TRIM (text)

Example of TRIM Function :

For example, to remove all the extra spaces in the column A, enter the following formula in cell A1, and then copy it below the column:

= TRIM (A1)

It will remove all the extra spaces in the cells and place only one space character between the words.

7) LEN:

Use LEN whenever you want to know how many characters are in a cell.

Syntax Of LEN Function:

The syntax of the LEN function is as follows:

= LEN (text)

Example of LEN Function :

To know how many characters are there in A2 cell –

= LEN (A2)

Please keep in mind that Excel’s LEN function counts all characters with space.

8) AND & OR:

These are the two most popular logical functions for testing multiple parameters.

AND Function:

This comes in handy when you have to test multiple conditions and make sure that they are all TRUE.

Technically the AND function tests the conditions you have specified and if all the conditions are TRUE, it returns TRUE or else FALSE returns.

Syntax Of AND Function:

The syntax of the AND function is as follows:

= AND (logical1, [logical2],…)

Example of AND Function :

For example, students have marks above 35 in both Math and English, they are PASS, otherwise FAIL.

= IF (AND (B2> 35, C2> 35), “PASS”, “FAIL”)

OR Function:

Like OR Excel or a function, an OR is a logical function that is used to compare two values ​​or statements.

The only difference is that the OR function returns TRUE if any of the given conditions is TRUE and returns FALSE if all the conditions are false.

Syntax Of OR Function:

The syntax of the OR function is as follows:

= OR (logical1, [logical2],…)

Example of OR Function :

For example, if students have marks above 35 in any subject in both Math and English, they are PASS, otherwise FAIL.

= IF (OR (B2> 35, C2> 35), “PASS”, “FAIL”)

9) CONCATENATE:

The CONCATENATE function of Excel joins two or more text items. It combines values ​​of two or more cells into a single cell.

Syntax Of CONCATENATE Function:

The syntax of the CONCATENATE function is as follows:

CONCATENATE (text1, [text2],…)

Example of CONCATENATE Function :

For example, to combine the values ​​of an A2 and B2 cell, simply enter the following formula in a separate cell:

= CONCATENATE (A2, B2)

If you want to separate these combined values, meaning add a space to them, then type (“”) the space character in the arguments list.

= CONCATENATE (A2, “”, B2)