Using Spreadsheet Functions

One handy feature in Grow is the ability to use spreadsheet functions to organize your data. While the functions in Grow have some similarities to Excel functions, there are some differences. If Excel or Google Docs is a full powered spreadsheet, Grow is more a "spreadsheet-lite" version. This article has two sections, one to explain the functions Grow has and how to use them, and the second to walk you through referencing other reports within those functions.

You can add a spreadsheet by clicking the "Add Report" button on the right side of the metric builder and searching for "Spreadsheet Functions" in the list of data sources. You can only add one spreadsheet per metric.

The Spreadsheet Functions report runs any expressions in order of their appearance in the data. So if you have cells early in the data (towards the top or the left) that reference cells later in the data it may end up timing out and returning a value of 0 if there are too many functions running at once.

For example, if you have some calculations in Column G that are used by another calculation in Column B, Grow might try to run the calculation in Column B first and return a zero because it hasn't run the calculations in Column G yet.

Most of the time this won't be an issue unless you have a lot of calculations running over several columns.

Spreadsheet Functions

Here is a list of all of the spreadsheet functions in Grow:

• Sum
• Multiply
• Average
• Count
• Min
• Max
• Stdev
• Countif
• Countifs
• Sumif
• Sumifs
• Arithmetic operators: + - / * ()
• If
• Importseries
• Transpose
• Unique
• Last
• First
• Left
• Right
• Round
• Mid

The following section will explain how to use each function or operator in Grow and how they compare to Excel. Given that this list is extensive, if you are looking for a specific function or operator, we recommend using Ctrl+F to search for it.

Sum

• How to use it: Returns the sum of a series of numbers and/or cells.
• Syntax: =SUM(range_to_sum)
• Grow Example: =SUM(report1!A2:A100, 101)
• Excel Comparison: =SUM function adds a range of numbers/cells.

Multiply

• What it does: Returns the product of a series of numbers and/or cells.
• Syntax: =MULTIPLY(range_to_multiply)
• Grow Example: =MULTIPLY(report1!B2,2) or=MULTIPLY(report1!B2:B4)
• Excel Comparison: =PRODUCT function multiplies a range of numbers/cells.

Average

• What it does: Returns the average of a series of numbers and/or cells.
• Syntax: =MULTIPLY(range_to_average)
• Grow Example: =AVERAGE(report1!A)
• Excel Comparison: =AVERAGE function returns the average of a range of numbers

Count

• What it does: Returns the amount size (count) of a series
• Syntax: =COUNT(range_to_count)
• Grow Example: =COUNT(report1!A)
• Excel Comparison: =COUNT function returns how many numbers are in a range of numbers/cells

Min

• What it does: Returns the minimum value of a series of numbers and or/cells
• Syntax: =MIN(range)
• Grow Example: =MIN(report1!A)
• Excel Comparison: =MIN function returns the minimum value in a range of numbers/cells

Max

• What it does: Returns the maximum value of a series of numbers and or/cells
• Syntax: =MIN(range)
• Grow Example: =MAX(report1!A)
• Excel Comparison: =MAX function returns the maximum value in a range of numbers/cells

Stdev

• What it does: Returns the standard deviation of a series of numbers and or/cells
• Syntax: =STDEV(range)
• Grow Example: =STDEV(report1!A)
• Excel Comparison: =STDEV.P function calculates the standard deviation based on the entire population (of data)

Countif

• What it does: Returns the count of a range based on a condition you specify.
• Syntax: =COUNTIF(range_to_count, "criteria")
• Grow Example: =COUNTIF(report1!A, ">10") --> This will count the number of times the value is greater than 10
• Excel Comparison: =COUNTIF function counts the number of cells within a range that meet the given criteria

Countifs

• What it does: Returns the count of a range with multiple criteria
• Syntax: =COUNTIFS(range_with_criteria, "criteria",range_with_criteria2, "criteria2")
• Grow Example: =COUNTIFS(report2!A, "Esther", report2!C, "5") --> This will count the number of times the value in Column A is "Esther" AND the value in Column C is "5"
• Excel Comparison: =COUNTIFS function counts the number of cells within a range that meet multiple criteria

Sumif

• What it does: Returns the sum of a range based on a condition that you specify.
• Syntax:
• =SUMIF(range_to_sum, "criteria")
• =SUMIF(range_with_criteria, "criteria", range_to_sum)
• Grow Example:
• =SUMIF(report1!A, ">10") --> This will sum up all of the values in Column A that are greater than 10
• =SUMIF(report1!A, ">10", report1!B) --> This will sum up all of the values in Column B where the values in Column A are greater than 10
• Excel Comparison: =SUMIF function adds the cells specified by a given criteria

Sumifs

• Returns the sum of a range depending on multiple criteria.
• Syntax: =SUMIFS(range_to_sum,range_with_criteria, "criteria",range_with_criteria2, "criteria2")
• Grow Example: =SUMIFS(report2!B, report2!A, "Esther", report2!C, "5") --> This will sum up all of the values in Column B where the values in Column A are "Esther" and the values in Column C are 5
• Excel Comparison: =SUMIFS functions adds the cells in a range that meet multiple criteria.

+, -, /, *, ( )

• Each operator can be used with any of the functions listed above. They will return a single numerical value based on what the operator does.
• Grow Example: =MAX(report1!A) + SUM(report1!B) / 5 - 1.5
• Excel Comparison: + (plus sign) operator performs addition. - (minus sign) performs subtraction or negation. * (asterisk) performs multiplication). / (forward slash) performs division.

