Formulas

Modified on Wed, 28 Feb 2024 at 12:38 PM

Introduction

Users can create new columns in Gigasheet using spreadsheet-like formulas.

Note: Gigasheet formulas are different from formulas in a typical spreadsheet, in that they operate on an entire column of data. In a typical spreadsheet, formulas can be entered in any cell, and reference any other cell in the sheet. In Gigasheet formulas apply the same operation to every row in the sheet.

TABLE OF CONTENTS

Math/Calculation Functions
String and Text Function
Logic Functions
Other Functions

How to get there

Alternatively, you can click the + icon on the top right of your sheet.

Math/Calculation Functions

Operator	Description	Example
+	Add together two numeric values	number_column + 12
-	Subtract two numeric values	number_column - 12
*	Multiply two numeric values	number_column * col 15
/	Divide two numeric values	number_column / 100

String and Text Function

SUBSTITUTE	DESCRIPTION	Replace all occurrences in the array of the find string with the replace string.
	FORMULA	SUBSTRING(text, search_for, replace_with)
	EXAMPLE FORMULA	SUBSTITUTE(company_col, "inc", "Inc.")
	EXAMPLE INPUT	Gigasheet inc
	EXAMPLE OUTPUT	Gigasheet Inc.

CONCAT	DESCRIPTION	Merge values from various ranges and/or strings.
	FORMULA	CONCAT(value1, value2, ...)
	EXAMPLE FORMULA	CONCAT(user_col, "@gigasheet.com")
	EXAMPLE INPUT	support
	EXAMPLE OUTPUT	support@gigasheet.com

LEFT	DESCRIPTION	Retrieve values located on the left side of a text value.
	FORMULA	LEFT(text, num_characters)
	EXAMPLE FORMULA	LEFT(text_col, 4)
	EXAMPLE INPUT	Gigasheet Rocks
	EXAMPLE OUTPUT	Giga

RIGHT	DESCRIPTION	Retrieve values located on the right side of a text value
	FORMULA	RIGHT(text, num_characters)
	EXAMPLE FORMULA	RIGHT(text_col, 4)
	EXAMPLE INPUT	Gigasheet Rocks
	EXAMPLE OUTPUT	ocks

RAND	DESCRIPTION	Generates random numbers
	FORMULA	RAND()
	EXAMPLE FORMULA	N/A
	EXAMPLE INPUT	N/A
	EXAMPLE OUTPUT	0.84727895

RANDBETWEEN	DESCRIPTION	Generate a random string of numbers between two numerical values
	FORMULA	RANDBETWEEN(min, max)
	EXAMPLE FORMULA	RANDBETWEEN(5.5, 10)
	EXAMPLE INPUT	5.5, 10
	EXAMPLE OUTPUT	6.9860978479

LENGTH	DESCRIPTION	Returns the number of characters in a text string.
	FORMULA	LEN()
	EXAMPLE FORMULA	LEN(text_col)
	EXAMPLE INPUT	Gigasheet
	EXAMPLE OUTPUT

ROUND	DESCRIPTION	Rounds to the specified number of digits
	FORMULA	ROUND(numeric_col, [places])
	EXAMPLE FORMULA	ROUND(2.149, 1)
	EXAMPLE INPUT	2.149
	EXAMPLE OUTPUT	2.1

TEXT	DESCRIPTION	Returns a plain text version of the input column
	FORMULA	TEXT(num_col)
	EXAMPLE FORMULA	TEXT(uid)
	EXAMPLE INPUT	100
	EXAMPLE OUTPUT	“100”

EXACT	DESCRIPTION	Returns true if the given two strings are exactly the same
	FORMULA	EXACT(string1, string2)
	EXAMPLE FORMULA	EXACT(Gigasheet, gigasheet)
	EXAMPLE INPUT	gigasheet
	EXAMPLE OUTPUT	false

SUMBY	DESCRIPTION	Returns the sum of the second argument binned by the value of the first.
	FORMULA	SUMBY(category_col, num_col)
	EXAMPLE FORMULA	SUMBY(product_category, units)
	EXAMPLE INPUT
	EXAMPLE OUTPUT

AVERAGEBY	DESCRIPTION	Returns the average of the second argument binned by the value of the first.
	FORMULA	AVERAGEBY(category_col, num_col)
	EXAMPLE FORMULA	AVERAGEBY(day_of_week, sales)
	EXAMPLE INPUT
	EXAMPLE OUTPUT

COUNTBY	DESCRIPTION	Returns the row count of the value in the given column.
	FORMULA	COUNTBY(category_col)
	EXAMPLE FORMULA	COUNTBY(region)
	EXAMPLE INPUT
	EXAMPLE OUTPUT

MAXBY	DESCRIPTION	Returns the max value of the second argument by the value of the first.
	FORMULA	MAXBY(category_col, num_col)
	EXAMPLE FORMULA	MAXBY(product_category, units)
	EXAMPLE INPUT
	EXAMPLE OUTPUT

