Formulas

Modified on Thu, 17 Oct at 10:19 AM

Introduction

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




TABLE OF CONTENTS



How to get there

Login to Gigasheet > Select a Sheet > Insert Tab > select Formula.  


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 of the formula land in the sheet's cell as pasted values. 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".


 


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 Function

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!

Was this article helpful?

That’s Great!

Thank you for your feedback

Sorry! We couldn't be helpful

Thank you for your feedback

Let us know how can we improve this article!

Select at least one of the reasons
CAPTCHA verification is required.

Feedback sent

We appreciate your effort and will try to fix the article