If

• Returns one value if a logical expression is TRUE and another if it is FALSE
• Syntax: =IF(range, "criteria", "return_if_true", "return_if_false")
• Grow Example: =IF(report1!A1, "<5", "Great", "Bad") --> In this example, if a cell is less than 5 (true), then it enters the word Great. If not (false), then it enters the word Bad.
• Excel Comparison: =IF function does something if a cell is true and something else if it is false.

Importseries

• What it does: Copies the results of a reference or expression into a row or column. IMPORTSERIES can be used in conjunction with UNIQUE, DATEFORMAT, LEFT, RIGHT, MID, SUBSTRING, TIMEMANIPULATE
• Grow Example: =IMPORTSERIES(UNIQUE(DATEFORMAT(report1!E, "MMMM")))
• Excel Comparison: N/A

One note with IMPORTSERIES: Be sure that you are not importing multiples series into the same column. The first series will pull in the column for that series and overwrite anything that is below it.

Transpose

• Copies the results of a reference or expression into a row or column. If the source was a column, the destination will be a row, and vice versa. TRANSPOSE can be used in conjunction with UNIQUE, DATEFORMAT, LEFT, RIGHT, MID, SUBSTRING, TIMEMANIPULATE
• Grow Example: =TRANSPOSE(UNIQUE(report1!E))
• Excel Comparison: =TRANSPOSE function transposes the selected cells into a row or column.

Unique

• Returns a unique value in the selected range, discarding duplicates. Values are returned in the order in which they are encountered in the range. UNIQUE is most often used inside of IMPORTSERIES or TRANSPOSED, and then applied to DATEFORMAT, LEFT, RIGHT, MID, SUBSTRING, TIMEMANIPULATE
• Grow Example: =IMPORTSERIES(UNIQUE(report1!E))
• Excel Comparison: N/A

Last

• What it does: Returns the last value in the selected range.
• Grow Example: =LAST(REPORT1!E)
  or =LAST(REPORT1!E,-1) to get the second-to-last value in the range
• Excel Comparison: N/A

First

• What it does: Returns the first value in the selected range.
• Grow Example: =FIRST(report1!E)
• Excel Comparison: N/A

Left

• What it does: Returns a substring from the beginning of a specified string.
• Syntax: LEFT(string, [numberOfCharacters])
• Grow Example: =LEFT(report2!A2, 3)
• Excel Comparison: =LEFT function returns the first character(s) in a text string, based on number of characters you specify.

Right

• What it does: Returns a substring from the end of a specified string.
• Syntax: RIGHT(string, [numberOfCharacters])
• Grow Example: =RIGHT(report2!A2)
• Excel Comparison: Returns the rightmost characters in a text string, based on the number of characters you specify.

Round

• What it does: Rounds the number to the specified number of places. You can use zero to round to the nearest whole number.
• Syntax: ROUND(number, [places])
• Grow Example: =ROUND(report2!A2, 1)
• Excel Comparison: =ROUND function rounds a number to a specified number of digits.

Mid

• What it does: Returns a segment of a string.
• Syntax: MID(string, startPosition, length)
• Grow Example: =MID(report2!A2, 3, 2)
• Excel Comparison: =MID function returns a specific number of characters from a text string starting where you specify.

Referencing Other Reports

The syntax is =FUNCTION(report!location), where the function is the expression, the report is where you want to pull it from, and location is the column or cell that you want to reference.

• The report numbers are listed above your data, but below the metric visual. (You can see this in the example below.) When referencing a report, you will write report#! It is always followed by an exclamation mark. For example, you will reference report1! or report2! or report3!, etc. based on the reports you have.
• Previously, to reference the Master SQL report, you would enter masterreport. Now, to reference the Master SQL report or Joined reports, use the corresponding report1! or report2!, etc.
• If you are referencing a cell or column from spreadsheet functions, use spreadsheet!.
• In the location field, you can referencea specific cell or a column in a report. This location always comes immediately after the exclamation mark denoting which report is being referenced.

Please note that you cannot reference spreadsheet data anywhere outside of spreadsheet functions. This includes joined reports or master reports. Spreadsheet data can only be referenced as spreadsheet!A1 inside spreadsheet functions.

Examples: Referencing other reports in Spreadsheet Functions

Here are a few simple examples of referencing a report:
=report1!A2
=report1!B3 + report3!B5
=spreadsheet!A1+spreadsheet!B1
=SUM(spreadsheet!A1, B1)

This example would return the same figure as the example directly above it.

Additionally, we will use the following Google Analytics example.

The metric is the Visitor Overview prebuilt metric. It has two Google Analytics reports and a Master SQL report.

Let us say we want to pull column B from Report 1 into our spreadsheet. First, go to the Spreadsheet Functions tab. You can give the column a title, if you want.

In the cell below your title, enter the following function: =IMPORTSERIES(report1!B)

Hit enter, and watch your data pull in to your spreadsheet.

Next, we are going to reference something from Report 2. Again, you can label the cell if you want. We are going to pull cell D2 from report 2.

Enter the following function into your cell: =report2!D2

This will return the value that is in Report 2, cell D2.

For the Master SQL report, we will pull in column D, which, in our metric, is Total Pageviews.

In order to reference this, use the following function: =IMPORTSERIES(report3!D)

Press enter, and the column will fill in to your spreadsheet.