Find in site
Office OneNote Gem Add-Ins
  • Home
  • Addins
    • AddIns Internal (Windows10,8,7) >
      • Bridge for EndNote and OneNote
      • Gem for OneNote >
        • OneNote Gem - Excel Editor
        • Translate Gem for OneNote
      • Mind Map for OneNote
      • OneNote Gem - Favorites >
        • OneNote Gem - Keyword Tags
        • OneNote Mind Map
    • Web and Link AddIns (Windows) >
      • Anchor to OneNote >
        • Anchor to OneNote for Acrobat >
          • Anchor to OneNote for Adobe Acrobat Reader
          • Anchor to OneNote for Adobe Acrobat Reader DC
        • Anchor to OneNote for Chrome
        • Anchor to OneNote for Firefox
        • Anchor to OneNote for Access
        • Anchor to OneNote for Excel
        • Anchor to OneNote for Opera
        • Anchor to OneNote for PowerPoint
        • Anchor to OneNote for Outlook
        • Anchor to OneNote for Project
        • Anchor to OneNote for Publisher
        • Anchor to OneNote for Visio
        • Anchor to OneNote for Word
      • Anchor to OneNote for PDF
      • Bring to OneNote >
        • Bring to OneNote for Acrobat
        • Bring to OneNote for Firefox
        • Bring to OneNote for Thunderbird
        • Bring to OneNote for Chrome >
          • Install Bring to OneNote for Chrome
        • Bring to OneNote for Opera
        • Bring to OneNote for Internet Explorer
        • Bring to OneNote for Safari
        • Bring to OneNote for Windows Explorer
        • Bring to OneNote for Word
        • Bring to OneNote for Excel
        • Bring to OneNote for PowerPoint
        • Bring to OneNote for Visio
        • Register Bring to OneNote
      • Bring to Mac OneNote
      • Favorite to OneNote >
        • Favorite to OneNote for Chrome >
          • Install Favorite to OneNote for Chrome
        • Favorite to OneNote for Firefox
        • Favorite to OneNote for Internet Explorer
        • Favorite to OneNote for Opera
        • Favorite to OneNote for Windows Explorer
        • Image to OneNote
      • Row to OneNote >
        • Row to OneNote for Access
        • Row to OneNote for Excel
        • Row to OneNote for Project
    • Mind Map AddIns (Windows10,8,7) >
      • Mind Map for OneNote
      • OneMind for Office OneNote
      • OneNote Gem - Favorites
      • Pons for MindManager and OneNote
      • Pons for Visio and OneNote
    • AddOns External (Windows10,8,7) >
      • Auto OneNote
      • OneNote Batch >
        • OneNote Batch Command Lines
        • Google Keep Importer
        • Register OneNote Batch
      • OneNote Reminder
      • OneNote Search Bar
      • Fix One
    • Markdown AddIns (Windows) >
      • One Markdown
      • Gem for OneNote
    • Mac OneNote AddIns (MACOS) >
      • Gem Menu for Mac OneNote
      • OneMind for Mac OneNote
      • Bring to Mac OneNote >
        • Bring to OneNote for Mac Safari
        • Bring to OneNote for Mac Chrome
        • Bring to OneNote for Mac Firefox
    • UWP OneNote AddIns (Windows 10) >
      • Gem Menu for OneNote UWP
      • OneMind for OneNote UWP
      • OneReader
    • Cross-Platform AddIns >
      • OneMind for OneNote >
        • OneMind for Mac OneNote
        • OneMind for Windows Office OneNote 2016, 2013, 2010
        • OneMind for Windows OneNote
      • CiteBoard for OneNote
  • Download
  • Buy
  • Documents
    • Document
    • FAQs
    • Markdown
    • Formula for OneNote Table
    • Mind Map AddIns
    • Evernote Documents
    • OneNote Download
    • Release
  • Support
  • Templates
    • OneNote Templates
    • Gem Menu Templates
  • ⭐ WordNote

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

​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)
Command Line 
OneNote Tag Tree
Mind Map Tool

MindMap Add-Ins Comparison
One Markdown Syntax
OneNote Table Formula
OneNote Gantt

Repair OneNote
Add-Ins Documents
OneNote FAQs
OneNote Download
Release


View and Commert PDF in OneNote

MOBILE SITE
Copyright © 2011-2020 Digital GemSoft Ltd. OneNoteGem.com All Rights Reserved.