Basic Excel functions, such as calculation and statistical functions, are very useful for those who frequently work with Excel spreadsheets, especially in accounting and human resources.
Using Excel, we frequently work with basic functions. These functions help perform data calculations directly on the spreadsheet more quickly and conveniently.
Excel offers a vast array of functions for analyzing, auditing, and calculating data by making the most of the functions provided. These are far more convenient than expressions.
COUNT function
The COUNT function in Excel returns the count of numerical values. This includes negative numbers, percentages, dates, times, fractions, and formulas that return numbers. Empty cells and text values are ignored.
Syntax: COUNT (value1, [value2], ...)
The COUNT function syntax has the following arguments:
value1: (Required) The first item, a reference to the cell or range you want to count.
value2, ...: (Optional) Up to 255 additional items, cell references, or ranges that you want to count.
Note that the arguments can contain or reference various data types, but only the index is counted.
If you want to count logical values, text, or error values, use the COUNTA function (COUNT calculates the number of cells with numeric values, while COUNTA finds the number of cells with non-empty data).
If you only want to count numbers that meet certain criteria, use the COUNTIF or COUNTIFS function.
For example, if you need to count cells B1 to B10, type =COUNT(B10:B10).
To sum cells based on multiple conditions (e.g., blue and green), use the following SUMIFS function (the first argument is the range of spreadsheets to sum).
To sum cells based on a criterion (e.g., green), use the SUMIF function with 3 arguments (the last argument being the range to sum).
The COUNTBLANK function counts empty cells.
The COUNTBLANK function is categorized under Excel's statistical functions. You use this function when you need to count empty cells.
In financial analysis, this function can be useful for highlighting or counting blank cells within a given range.
The syntax of the function is:
=COUNTBLANK(range of spreadsheets to count)
In there:
The spreadsheet range to count specifies the range of cells where you want to count empty cells.
As a worksheet function, COUNTBLANK can be entered as part of a formula in a worksheet cell.
If desired, you can use conditional formatting to highlight rows with blank cells using the COUNTBLANK function. Select the desired range and, within the selected conditional formatting, apply the COUNTBLANK() function. This will then highlight all blank cells within the desired range.
The COUNTA function counts non-empty cells.
The COUNTA function is used to count the number of cells that contain any content, including numbers, letters, or symbols; in other words, it counts non-empty cells.
Syntax:
=COUNTA(range of spreadsheets to count).
The COUNTA function counts cells that contain values, including numbers, text, boolean values, errors, and blank text (""). COUNTA does not count blank cells.
The COUNTA function returns the number of values in the given list of arguments.
COUNTA accepts multiple arguments in the form of value1, value2, value3, etc. The arguments can be individual hardcoded values, cell references, or ranges, up to a total of 255 arguments.
All values are calculated, including text, numbers, percentages, errors, dates, times, fractions, and formulas that return empty strings ("") or blank cells are ignored.
SUM function
The SUM function in Excel adds numerical values within a range of cells, categorized by mathematical and trigonometric functions.
The function is entered by typing “=SUM” followed by the values to be summed. The values provided to the function can be numbers, cell references, or ranges.
The SUM formula automatically updates when a user inserts or deletes a value. It also includes changes made to an existing range of cells. Furthermore, the function automatically ignores empty cells and text values.
For example, you want to add the numbers in cells A2 and B2 together, and then display the result in cell B3.
To do this, simply move to cell B3, type "=SUM", and select the =SUM function that appears in the pop-up list.
Next, press the Ctrl key while clicking on cells A2 and B2, and finally press the Enter key.
The sum of the two numbers in cells A2 and B2 that you just selected will appear instantly in cell B3.
You can use the SUM function to calculate the sum of two or more cells simply by selecting the necessary cells and adding them to the function's content.
Conditional summation function SUMIF
The SUMIF function aggregates cells that meet the given criteria. The criteria are based on dates, numbers, and text. It supports logical operators such as (>, <, =) and wildcards (*, ?).
The general formula for the SUMIF function in Excel is:
=SUMIF(range, criteria, [sum_range])
Arguments:
range: (required argument) This is the range of cells to which you want to apply the criteria.
criteria: (required argument) These are the criteria used to determine which cells need to be added.
The criteria argument can be: a numerical value (which can be an integer, decimal number, date, time, or logical value), a text string, or an expression.
sum_range: (optional argument) This is an array of numeric values (or cells containing numeric values) that will be summed together if the corresponding range entry meets the provided criteria.
If the [sum_range] argument is omitted, the values from the range argument will be summed instead.
For example, to sum cells based on a condition (e.g., greater than 9), use the following SUMIF function (two arguments).
Average function
The =AVERAGE function does exactly what its name suggests: it gives the average value of the selected numbers. To calculate the average, Excel sums all the numerical values and divides them by the number of numerical values.
The AVERAGE function can handle up to 255 individual arguments, which can include numbers, cell references, ranges, arrays, and constants.
The syntax for the AVERAGE function is:
= AVERAGE (number1, [number2],...)
In there:
number1 : A number or cell reference that refers to numerical values.
number2: (Optional) A number or cell reference that refers to numerical values.
The AVERAGE function will ignore logical and numerical values entered as text. If you need to include these values in the average, see the AVERAGEA function.
If the values provided to AVERAGE contain errors, AVERAGE will return an error. You can use the AGGREGATE function to ignore the error.
For example, if you want to calculate the average value from cell A10 to cell J10, simply type =AVERAGE(A10:J10) and press Enter. The result displayed in cell K10 will be the average value between cells A10 and J10.
The AVERAGE function calculates the average of the numbers provided as arguments.
Alternatively, you can use the mouse cursor to drag and drop and select the data range, or you can simultaneously press the Ctrl key and click on each cell individually if the cells are not adjacent.
However, note that the zero value (0) in C5 is included in the average, as it is a valid numerical value. To exclude zero values, use AVERAGEIF or AVERAGEIFS instead.
Min and Max functions
To find the lowest value (smallest number) in a set of values, use the MIN function.
The MIN function has the following arguments in its syntax:
number 1: mandatory
Numbers 2 to 255: optional
For each argument, you can enter a number, a name, an array, or a spreadsheet reference where the number is stored.
If the argument is a reference or array, any empty cells, logical values (true or false), or text values are ignored when calculating the minimum value.
If the range does not contain any numeric values, the MIN formula will return zero.
If you want to find the smallest number in a data range, the =MIN function can do that. Simply type in the phrase =MIN(D3:J13) , and Excel will return the smallest number within that range.
In contrast to the =MIN function mentioned earlier, the =MAX function will return the largest number within the desired range.
The syntax of this function is similar to the =MIN function, consisting of the addresses of the first and last cells. The Excel MAX function has the following arguments in its syntax:
number 1: mandatory
Numbers 2 to 255: optional
For each argument, you can enter a number, a name, an array, or a spreadsheet reference where the number is stored.
If the argument is a reference or array, any empty cells, logical values, or text are ignored when calculating the maximum value.
If the range does not contain any numerical values, then the result of the MAX formula is 0.
In Excel 2019 or Excel for Office 365, you can use the MINIFS and MAXIFS functions to find the minimum or maximum value based on one or more criteria. For earlier versions of Excel, the MINIFS and MAXIFS functions are not available.
Vu Huyen (compiled)
Beneficial
Emotion
Creative
Unique
Source
Comment (0)