Search
OneNoteGem
  • Home
  • Addins
    • Windows Office Addins >
      • Gem for OneNote
      • OneNote Search Bar
      • OneNote Gem - Favorites
      • OneNote Batch
      • Pons for MindManager and OneNote
      • Mind Map for OneNote
      • Pons for Visio and OneNote
      • Row to OneNote
      • Auto OneNote
      • Favorite to OneNote
      • Bridge for EndNote and OneNote
      • Anchor to OneNote
      • Anchor to OneNote for PDF
      • Bring to OneNote
      • Gem Table for OneNote
    • Windows UWP Addins >
      • Gem Menu for OneNote UWP
      • CiteBoard for OneNote
      • OneReader
      • OneMind for Windows OneNote
      • OneNote Reminder Cloud (UWP)
      • OneNote Batch Cloud (UWP)
    • Mac Addins >
      • Gem Menu for Mac OneNote
      • CiteBoard for OneNote
      • Bring to Mac OneNote
      • OneMind for Mac OneNote
      • OneNote Reminder for Mac
      • OneNote Batch for Mac
      • Fix One for Mac - Fix OneNote Login, Sync Tools
    • Android Addins >
      • CiteBoard for OneNote
      • Fix One for Android - Fix OneNote Login, Sync Tools
    • Cross-Platform Addins >
      • OneNote Reminder
      • OneMind for OneNote
      • CiteBoard for OneNote
      • Fix One - Fix OneNote Login, Sync Tools
    • Markdown Addins >
      • Gem for OneNote
      • One Markdown
    • Mind Map Addins >
      • OneNote Gem - Favorites
      • Pons for MindManager and OneNote
      • Mind Map for OneNote
      • Pons for Visio and OneNote
      • OneMind for OneNote
    • Other Addins >
      • One Markdown
      • Clip to OneNote
      • WordNote
      • OneShare
      • Fix One for Windows - Fix OneNote Login, Sync Tools
  • Buy
  • Documents
  • FAQs
  • Templates
  • Support
  • Release
  • WordNote
Gem Table for OneNote
 
 
The table of merged cells for OneNote

Formula for OneNote Table Document

2020-03-17 07:59
 
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
 
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
  • ……
Insert a Formula in a Table Cell
 
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
A1 References and a Range of Cells
 
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)

Now

 
Formula Description Result
NOW() Returns the current date and time. 11/6/2011 19:03
NOW()-0.5 Returns the date and time 12 hours ago (-0.5 days ago). 11/6/2011 7:03
NOW()+7 Returns the date and time 7 days in the future. 11/13/2011 19:03
NOW()-2.25 Returns the date and time 2 days and 6 hours ago (-2.25 days ago). 11/4/2011 13:03
 

Date

 
Encode a date
Date(Year, Month, Day)
 

Time

 
Encode a Time
Time(Hour, Minute, second)
 
 

Year

 
Data    
Date    
7/5/2008    
7/5/2010    
Formula Description (Result) Result
YEAR(A3) Year of the date in cell A3 (2008) 2008
YEAR(A4) Year of the date in cell A4 (2010) 2010
 

Month

 
Date    
15-Apr-11    
Formula Description Result
MONTH(A2) Month of the date in cell A2 4
 

Day

 
Date    
15-Apr-11    
Formula Description (Result) Result
DAY(A2) Day of the date in cell A2 (15) 15
 
 

Hour

 
Time    
0.75    
7/18/2011 7:45    
4/21/2012    
Formula Description Result
HOUR(A2) Returns 75% of 24 hours 18
HOUR(A3) Returns the hour portion of the date/time value. 7
HOUR(A4) A date with no time portion specified is considered 12:00 AM, or 0 hours. 0
 
 

Minute

 
Time    
12:45:00 PM    
Formula Description Result
MINUTE(A2) Minute portion of the time in A2. 45
 
 

Second

 
Data    
Time    
4:48:18 PM    
4:48 PM    
Formula Description R esult
SECOND(A3) Seconds in the first time (18) 18
SECOND(A4) Seconds in the second time (0) 0
 


 

 
Download Gem for OneNote

Related Documents

  • OneNote Kanban Apply: Adjust the Width of all Subtables to Fit the Column Width of the Parent Table with One Click
  • OneNote Gem - Excel Editor
  • Copy OneNote Selected Cells, Paste and Replace the Selected Cells in another Table
  • Currency Style for OneNote Cells
  • How to apply Comma Style for OneNote table cells
Command Line
Tag System
MindMap Add-Ins Comparison
Search Add-Ins Comparison
One Markdown Syntax
Table Formula
Gantt

Gem Menu Templates
Repair OneNote
OneNote Download
English website Chinese website
Mobile Site
Copyright © 2011-2025 Digital GemSoft. OneNoteGem.com All Rights Reserved.