The functions of the SRVNAV and the SRVDOCK are used to calculate the average arithmetic value of the arguments of interest in Excel. The average number is calculated by the classic way - the summation of all numbers and the division of the amount by the number of these numbers. The main difference of these functions is that they are differently handled not numeric types of values \u200b\u200bthat are in Excel cells. Further about everything in more detail.
Examples of using the function of the SRPNAH in Excel
If there is a range of cells B2: B8 with numbers, then formula \u003d SRVNOV (B2: B8) will return the mean value of the specified numbers in this range:
The syntax of the use is as follows: \u003d CPNPH (number1; [number2]; ...), where the first number is a mandatory argument, and all subsequent arguments (up to date 255) are optional for filled. That is, the number of selected source ranges cannot exceed more than 255:
The argument may have a numeric value to be a link to the range or array. Text and logical value in the range is completely ignored.
The arguments of the SRVNAF function can be represented not only by numbers, but also by names or references to a specific range (cell) containing the number. The logical value and the text representation of the number that is directly entered into the list of arguments are taken into account.
If the argument is submitted by reference to the range (cell), then its text or logical value (reference to an empty cell) is ignored. At the same time, cells that contain zero are taken into account. If the argument contains errors or text that cannot be transformed into a number, then it leads to a general error. To account for logical values \u200b\u200band text presentation, the numbers are necessary in the calculations to use the SRDeaving function, which the conversation will go further.
The result of the function in the example in the picture below is the number 4, because Logical and text objects are ignored. Therefore:
(5 + 7 + 0 + 4) / 4 = 4
When calculating average values, it is necessary to consider the difference between the empty cell and the cell containing the zero value, especially if in the Excel dialog box, the "Show zeros in cells that contain zero values" are removed. When the flag is installed, empty cells are ignored, and there are no zero values. To remove or install this flag, you must open the "File" tab, further click on the "Parameters" and select the "Show options for the next sheet" in the category "where it is possible to select the checkbox:
The results of 4 more tasks are reduced to the table below:
As can be seen on the example, in the cell A9, the function of the SR will have 2 arguments: 1 - the range of cells, 2 - an additional number 5. It can also be specified in the arguments and additional ranges of cells with numbers. For example, as in the cell A11.
Formulas with examples of using the function of the SRD
The SRDNOCH function differs from the fact that the true logical value "Truth" in the range is equal to 1, and the false logical value "false" or a text value in the cells is equal to zero. Therefore, the result of calculating the function of the SRDedcha is different:
The result of the function returns the number in Example 2.833333, as text and logical values \u200b\u200bare taken for zero, and the logical truth is equal to one. Hence:
(5 + 7 + 0 + 0 + 4 + 1) / 6 = 2,83
Syntax:
SRDNOCH (value1; [value2]; ...)
The arguments of the functions of the SRDEDOV are subordinate to the following properties:
- "Value1" is mandatory, and "value2" and all the meanings that are followed optional. The total number of cell ranges or their values \u200b\u200bcan be from 1 to 255 cells.
- The argument may be a number, name, array or reference containing a number, as well as a textual representation of a number or a logical value, for example, "truth" or "lie".
- The logical value and the text representation of the number entered into the list of arguments are taken into account.
- The argument containing the "truth" value is interpreted as 1. The argument containing the "Lie" value is interpreted as 0 (zero).
- The text contained in arrays and references is interpreted as 0 (zero). Empty text ("") is interpreted, too, as 0 (zero).
- If the argument array or reference, only the values \u200b\u200bincluded in this array are used or link. Empty cells and text in the array and reference - ignored.
- Arguments that are values \u200b\u200bof errors or text that are not transformed into numbers cause errors.
The results of the feature of the SRDedal function are reduced to the table below:
Attention! When calculating average values \u200b\u200bin Excel, it is necessary to take into account the differences between an empty cell and a cell containing a zero value (especially if in the "Parameters" dialog box, the "Show zeros in cells that contain zero values" are removed. When the flag is set, empty cells are not taken into account, and zero values \u200b\u200bare taken into account. To set the checkbox on the "File" tab, you need to select the "Parameters" command, go to the category "Advanced", where find the section "Show options for the next sheet" and install the flag there.
