Buzz
Compilation of commonly used statistical functions in Excel
1. Count Function - Counting numerical data
- Syntax: Count(range of data to count).
- Meaning: Used to count the total number of cells containing numerical values.
- Example:
2. Counta Function - Count all cells with data (unlike count, it counts cells with non-numerical data)
- Syntax: Counta(range of data to count).
- Example:
3. Countif Function - Count data based on a single condition
- Syntax: Countif (range of data containing search values, search condition).
- The function can handle a maximum of 127 conditions.
- Example: Calculate the total sales of products on 18/4/2015.
4. Countifs Function
- Syntax: Countifs( range of condition data 1, condition 1, range of condition data 2, condition 2, …..).
- Example: Calculate the total number of products sold on 18/4/2015 with a unit price of 17000.
5. Countblank Function - Count the number of empty cells in the table (note that the value 0 is not considered empty)
- Syntax: Countblank (range of data to count).
- Example:
6. Rank Function
- If you want to find out your exam score ranking, use the rank function.
- Syntax: Rank(value to be sorted, range of data to sort, sorting order).
In which:
Sorting order has 2 values:
+ Value = 0 sorts in descending order.
+ Value = 1 sorts in ascending order.
- Example: Explore the product with code HGSD to find its ranking among the items.
Problem analysis:
+ Based on the quantity sold, calculate the ranking of the best-selling product among the items.
+ Check the sales value of the 'HGSD' product with 7 units sold to determine its position in the quantity column. This order is the ranking of the best-selling products.
7. Average Function - Calculate the average value
- Syntax: Average(value 1, value 2, value 3,…..).
- Example: Calculate the average unit price of the products.
- Problem analysis: To calculate the average unit price of the products, calculate the average value of the cells in the price column.
8. Column, Columns Function
Highly useful in Excel files containing large datasets.
- Column Function: Used to determine the column number of a data cell in the table.
+ Syntax: Column(address of the cell to determine its column).
+ Column order is calculated from the first column (column A) in Excel.
+ Example:
- Columns Function: Counts the total number of columns in a data range.
+ Syntax: Columns(range of data to determine the number of columns).
+ Example:
