Skip to main content

Formulas and Functions

As previously mentioned, there are over 400 formulas and functions that can be incorporated into a report template. To access, click the fx button in the formula bar.

Parameters

A formula can have one or more parameters that define the value returned to the cell. A parameter can be a hard coded value or a cell reference (or range). When a cell reference or range is specified, it can be done as an absolute reference, a relative reference, or a mix of both.

  • Absolute Reference

    An absolute reference means that the cell or range is locked so that if the cell is copied the reference will not change. An absolute reference is denoted with “$”, e.g., $A$1 or $A$1:$C$5.

  • Relative Reference

    A relative reference means that the cell or range changes if the cell is copied. A relative reference is denoted without “$”, e.g. A1 or A1:C5.

  • Mixed Reference

    A mixed reference means that either the column or row is locked but the other is not so if the cell is copied only the unlocked reference is changed. For example, $A1 means that the column (A) is absolute while the row (1) is relative. Conversely, A$1 means that the column (A) is relative while the row (1) is absolute.

A Practical Example

Consider a report template that brings in product codes from 4 different lines and contains a lookup table to determine the name of the product based on the code.

To show the product name along with the product code, a LOOKUP formula can be used. Let us assume that the product codes are to be written to cells C4, D4, E4 and F4 and the corresponding names in cells C5, D5, E5 and F5. The lookup table is in cells AA2:AB51.

In cell C5, the formula should be:

=LOOKUP(C4, $AA$2:$AB$51)

The LOOKUP function has two parameters: the first is the value to look up and the second is the range of the table to use for looking up. Notice that the first parameter is set as a relative reference and the second parameter as an absolute reference. This is so the formula can be copied from C5 and pasted into D5, E5 and F5 without making any edits. The first parameter will change but the second will not.

Useful Functions

While it would be impossible to document all the functions available in the studio, the following are ones that can help in reporting.

  • IF

    The IF function is used to compare values and show a different value based on the comparison being true or false.

    A common use of this would be that if a measurement is less than a threshold (like 0.1) show is as “N/A”, otherwise show the value. If the value is in cell A1, the formula is:

    =IF(A1<0.1, “N/A”, A1)

    The first parameter is the comparison, the second is the value if the comparison is true and the third is if the comparison is false.

  • IFERROR

    The IFERROR function is used to check if there is an error and if so, do not show the error but instead show what is specified.

    A good example of this is using this function in conjunction with the AVERAGE formula so that if all the cells are empty, #DIV/0! is not shown. If the average spans the range A1:A23, the formula is:

    =IFERROR(AVERAGE(A1:A23), “”)

    The first parameter is what to test for an error. If this is not an error, the value from the parameter is used. However, if the value from parameter 1 is an error, the value in parameter 2 is shown. In this case, the cell will appear empty.

  • COUNTIF

    The COUNTIF function can be used to count values in a range that meet the criteria specified. The criteria can be hard coded like “>25” or “<1” or a cell reference. If a cell reference is specified on its own, this means that the criteria operator is equals. For any other operator it must be concatenated with the cell reference. For example, if the criteria is greater than the value of cell A1, it must be specified as “>” & A1.

    For example, if cells C4:C50 contain temperature readings and the report needs a count of how many times the temperature exceeded 100 degrees the formula is:

    =COUNTIF(C4:C53, “>100”)

  • SUMIF and AVERAGEIF

    The SUMIF and AVERAGEIF functions are very similar to the COUNTIF function in that a range and a criteria is specified to derive a value. However, both functions have an optional third parameter which is the range to perform the calculation on.

    For example, let us say the B column contains the state 0 or 1 and the C column contains temperature in rows 4 to 53. To get the average temperature only when the state is 1, the formula is:

    =AVERAGEIF(B4:B53, “>0”, C4:C53)

  • MATCH

    The MATCH function is used to find the matching row or column where a specified value is located.

    For example, if the report contains hourly values from cells C4:C27 and in cell C29 the maximum of that range is determined (using the MAX function), the MATCH formula can be used to determine which row in the range contains the maximum. The formula is:

    =MATCH(C29, C4:C27, 0)

    This in and of itself may not be very useful, however, if the B column contains the timestamps corresponding to those values, the OFFSET function can be used with the MATCH function to get the time of maximum for the day. The formula for that is:

    =OFFSET(B4, MATCH(C29, C4:C27, 0)-1, 0)

    Note the -1 at the end of the MATCH formula. This is because the OFFSET formula is 0 based and the MATCH formula is 1 based.

  • MID

    The MID function is used to extract characters from text. A good example of this would be a report where a code is retrieved that has multiple elements that need to be broken out within the report. For example, if the code is brought into cell B4 and the 4th and 5th characters need to be extracted, the formula is:

    =MID(B4, 4, 2)

In this section: