The math and trigonometric category contains about 80 different Excel functions, ranging from the indispensable summation and rounding, to a little-known number of trigonometric functions. As part of this lesson, we will review only the most useful mathematical functions in Excel.
About mathematical functions SUM и SUMMESLI You can read in this tutorial.
ROUND()
math function ROUNDWOOD allows you to round the value to the required number of decimal places. You can specify the number of decimal places in the second argument. In the figure below, the formula rounds the value to one decimal place:
If the second argument is zero, then the function rounds the value to the nearest integer:
The second argument can also be negative, in which case the value is rounded to the required decimal point:
A number like 231,5 is a function ROUNDWOOD rounds away from zero:
If you need to round a number up or down in absolute value, you can use the functions KRUGLVVERH и ROUND DOWN.
PRODUCT()
math function PRODUCT calculates the product of all its arguments.
We will not discuss this function in detail, since it is very similar to the function SUM, the difference is only in the purpose, one sums up, the second multiplies. More details about SUM You can read the article Sum in Excel using the SUM and SUMIF functions.
ABS()
math function ABS returns the absolute value of the number, i.e. its module.
Function ABS can be useful when calculating the number of days between two dates, when there is no way to determine which date is the start and which is the end.
In the figure below, columns A and B represent dates, and which of them is the initial and which is the final date is unknown. It is required to count the number of days between these dates. If you simply subtract another date from one date, then the number of days may turn out to be negative, which is not entirely correct:
To avoid this, we use the function ABS:
Pressing Enter, we get the correct number of days:
ROOT()
Returns the square root of a number. The number must be non-negative.
You can also extract the square root in Excel using the exponentiation operator:
DEGREE()
Allows you to raise a number to a given power.
In Excel, in addition to this mathematical function, you can use the exponentiation operator:
CASEBETWEEN()
Returns a random number between the two values given as arguments. Each time the sheet is recalculated, the values are updated.
Although there are a lot of mathematical functions in Excel, only a few of them are of real value. There is no point in learning everything at once, since many may not even be useful. The mathematical functions described in this lesson are the very minimum that will ensure confident work in Excel and will not overload your memory with unnecessary information. Good luck and success in learning Excel!