10 formulas in Excel that will make your life easier

May 15, 2020LikbezTechnology
These simple yet useful features can come in handy for a variety of tasks.

0

To apply any of the listed functions, put an equal sign in the cell where you want to see the result. Then enter a name for the formula (for example, MIN or MAX), open the parentheses, and add the necessary arguments. Excel will suggest the syntax so you don’t make a mistake.

Arguments are the data that the function works with. To add them, you need to select the appropriate cells or enter the required values ​​in brackets manually.

There is also an alternative way to specify arguments. If you add empty brackets after the function name and click the Insert Function (fx) button, an input window will appear with additional prompts. You can use it if you feel more comfortable.

1. MAX

• Syntax: =MAX(number1; [число2]; …).

The MAX formula displays the largest of the numbers in the selected cells. Function arguments can be either individual cells or ranges. Be sure to include only the first argument.

2 MINUTES

• Syntax: =MIN(number1; [число2]; …).

The “MIN” function is the opposite of the previous one: it displays the smallest number in the selected cells. Otherwise, the principle of operation is the same.

3. AVERAGE

• Syntax: =AVERAGE(number1; [число2]; …).

“AVERAGE” displays the arithmetic mean of all numbers in the selected cells. In other words, the function adds the values ​​specified by the user, divides the resulting sum by their number, and returns the result. Arguments can be individual cells and ranges. For the function to work, you need to add at least one argument.

4. SUM

• Syntax: =SUM(number1; [число2]; …).

This simple but highly requested function calculates the sum of the numbers in the selected cells. You can add both individual values ​​and ranges of cells. Only the first argument is required in the formula.

5. IF

• Syntax: =IF(logical_expression; value_if_true; [значение_если_ложь]).

The IF formula checks if a given condition is true and displays one of two user-specified values ​​depending on the result. It makes it easy to compare data.

Any logical expression can be used as the first argument of the function. The second is the value that the table will display if this expression is true. And the third (optional) argument is the value that appears when the result is false. If it is not specified, the word “false” will be displayed.

6. SUMIF

• Syntax: =SUMIF(range, condition, [диапазон_суммирования]).

Improved “SUM” function that adds only those numbers in the selected cells that meet the specified criteria. With its help, you can add numbers that, for example, are more or less than a certain value. The first argument is a range of cells, the second is the condition under which elements for addition will be selected from them.

If you need to calculate the sum of numbers not in the range selected for testing, but in an adjacent column, select this column as the third argument. In this case, the function will add the numbers next to each cell that passes the test.

7. COUNT

• Syntax: =COUNT(value1; [значение2]; …).

This function counts the number of selected cells that contain numbers. Arguments can be individual cells and ranges. The function requires at least one argument to work. Be careful: “COUNT” takes into account cells with dates.

8. DAYS

• Syntax: =DAYS(end date; start date).

It’s simple: the “DAYS” function displays the number of days between two dates. First, the end date is added to the arguments, and then the start date – if they are mixed up, the result will be negative.

9. CORREL

• Syntax: =CORREL(range1, range2).

“CORREL” defines the correlation coefficient between two ranges of cells. In other words, the function calculates the statistical relationship between different data: dollar and cent exchange rates, expenses and profits, and so on. The more changes in one range coincide with changes in another, the higher the correlation. The maximum possible value is +1, the minimum is -1.

10. SCEP

• Syntax: =CONCEPT(text1; [текст2]; …).

This function merges text from selected cells. Arguments can be either individual cells or ranges. The order of the text in the result cell depends on the order of the arguments. If you want the function to put spaces between text fragments, add them as arguments, as in the screenshot above.

• 10 Quick Excel Tricks
• 4 Data Analysis Techniques in Microsoft Excel
• 12 Easy Tricks to Work Faster in Excel
• How…

Posted

in

by

Tags: