OneNote Gem Add-Ins

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.

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:

• 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.

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

Arithmetic Operators
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)

Format (Display Format)

 

Date

Display result as date format: 2020-01-15
 

Time

Display result as time format: 09:12:13
 

DateTime

Display result as date time format: 2020-01-15 09:12:13
 

Date:yyyy/mm/dd hh:nn:ss

Display as customize date & time format
 

Specifier	Displays
c	Displays the date using the format given by the ShortDateFormat global variable, followed by the time using the format given by the LongTimeFormat global variable. The time is not displayed if the date-time value indicates midnight precisely.
d	Displays the day as a number without a leading zero (1-31).
dd	Displays the day as a number with a leading zero (01-31).
ddd	Displays the day as an abbreviation (Sun-Sat) using the strings given by the ShortDayNames global variable.
dddd	Displays the day as a full name (Sunday-Saturday) using the strings given by the LongDayNames global variable.
ddddd	Displays the date using the format given by the ShortDateFormat global variable.
dddddd	Displays the date using the format given by the LongDateFormat global variable.
e	(Windows only) Displays the year in the current period/era as a number without a leading zero (Japanese, Korean, and Taiwanese locales only).
ee	(Windows only) Displays the year in the current period/era as a number with a leading zero (Japanese, Korean, and Taiwanese locales only).
g	(Windows only) Displays the period/era as an abbreviation (Japanese and Taiwanese locales only).
gg	(Windows only) Displays the period/era as a full name (Japanese and Taiwanese locales only).
m	Displays the month as a number without a leading zero (1-12). If the m specifier immediately follows an h or hh specifier, the minute rather than the month is displayed.
mm	Displays the month as a number with a leading zero (01-12). If the mm specifier immediately follows an h or hh specifier, the minute rather than the month is displayed.
mmm	Displays the month as an abbreviation (Jan-Dec) using the strings given by the ShortMonthNames global variable.
mmmm	Displays the month as a full name (January-December) using the strings given by the LongMonthNames global variable.
yy	Displays the year as a two-digit number (00-99).
yyyy	Displays the year as a four-digit number (0000-9999).
h	Displays the hour without a leading zero (0-23).
hh	Displays the hour with a leading zero (00-23).
n	Displays the minute without a leading zero (0-59).
nn	Displays the minute with a leading zero (00-59).
s	Displays the second without a leading zero (0-59).
ss	Displays the second with a leading zero (00-59).
z	Displays the millisecond without a leading zero (0-999).
zzz	Displays the millisecond with a leading zero (000-999).
t	Displays the time using the format given by the ShortTimeFormat global variable.
tt	Displays the time using the format given by the LongTimeFormat global variable.
am/pm	Uses the 12-hour clock for the preceding h or hh specifier, and displays 'am' for any hour before noon, and 'pm' for any hour after noon. The am/pm specifier can use lower, upper, or mixed case, and the result is displayed accordingly.
a/p	Uses the 12-hour clock for the preceding h or hh specifier, and displays 'a' for any hour before noon, and 'p' for any hour after noon. The a/p specifier can use lower, upper, or mixed case, and the result is displayed accordingly.
ampm	Uses the 12-hour clock for the preceding h or hh specifier, and displays the contents of the TimeAMString global variable for any hour before noon, and the contents of the TimePMString global variable for any hour after noon.
/	Displays the date separator character given by the DateSeparator global variable.
:	Displays the time separator character given by the TimeSeparator global variable.

 
 

 

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:

• SUM(LEFT)
• SUM(LEFT,ABOVE,RIGHT,BELOW)
• SUM(C:C)
• SUM(C1:C100)