Catégorie : Excel function

The AVERAGE function calculates the arithmetic mean of a specified set of values in a worksheet. This function can accept up to 255 arguments, including cell references, ranges, arrays, and constants.

The AVERAGE function uses the following arguments:

=AVERAGE(number1; [number2]; …) 

Number1 (Required Argument): The first number, cell reference, or range for which you want to calculate the average.
Number2 (Optional Argument): Additional numbers, cell references, or ranges to include in the average calculation (up to 255 total arguments).

USING THE AVERAGE FUNCTION
Let’s calculate the average sales of goods in the table below using the AVERAGE function.

To find the average sales:

1. Select an empty cell and enter:

=AVERAGE(B2:B5)

1. Press Enter to display the result: 560,25 (as shown in the table below).

NOTE: Important considerations when using the AVERAGE function:

• Empty cells are automatically excluded from the calculation
• Text and logical values in cell references are ignored
• Cells containing zero (0) are included in the calculation
• All referenced cells must contain numeric values
• To include logical values and text representations of numbers, use the AVERAGEA function
• For conditional average calculations, use the AVERAGEIF or AVERAGEIFS functions

The COUNTBLANK function calculates the number of empty cells within a specified range in a worksheet.

The COUNTBLANK function requires a single argument:

=COUNTBLANK(range) 

Range (Required Argument): This specifies the cell range where blank cells should be counted.

USING THE COUNTBLANK FUNCTION
Let’s determine the number of blank cells in the table below using the COUNTBLANK function.

To count blank cells, follow these steps:

1. Select an empty cell and enter:

=COUNTBLANK(A1:C5)

1. Press Enter to display the result: 2 (as shown in the table below).

NOTE: Important notes when using the COUNTBLANK function:

• Excludes cells containing text, numbers, errors, or other non-blank values
• Counts cells with formulas that return empty results as blank
• Does not count cells containing zeros (0) as blank

The COUNTA function is a function that returns the number of non-empty cells within a specified range. This function excludes empty cells from its count. The COUNTA function may also be known as the Excel COUNTIF Not Blank formula.

The COUNTA function uses the following arguments:

=COUNTA(value1; [value2]; …) 

Value1 (Required Argument): This represents the first set of values to be counted.
Value2,… (Optional Argument): These are additional values to include in the count, with a maximum of 255 total arguments permitted.

USING THE COUNTA FUNCTION
Using the table below, we will determine the number of non-empty cells by applying the COUNTA function.

To calculate the quantity of non-empty cells in your worksheet, follow these steps:

1. Select an empty cell and input:

=COUNTA(A1:C5) 

1. Press Enter to display the result: 15 (as illustrated in the table below).

NOTE: Important considerations when working with the COUNTA function:

• Empty cells are automatically excluded from the count
• For counts excluding logical values, text, or error messages, utilize the COUNT function instead
• To implement conditional counting based on specific criteria, employ either the COUNTIF or COUNTIFS functions

The COUNTIFS function is a function used for counting cells that meet single or multiple conditions or criteria. Just like the COUNT and COUNTIF functions, the COUNTIFS function is used with criteria or conditions relating to numbers, dates, text, logical operators, and wildcards.

The COUNTIFS function has the following arguments:

=COUNTIFS(criteria_range1; criteria1; [criteria_range2; criteria2]…) 

Criteria_range1 (Required Argument): This is the first range to be evaluated with the associated criteria.
Criteria1 (Required Argument): This is the condition to be applied to criteria_range1, which may be an expression, number, cell reference, or text specifying which cells to count. For example, criteria can be expressed as 43, « >23 », D2, etc.
Criteria_range2, criteria2 (Optional Argument): These are additional ranges and their associated criteria. The function allows up to 127 range/criteria pairs. The criteria can be:

• A numerical value (integer, decimal, time, or logical value)
• A text string (e.g., « Monday », « East », « Price ») including wildcards (*, ?)

USING THE COUNTIFS FUNCTION
Using the table below, let’s count the number of shoes that are red.

To count red shoes in the list, follow these steps:

1. Select an empty cell and enter:

=COUNTIFS(A2:A5; « sneakers »; B2:B5; « red »)

1. Press Enter. The result will be 2, as shown in the table.

NOTE: Remember these points when using COUNTIFS:

• COUNTIFS treats empty cells as 0 when criteria reference an empty cell.
• #VALUE! error occurs if:
  • Criteria ranges have different lengths
  • Text criteria exceed 255 characters
• Wildcards (*, ?) can be used in criteria.
• The function evaluates each cell pair sequentially:
  • If the first cell pair meets criteria, count increases by 1
  • If the second pair meets criteria, count increases again by 1
  • This continues until all cells are evaluated

The COUNTIF function is used to count the number of cells that meet a certain criterion or condition. This can also be used to count cells that contain dates, numbers, and text. This function also supports the use of logical operators and wildcards.

The COUNTIF function has the following arguments:

=COUNTIF(Range; criteria) 

Range (Required Argument): This indicates the range of cells that are to be counted.
Criteria (Required Argument): This is the condition to be met by the cells provided in the worksheet. The criteria can be in the following:

• A numerical value such as integer, decimal, time, or logical value
• A text string such as Monday, East, Price and including wildcards such as asterisks and question mark

USING THE COUNTIF FUNCTION
With the table below, let’s use the COUNTIF function to count how many times James’ name appears on the list.

To get the number of times James’ name appears on the list, follow the steps below:

1. Select an empty cell, type in the function name and the arguments to be used:

=COUNTIF(B2:B6; « James ») 

1. Click on Enter and the result will be 2.

NOTE: Take note of the following points when using the COUNTIF function:

• When using the COUNTIF function, make sure the criteria argument is enclosed in quotes (e.g., « James »).
• When the provided criteria argument is a text string that is more than 255 characters in length, #VALUE! error occurs.
• #VALUE! error also occurs when the formula is referring to a cell or range of cells in a closed workbook.

The COUNT function is a function that counts the number of cells containing numbers, as well as the number of arguments that include numerical values.

The COUNT function uses the following syntax:

• Value1 (Required Argument): The cell range or value you want to include in the count (must contain numbers).
• Value2, … (Optional Argument): You can add up to 255 additional items, cell references, or ranges where numbers should be counted.

USING THE COUNT FUNCTION

Using the table below, we will count how many cells contain numbers with the COUNT function.

To count cells with numbers, follow these steps:

1. Select an empty cell.
2. Type the function followed by the range:

=COUNT(A2:B5)

  Press Enter. The result will be 3.

NOTE: IMPORTANT POINTS ABOUT THE COUNT FUNCTION

• Counted Values: Numbers, dates, and text representations of numbers (e.g., « 10 »).
• Not Counted: Text values, errors, or empty cells.
• For counting logical values (TRUE/FALSE), use COUNTA.
• For counting numbers based on conditions, use COUNTIF or COUNTIFS.
• The COUNT function does not count logical values (TRUE or FALSE).
• If an argument is an array or reference, only numbers within it are counted.