OneNote Table Formula
Table formula is a set of Gem for OneNote features. You can perform calculations and logical comparisons in a table by using formulas.
Where is the Formula for OneNote Table
OneNote table formula is hidden in the second "Gem" tab. It will appear when you select a table in current OneNote page.
At first, create or select a table in current OneNote page. Then, you will find "Table Tools" tab set appear on the OneNote ribbon, there are 2 hidden tabs are shown in "Table Tools" set, they are "Layout" and "Gem" ( the second Gem tab ).
The features in this second "Gem" tab are used for table only.
In there, you will find the "Table Formula" group.
Where is the Formula for OneNote Table
OneNote table formula is hidden in the second "Gem" tab. It will appear when you select a table in current OneNote page.
At first, create or select a table in current OneNote page. Then, you will find "Table Tools" tab set appear on the OneNote ribbon, there are 2 hidden tabs are shown in "Table Tools" set, they are "Layout" and "Gem" ( the second Gem tab ).
The features in this second "Gem" tab are used for table only.
In there, you will find the "Table Formula" group.
Insert a Formula in a Table Cell
Select a table cell or a group of cells where you want to insert formula.
On the "Table Tools" -> "Gem" tab -> "Formula" group -> Click "Formula".
It will pop up a "Formula" dialog.
In the Formula dialog:
Click "OK" button, it will save the formula into the selected cell, and calculate and display and format the result into the cell.
If you have selected multiple table cells, Gem will save the formula to the selected cells, it also auto taking account of any relative references you had in the original formula.
For Example: Insert formula D2+B3-C3 into cells from D3 to D14
The relative formula are:
Select a table cell or a group of cells where you want to insert formula.
On the "Table Tools" -> "Gem" tab -> "Formula" group -> Click "Formula".
It will pop up a "Formula" dialog.
In the Formula dialog:
- Type the formula in the "Formula" box.
- Type the number format or select a number format from the "Format" list. It help format the number of the formula result and display in the table cell.
- Function list the available functions.
Click "OK" button, it will save the formula into the selected cell, and calculate and display and format the result into the cell.
If you have selected multiple table cells, Gem will save the formula to the selected cells, it also auto taking account of any relative references you had in the original formula.
For Example: Insert formula D2+B3-C3 into cells from D3 to D14
The relative formula are:
- Cell D3, Formula is D2+B3-C3
- Cell D4, Formula is D3+B4-C4
- Cell D5, Formula is D4+B5-C5
- ……
Modify the Formula Saved in Cell
If you want to modify the cell formula, select the cell, and click the "Formula" feature on the OneNote ribbon again.
Still in the "Formula" dialog to change the formula.
Remove the Cell Formula
If you want to remove the formula from cell, select the cell, click "Delete" feature on OneNote ribbon, it will remove the formulas from selected cells.
Highlight the Formula Cells
If you want to find out which cells contain formula, click the "Formula Cells" feature on the OneNote ribbon, it will select all the formula cells in the table.
Update Formula Results
If you change some of the number in table, the formula don't automatically recalculate the results, you need to manually to update them.
Click "Recalculate" feature on the OneNote ribbon, it will recalculate all the formula results in the table.
If you want to modify the cell formula, select the cell, and click the "Formula" feature on the OneNote ribbon again.
Still in the "Formula" dialog to change the formula.
Remove the Cell Formula
If you want to remove the formula from cell, select the cell, click "Delete" feature on OneNote ribbon, it will remove the formulas from selected cells.
Highlight the Formula Cells
If you want to find out which cells contain formula, click the "Formula Cells" feature on the OneNote ribbon, it will select all the formula cells in the table.
Update Formula Results
If you change some of the number in table, the formula don't automatically recalculate the results, you need to manually to update them.
Click "Recalculate" feature on the OneNote ribbon, it will recalculate all the formula results in the table.
A1 References and a Range of Cells
You can refer to a cell, a set of cells, or a range of cells by using the A1 reference convention. In this convention, the letter refers to the cell’s column and the number refers to the cell’s row. The first column in a table is column A; the first row is row 1. The following table contains examples of this reference style.
The cell in the first column and the second row
A2
The first two cells in the first row
A1,B1
All the cells in the first column and the first two cells in the second column
A1:B2
The first row
1:1
The first column
A:A
You can refer to a cell, a set of cells, or a range of cells by using the A1 reference convention. In this convention, the letter refers to the cell’s column and the number refers to the cell’s row. The first column in a table is column A; the first row is row 1. The following table contains examples of this reference style.
The cell in the first column and the second row
A2
The first two cells in the first row
A1,B1
All the cells in the first column and the first two cells in the second column
A1:B2
The first row
1:1
The first column
A:A
Arithmetic Operators
Arithmetic operators include: +, -, * (Multiplication), / (Division)
Example 1: Cell D2 + Cell B3 - Cell C3
D2+B3-C3
Example:
Comparison Operators
Comparison operators include: <, >, <=, >=, =, <>
Example:
Arithmetic operators include: +, -, * (Multiplication), / (Division)
Example 1: Cell D2 + Cell B3 - Cell C3
D2+B3-C3
Example:
- If( (D2+B3-C3)>(D3+B4-C4), (D2+B3-C3), (D3+B4-C4) )
- SUM(A:A) * SUM(B:B)
Comparison Operators
Comparison operators include: <, >, <=, >=, =, <>
Example:
- If( D3>D4, D3, D4)
- AND(SUM(LEFT)<10,SUM(ABOVE)>=5)
Available Functions
Expr is a sub formula.
ABS(Expr)
Calculates the absolute value of the value inside the parentheses.
Example: ABS(C3-(D2+B3)) Result: 1500.64
AND(Expr, Expr, ….)
Evaluates whether the arguments inside the parentheses are all 1 or 0.
Example: AND( (C4-C3)<0, (C6-C5)>0) Result: 1
AVERAGE(Expr, Expr, …)
Calculates the average of items identified inside the parentheses.
Example: AVERAGE(C3,C4,C5) Result: 73.67
COUNT(Expr)
Calculates the count of items identified inside the parentheses.
Example: COUNT(BELOW) Result:
The number of values to the below of the formula cell, in the same column.
IF( ConditionExpr, TrueExpr, FalseExpr)
Evaluates the first argument. Returns the second argument if the first argument is true; returns the third argument if the first argument is false.
Example: IF(C3>10, 100, -100) Result: 100
INT(Expr)
Rounds the value inside the parentheses down to the nearest integer.
Example: INT(D2+B3-C3) Result: 1500
MAX(Expr, Expr, …)
Returns the maximum value of the items identified inside the parentheses.
Example: MAX(C3, C4, C5) Result: 90
MIN(Expr, Expr, …)
Returns the minimum value of the items identified inside the parentheses.
Example: MIN(C3, C4, C5) Result: 42
MOD(Expr1, Expr2)
Takes two arguments (must be numbers or evaluate to numbers). Returns the remainder after the second argument is divided by the first. If the remainder is 0 (zero), returns 0.0
Example: MOD(4, 2) Result: 0.0
NOT(Expr)
Takes one argument. Evaluates whether the argument is true. Returns 0 if the argument is true, 1 if the argument is false. Mostly used inside an IF formula.
Example: NOT(C3=0) Result: 1
OR(Expr1, Expr2)
Takes two arguments. If either is true, returns 1. If both are false, returns 0. Mostly used inside an IF formula.
Example: OR(C3<100, C4>40) Result: 1
ROUND(Expr1, Integer)
Takes two arguments (first argument must be a number or evaluate to a number; second argument must be an integer or evaluate to an integer). Rounds the first argument to the number of digits specified by the second argument. If the second argument is greater than zero (0), first argument is rounded down to the specified number of digits. If second argument is zero (0), first argument is rounded down to the nearest integer. If second argument is negative, first argument is rounded down to the left of the decimal.
ROUND(123.456, 2) Result: 123.46
ROUND(123.456, 0) Result: 123
ROUND(123.456, -2) Result: 100
SIGN( Expr )
Takes one argument that must either be a number or evaluate to a number. Evaluates whether the item identified inside the parentheses if greater than, equal to, or less than zero (0). Returns 1 if greater than zero, 0 if zero, -1 if less than zero.
Example: SIGN(-11) Result: -1
SUM( Expr, Expr, … )
Calculates the sum of items identified inside the parentheses.
Examples:
Expr is a sub formula.
ABS(Expr)
Calculates the absolute value of the value inside the parentheses.
Example: ABS(C3-(D2+B3)) Result: 1500.64
AND(Expr, Expr, ….)
Evaluates whether the arguments inside the parentheses are all 1 or 0.
Example: AND( (C4-C3)<0, (C6-C5)>0) Result: 1
AVERAGE(Expr, Expr, …)
Calculates the average of items identified inside the parentheses.
Example: AVERAGE(C3,C4,C5) Result: 73.67
COUNT(Expr)
Calculates the count of items identified inside the parentheses.
Example: COUNT(BELOW) Result:
The number of values to the below of the formula cell, in the same column.
IF( ConditionExpr, TrueExpr, FalseExpr)
Evaluates the first argument. Returns the second argument if the first argument is true; returns the third argument if the first argument is false.
Example: IF(C3>10, 100, -100) Result: 100
INT(Expr)
Rounds the value inside the parentheses down to the nearest integer.
Example: INT(D2+B3-C3) Result: 1500
MAX(Expr, Expr, …)
Returns the maximum value of the items identified inside the parentheses.
Example: MAX(C3, C4, C5) Result: 90
MIN(Expr, Expr, …)
Returns the minimum value of the items identified inside the parentheses.
Example: MIN(C3, C4, C5) Result: 42
MOD(Expr1, Expr2)
Takes two arguments (must be numbers or evaluate to numbers). Returns the remainder after the second argument is divided by the first. If the remainder is 0 (zero), returns 0.0
Example: MOD(4, 2) Result: 0.0
NOT(Expr)
Takes one argument. Evaluates whether the argument is true. Returns 0 if the argument is true, 1 if the argument is false. Mostly used inside an IF formula.
Example: NOT(C3=0) Result: 1
OR(Expr1, Expr2)
Takes two arguments. If either is true, returns 1. If both are false, returns 0. Mostly used inside an IF formula.
Example: OR(C3<100, C4>40) Result: 1
ROUND(Expr1, Integer)
Takes two arguments (first argument must be a number or evaluate to a number; second argument must be an integer or evaluate to an integer). Rounds the first argument to the number of digits specified by the second argument. If the second argument is greater than zero (0), first argument is rounded down to the specified number of digits. If second argument is zero (0), first argument is rounded down to the nearest integer. If second argument is negative, first argument is rounded down to the left of the decimal.
ROUND(123.456, 2) Result: 123.46
ROUND(123.456, 0) Result: 123
ROUND(123.456, -2) Result: 100
SIGN( Expr )
Takes one argument that must either be a number or evaluate to a number. Evaluates whether the item identified inside the parentheses if greater than, equal to, or less than zero (0). Returns 1 if greater than zero, 0 if zero, -1 if less than zero.
Example: SIGN(-11) Result: -1
SUM( Expr, Expr, … )
Calculates the sum of items identified inside the parentheses.
Examples:
- SUM(LEFT)
- SUM(LEFT,ABOVE,RIGHT,BELOW)
- SUM(C:C)
- SUM(C1:C100)
