SUM-Summation
1. Overview
| Syntax | SUM(number1,number2,...) | To sum all the numbers in a specified cell area. |
|---|---|---|
| Parameter | number1,number2,... | Parameters from No.1 to No.n or all numbers in a specified cell area. |
| Returned Value | Number | - |
The function takes into account the numeric and logical values and text expressions in the parameter. If the parameter is an array or reference, the function will only calculate the value in the array or cell reference.
3. Example
| Formula | Result | Remark |
|---|---|---|
| SUM(70,80) | 150 | - |
| SUM("70",80,TRUE) | 151 | Logical Value TRUE considered as 1 in the calculation FALSE considered as 0 in the calculation Text "70" considered as 70 in the calculation |
| SUM(B1:B2, B4:B5) | 12 |  |
COUNT-Counting
1. Overview
| Syntax | COUNT(value1,value2,...) | To count the number of the items in an array or a data area. |
|---|---|---|
| Parameter | value1,value2,... | The parameter that can contain any type of data. |
| Returned Value | Number | - |
2. Notes
Counting objects can be arrays or cells, and cells can be cell areas or extended cells.
The count function counts null values caused by empty strings, but it does not count null values caused by NULL values.
Resolvable text values, logical values, zero values, and blank cells in arrays or reference parameters will be involved in the calculation.
Unresolvable text values in arrays or reference parameters will not be involved in the calculation.
3. Example
For details, see Count Function.
AVERAGE-Averaging
1. Overview
| Syntax | AVERAGE(number1,number2,…,countstring) | To return the average value of the specified data. |
|---|---|---|
| Parameter 1 | number1,number2,... | The parameter which is used to calculate the average value. |
| Parameter 2 | countString | To determine whether text and logic values are involved in the count. false: not involved true: involved |
| Returned Value | Number | - |
Parameters must be numbers, or names, arrays or references containing numbers.
If the array or reference parameter contains text and logical values, the array or reference will be counted by default. With false of countString, the array or reference will not be counted.
The empty cell is not involved in the count.
The zero value in the cell is involved in the count.
3. Example
If cells from A1 to A6 are named ages and the cell values are 10, 23, text, 29, 33, and 25, respectively, the different results are as follows:
| Formula | Result | Remark |
|---|---|---|
| AVERAGE(A1:A6) | 20 | The text and logical values are involved in the count by default. (10 + 23 + 0 + 29 + 33 + 25)/6 = 20 |
| AVERAGE(ages) | 20 | The text and logical values are involved in the count by default. (10 + 23 + 0 + 29 + 33 + 25)/6 = 20 |
| AVERAGE(A1:A6,false) | 24 | With false of countString, the text and logical values are not involved in the count. (10 + 23 + 29 + 33 + 25)/5 = 24 |
| AVERAGE(A1:A6,27) | 21 | [(10 + 23 + 0 + 29 + 33 + 25)+ 27]/7 = 21 |
CHAR-Returning the Character
1. Overview
| Syntax | CHAR(number) | To return the corresponding character according to the specified number. To convert the computer numeric codes of other types to characters. |
|---|---|---|
| Parameter | number | The number which is used to specify the character. From 1 to 65535 |
| Returned Value | String | - |
2. Example
| Formula | Result |
|---|---|
| CHAR(88) | X |
| CHAR(45) | - |
DATE–Converting the Date
1. Overview
| Syntax | DATE(Year,Month,Day) | To return a series number representing a specific date. |
|---|---|---|
| Parameter 1 | Year | The parameter which is used to represent the year. One to four digits (From -9999 to 9999) When the Year value is less than or equal to 0, the value is considered as its absolute value plus one. |
| Parameter 2 | Month | The parameter which is used to represent the month. When the Month value is between 0 and 12, the function takes the value as the month. When the Month value is greater than 12, the function adds the number of months to January of the specified year. When the Month value is smaller than or equal to 0, the function subtracts the corresponding number of months from December of the previous year. |
| Parameter 3 | Day | The parameter which is used to represent the day. When the Day value is smaller than or equal to the number of days of a specified month, the function takes the parameter value as the day. When the Day value is greater than the number of days of a specified month, the function adds the number of days to the first day of the month. When the Day value is greater than the total number of days of two months, the function adds the remainder of the days subtracted from two or more months' days to the third or fourth month, and so on. When the Day value is smaller than or equal to 0, the function subtracts the corresponding number of days from the last day of the previous month of a specified month. |
Returned Value | String | - |
2. Notes
You can use this formula to process part of a date in a formula, such as the year or month.
This formula works best when the year, month, and day are functions rather than constants.
3. Example
| Formula | Result | Remark |
|---|---|---|
| DATE(1978,09,19) | 1978-09-19 | - |
| DATE(1211,12,1) | 1211-12-01 | - |
| DATE(-1999,10,10) | 2000-10-10 | As the Year value is less than or equal to 0, the value is considered as its absolute value plus one. |
| DATE(2000,25,2) | 2002-01-02 | As the Month value is greater than 12, the function adds the number of months to January of the specified year. |
| DATE(2000,0,2) | 1999-12-02 | As the Month value is smaller than or equal to 0, the function subtracts the corresponding number of months from December of the previous year. |
| DATE(2019,4,15) | 2019-04-15 | When the Day value is smaller than or equal to the number of days of a specified month, the function takes the parameter value as the day. |
| DATE(2000,3,35) | 2000-04-04 | As the Day value is greater than the number of days of a specified month, the function adds the number of days to the first day of the month. |
| DATE(2019,2,89) | 2019-04-30 | When the Day value is greater than the total number of days of two months, the function adds the remainder of the days subtracted from two or more months' days to the third or fourth month, and so on. |
| DATE(2000,5,-2) | 2000-04-28 | As the Day value is smaller than or equal to 0, the function subtracts the corresponding number of days from the last day of the previous month of a specified month. |
MAX-Finding the Maximum Value
1. Overview
| Syntax | MAX(number1,number2,…) | To return the maximum value in the parameter list. |
|---|---|---|
| Parameter | number1,number2,... | The parameter from which the maximum value needs to be found. |
| Returned Value | Number | - |
The parameter can be numbers, blank cells, logical values, or text expressions representing numeric values.
Resolvable text values, logical values, zero values, and blank cells in arrays or reference parameters will be involved in the calculation.
Unresolvable text values in arrays or reference parameters will not be involved in the calculation.
With no number in the parameter, 0 will be returned as the maximum value.
3. Example
| Formula | Result |
|---|---|
| MAX(0.1,0,1.2) | 1.2 |
| MAX(Text) | 0 |
MIN-Finding the Minimum Value
1. Overview
| Syntax | MIN(number1,number2,…) | To return the minimum value in the parameter list. |
|---|---|---|
| Parameter | number1,number2,... | The parameter from which the minimum value needs to be found. |
| Returned Value | Number | - |
2. Notes
The parameter can be numbers, blank cells, logical values, or text expressions representing numeric values.
Resolvable text values, logical values, zero values, and blank cells in arrays or reference parameters will be involved in the calculation.
Unresolvable text values in arrays or reference parameters will not be involved in the calculation.
With no number in the parameter, 0 will be returned as the minimum value.
MIN returns an error message if the parameter is an error value.
3. Example
If cells from B1 to B4 contain values of 3, 6, 9, and 12, the different results are as follows:
| Formula | Result |
|---|---|
| MIN(B1:B4) | 3 |
| MIN(B1:B4,0) | 0 |
| MIN(Text) | 0 |
TIME-Returning the Time and Date
1. Overview
| Syntax | TIME(Hour,Minute,Second) | To return the specified date and time. The date is the date of the day. The time is between 0:00:00 and 23:59:59. This function can return the corresponding value between 0 and 0.99999999. |
|---|---|---|
| Parameter 1 | Hour | The hour number, which is from 0 to 23. |
| Parameter 2 | Minute | The minute number, which is from 0 to 59. |
| Parameter 3 | Second | The second number, which is from 0 to 59. |
| Returned Value | Number | - |
2. Example
| Formula | Result |
|---|---|
| TIME(14,40,0) | 2020-06-22 14:40:00 |
| TIME(19,43,24) | 2020-06-22 19:43:24 |
RANGE-Returning the Array
1. Overview
| Syntax | RANGE() | RANGE(from,to,step): returns a sequence of numbers starting from the integer from, increasing by step each time, and up to the integer to. RANGE(from,to): (the default value of step is 1) returns a sequence of numbers starting from the integer from, increasing by 1 each time, and up to the integer to. RANGE(to): (the default values of from and step are 1.) returns a sequence of numbers starting from 1, increasing by 1, and up to the integer to. |
|---|---|---|
| Parameter 1 | From | The starting value, which is an integer, and the null value represents 1 by default. |
| Parameter 2 | To | The ending value, which is an integer. |
| Parameter 3 | Step | The step value, and the null value represents 1 by default. |
| Returned Value | Array | - |
2. Example
| Formula | Result | Remark |
|---|---|---|
| RANGE(4) | 1, 2, 3, 4 | To return an array that starts from 1, increases by 1 each time, and goes up to 4. |
| RANGE(-5) | To return the null value. | As no array starts from 1, increases by 1 each time, and goes up to -5, the null value is returned. |
| RANGE(-1,3) | -1, 0, 1, 2, 3 | To return an array that starts from -1, increases by 1 each time, and goes up to 3. |
| RANGE(6,-1,-2) | 6, 4, 2, 0 | To return an array that starts from 6, decreases by 2 each time, and goes up to -1. |
| RANGE(4,1,1) | To return the null value. | As no array starts from 4, increases by 1 each time, and goes up to 1, the null value is returned. |
| RANGE(1,6,2) | 1, 3, 5 | To return an array that starts from 1, increases by 2 each time, and goes up to 6. |
Previous：Entry-Level Calculation Guide