MINBY	DESCRIPTION	Returns the min value of the second argument by the value of the first.
	FORMULA	MINBY(category_col, num_col)
	EXAMPLE FORMULA	MINBY(product_category, units)
	EXAMPLE INPUT
	EXAMPLE OUTPUT

PROPER	DESCRIPTION	Capitalizes the first letter of every word in the given column.
	FORMULA	PROPER(text_col)
	EXAMPLE FORMULA	PROPER(name)
	EXAMPLE INPUT	john doe
	EXAMPLE OUTPUT	John Doe

LOWER	DESCRIPTION	Lower-cases every letter in the given column.
	FORMULA	LOWER(text_col)
	EXAMPLE FORMULA	LOWER(url)
	EXAMPLE INPUT	Www.Gigasheet.Com
	EXAMPLE OUTPUT	www.gigasheet.com

UPPER	DESCRIPTION	Upper-cases every letter in the given column.
	FORMULA	UPPER(text_col)
	EXAMPLE FORMULA	UPPER(address)
	EXAMPLE INPUT	john doe
	EXAMPLE OUTPUT	John Doe

YEAR	DESCRIPTION	Returns the year from a datetime as an integer
	FORMULA	YEAR(datetime_col)
	EXAMPLE FORMULA	YEAR(datetime_col)
	EXAMPLE INPUT	2021-04-22 1:02:03
	EXAMPLE OUTPUT	2021

MONTH	DESCRIPTION	Returns the month from a datetime as an integer
	FORMULA	MONTH(datetime_col)
	EXAMPLE FORMULA	MONTH(datetime_col)
	EXAMPLE INPUT	2021-04-22 1:02:03
	EXAMPLE OUTPUT	4

DAY	DESCRIPTION	Returns the day from a datetime as an integer
	FORMULA	DAY(datetime_col)
	EXAMPLE FORMULA	DAY(datetime_col)
	EXAMPLE INPUT	2021-04-22 1:02:03
	EXAMPLE OUTPUT

DAYOFWEEK	DESCRIPTION	Returns the day of week from a datetime as an integer, starting with Monday as 1
	FORMULA	DAYOFWEEK(datetime_col)
	EXAMPLE FORMULA	DAYOFWEEK(datetime_col)
	EXAMPLE INPUT	2021-04-22 1:02:03
	EXAMPLE OUTPUT	4

QUARTER	DESCRIPTION	Returns the quarter from a datetime as an integer, starting with 1 for the first quarter
	FORMULA	QUARTER(datetime_col)
	EXAMPLE FORMULA	QUARTER(datetime_col)
	EXAMPLE INPUT	2021-04-22 1:02:03
	EXAMPLE OUTPUT	3

Logic Functions

IF	DESCRIPTION	Retrieve values located on the right side of a text value.
	FORMULA	IF(boolean, value_if_true, value_if_false)
	EXAMPLE FORMULA	IF(temp_col > 80, "High", "Low")
	EXAMPLE INPUT	100
	EXAMPLE OUTPUT	High

AND	DESCRIPTION	Returns true when all given inputs are true and false otherwise
	FORMULA	AND(boolean1, boolean2, [boolean3, ...])
	EXAMPLE FORMULA	AND(temp_col > 80, temp_col < 90)
	EXAMPLE INPUT	100
	EXAMPLE OUTPUT	False

OR	DESCRIPTION	Returns true when any of the given inputs is true and false otherwise
	FORMULA	OR(boolean1, boolean2, [boolean3, ...])
	EXAMPLE FORMULA	OR(temp_col > 80, temp_col < 90)
	EXAMPLE INPUT	100
	EXAMPLE OUTPUT	True

NOT	DESCRIPTION	Returns true when the input is false, and false otherwise.
	FORMULA	NOT(boolean)
	EXAMPLE FORMULA	NOT(temp_col > 80)
	EXAMPLE INPUT	100
	EXAMPLE OUTPUT	False

ISBLANK	DESCRIPTION	Returns true if the input is null or blank, and false otherwise.
	FORMULA	ISBLANK(boolean)
	EXAMPLE FORMULA	ISBLANK(text_col)
	EXAMPLE INPUT	something
	EXAMPLE OUTPUT	False

Formulas Limitations and FAQ

LEFT, RIGHT, MID will not take expressions (i.e LEFT(EMAIL, FIND("@",EMAIL))
Columns as a Text Data Type can be accepted with numerical formulas but Numerical Data Types cannot be accepted where text is required for the formula.
Sheets that have identical column headers, formula will use the first matching column found.
Date formulas require the input column be a "date" data type.
Formulas can have a column as the second argument, but can't have an expression as the second argument. For example, LEFT(Column 1, (LENGTH(Column 1)-1)) is invalid. The workaround would be to input the formula LENGTH(Column 1) - 1, and then do LEFT(Column 1, new column)
Gigasheet cannot run formulas on specific "Column,Row" inputs. For example, A2, B44, D90 would be invalid.

Other Functions

Looking for something? Let us know what formula you'd like!