Difference between revisions of "Manuals/calci/Average, AverageA, AverageX Example"
Jump to navigation
Jump to search
(Created page with "<div style="font-size:30px">'''DATA'''</div><br/> {| class="wikitable" |+Spreadsheet |- ! !! A !! B !!C !!D |- ! 1 | ID || Amount || Blank || Boolean || SalesDate ||...") |
|||
| (18 intermediate revisions by one other user not shown) | |||
| Line 1: | Line 1: | ||
| − | <div style="font-size:30px">'''DATA'''</div | + | <div style="font-size:30px">'''DATA'''</div> |
{| class="wikitable" | {| class="wikitable" | ||
| − | |||
|- | |- | ||
| − | ! !! A !! B !!C !!D | + | ! !! A !! B !!C !!D !! E !! F |
|- | |- | ||
! 1 | ! 1 | ||
| Line 21: | Line 20: | ||
| 4 || 1000 || || FALSE || 28/5/2020 || C | | 4 || 1000 || || FALSE || 28/5/2020 || C | ||
|} | |} | ||
| − | |||
| + | |||
| + | <div style="font-size:24px">'''AVERAGE()'''</div> | ||
| + | *Parameters are values or range of cells containing values. | ||
| + | *AVERAGE() returns the average (arithmetic mean) of all the numbers in a column.<br></br> | ||
| + | |||
| + | <div style="font-size:16px">'''NOTE:'''</div> | ||
| + | *This function takes the specified column as an argument and finds the average of the values in that column. | ||
| + | *Does not support Logical and Text Values. | ||
| + | *Cells with the value zero are included. | ||
| + | *If Data type is Integer/ Numeric/ Decimal, Average skip the null value. | ||
| + | '''AVERAGE(A2:A5) = 2.5<br> | ||
| + | '''AVERAGE(B2:B5) = 1333.33<br></br> | ||
| + | |||
| + | |||
| + | <div style="font-size:24px">'''AVERAGEA()'''</div> | ||
| + | *AVERAGEA() returns the average (arithmetic mean) of the values in a column. Handles text and non-numeric values.<br></br> | ||
| + | |||
| + | <div style="font-size:16px">'''NOTE:'''</div> | ||
| + | The AVERAGEA function takes a column and averages the numbers, but also handles non-numeric data types according to the following rules: | ||
| + | |||
| + | *It support Logical values. | ||
| + | *Logical value TRUE counts as 1 & FALSE counts as 0. | ||
| + | *Values that contain non-numeric text count as 0. | ||
| + | *Empty text (" ") counts as 0. | ||
| + | '''AVERAGEA(A2:A5) = 2.5<br> | ||
| + | '''AVERAGEA(B2:B5) = 1333.33<br> | ||
| + | '''AVERAGEA(D2:D5) = 0.5<br></br> | ||
| − | <div style="font-size: | + | <div style="font-size:24px">'''AVERAGEX()'''</div> |
| + | *AVERAGEX() calculates the average (arithmetic mean) of a set of expressions evaluated over a table.<br></br> | ||
| − | + | <div style="font-size:16px">'''NOTE:'''</div> | |
| − | + | The AVERAGEX function takes a column and averages the numbers, but also handles non-numeric data types according to the following rules: | |
| − | = | + | *The function takes a table as its first argument, and an expression as the second argument. |
| + | *The AVERAGEX function enables you to evaluate expressions for each row of a table, and then take the resulting set of values and calculate its arithmetic mean. | ||
| + | *You cannot include non-numeric or null cells. Both the table and expression arguments are required. | ||
| + | *When there are no rows to aggregate, the function returns a blank. When there are rows, but none of them meet the specified criteria, then the function returns 0. | ||
| + | *Does not support Logical/ boolean values. | ||
| + | '''AVERAGEX(A2:A5) = 2.5<br> | ||
Latest revision as of 13:51, 19 October 2021
DATA
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | ID | Amount | Blank | Boolean | SalesDate | Name |
| 2 | 1 | TRUE | 29/5/2020 | A | ||
| 3 | 2 | 1000 | FALSE | B | ||
| 4 | 3 | 2000 | TRUE | 29/5/2020 | ||
| 5 | 4 | 1000 | FALSE | 28/5/2020 | C |
AVERAGE()
- Parameters are values or range of cells containing values.
- AVERAGE() returns the average (arithmetic mean) of all the numbers in a column.
NOTE:
- This function takes the specified column as an argument and finds the average of the values in that column.
- Does not support Logical and Text Values.
- Cells with the value zero are included.
- If Data type is Integer/ Numeric/ Decimal, Average skip the null value.
AVERAGE(A2:A5) = 2.5
AVERAGE(B2:B5) = 1333.33
AVERAGEA()
- AVERAGEA() returns the average (arithmetic mean) of the values in a column. Handles text and non-numeric values.
NOTE:
The AVERAGEA function takes a column and averages the numbers, but also handles non-numeric data types according to the following rules:
- It support Logical values.
- Logical value TRUE counts as 1 & FALSE counts as 0.
- Values that contain non-numeric text count as 0.
- Empty text (" ") counts as 0.
AVERAGEA(A2:A5) = 2.5
AVERAGEA(B2:B5) = 1333.33
AVERAGEA(D2:D5) = 0.5
AVERAGEX()
- AVERAGEX() calculates the average (arithmetic mean) of a set of expressions evaluated over a table.
NOTE:
The AVERAGEX function takes a column and averages the numbers, but also handles non-numeric data types according to the following rules:
- The function takes a table as its first argument, and an expression as the second argument.
- The AVERAGEX function enables you to evaluate expressions for each row of a table, and then take the resulting set of values and calculate its arithmetic mean.
- You cannot include non-numeric or null cells. Both the table and expression arguments are required.
- When there are no rows to aggregate, the function returns a blank. When there are rows, but none of them meet the specified criteria, then the function returns 0.
- Does not support Logical/ boolean values.
AVERAGEX(A2:A5) = 2.5