Formulas

Modified on Wed, 9 Apr at 10:42 AM

Introduction

In Gigasheet, you can create new columns using powerful, spreadsheet-style formulas. No need to drag or copy them down. Formulas automatically apply to the entire column.

Gigasheet also offers the ability to Ask AI which will write formulas for you.

TABLE OF CONTENTS

Introduction
How to get there
- Notable Differences with Excel/Google Sheets
Ask AI
Available Functions
Formulas Limitations and FAQ
Other Functions

How to get there

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

Notable Differences with Excel/Google Sheets

Gigasheet formulas are different from formulas in a typical spreadsheet, in that they operate on the entire column of data. Gigasheet formulas apply the same operation to every row in the sheet.

Results do not automatically update as the cells generating the formula output are modified. To refresh a formula's output, choose "Edit Formula" from the column menu, and select "Refresh Calculation".

Ask AI

Gigasheet's Ask AI feature within the formula builder translates natural language requests to Gigasheet formulas. Simply turn on Ask AI in the formula builder and prompt AI with the output you'd like generated in a new column. AI will generate a formula for review, and display sample output. When ready, hit Add Column to run the formula and add a new column.

Tip: For best results refer to column names in your prompt.

Available Functions

Math/Calculations 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/Text Functions

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

COUNTIF	DESCRIPTION	Returns a count of the number of cells if conditions are met.
	FORMULA	COUNTIF(col, if_value, num_col)
	EXAMPLE FORMULA	COUNTIF(day_of_week_col "Monday"; sales_col")
	EXAMPLE INPUT	Monday
	EXAMPLE OUTPUT	4

SUMIF	DESCRIPTION	Returns a sum if numeric conditions are met.
	FORMULA	SUMIF(col, if_value, num_col)
	EXAMPLE FORMULA	SUMIF(day_of_week_col "Monday"; sales_col)
	EXAMPLE INPUT	Monday
	EXAMPLE OUTPUT	8

AVERAGEIF	DESCRIPTION	Returns an average if numeric conditions are met.
	FORMULA	AVERAGEIF(col, if_value, num_col)
	EXAMPLE FORMULA	AVERAGEIF(day_of_week_col "Monday"; sales_col)
	EXAMPLE INPUT	Monday
	EXAMPLE OUTPUT	4

MEAN	DESCRIPTION	Returns the average of a numeric column.
	FORMULA	MEAN(num_col)
	EXAMPLE FORMULA	MEAN(sales_col)
	EXAMPLE INPUT	6,4,2,5
	EXAMPLE OUTPUT	4.25

STDEV	DESCRIPTION	Returns the standard deviation of a numeric column.
	FORMULA	STDEV(num_col)
	EXAMPLE FORMULA	STDEV(sales_col)
	EXAMPLE INPUT	6,4,2,5
	EXAMPLE OUTPUT	1.4790199457749

CORREL	DESCRIPTION	Returns the correlation of two numeric columns.
	FORMULA	CORREL(col1, col2)
	EXAMPLE FORMULA	CORREL(sales_col, price_col)
	EXAMPLE INPUT
	EXAMPLE OUTPUT

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

REGEXREPLACE	DESCRIPTION	Replaces the first matching substring of the given regular expression with the given string.
	FORMULA	REGEXREPLACE(text_col, regex, replacement)
	EXAMPLE FORMULA	REGEXREPLACE(company, "llc", "")
	EXAMPLE INPUT
	EXAMPLE OUTPUT

REGEXMATCH	DESCRIPTION	Returns true if the regular expression is matched.
	FORMULA	REGEXMATCH(text_col, regex)
	EXAMPLE FORMULA	REGEXMATCH(company, "llc")
	EXAMPLE INPUT
	EXAMPLE OUTPUT

REGEXEXTRACT	DESCRIPTION	Returns the first matching substring according to the given regular expression.
	FORMULA	REGEXEXTRACT(text_col, regex)
	EXAMPLE FORMULA	REGEXEXTRACT(price, "[0-9]*\.[0-9]+[0-9]+")
	EXAMPLE INPUT
	EXAMPLE OUTPUT

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!