After running a new report or opening a saved report, there are many actions you can take to work with the data. One of these options is adding a formula to the report. A formula allows you to perform a function on the data in a report. Some of these functions include:

- Perform mathematical functions on the data in your report.
- Use logical functions to replace data.
- Remove unwanted spaces or shorten text.
- Format a number as currency or a percentage.
- Format dates and time.
- Remove decimal points and round numbers.
- Calculate elapsed time.
- Count the number of characters in a response.

## Add a Formula to a Report

When you add a formula to a report, a new column is automatically added as well, containing the data resulting from the formula. Follow the steps below to add a formula to a report.

- On your report, click
**Formula**. - Enter a Name for the new column, which will contain the data resulting from the formula.
- Select a
**formula**from the Formula drop-down. This will automatically insert it into the Formula box.- You may also type a formula into the Formula box.
- You can also click
**Formula Help**for a list and descriptions of common formulas.

- Once your formula is in the Formula box, place your cursor or highlight the placeholder text where you need to refer to an existing column of data in your report.
- Select the
**column**from the Insert a Column drop-down, or manually type the label of the column inside brackets (e.g. [Project Name]). If you select the column from the drop-down, it will automatically insert where you placed your cursor or highlighted text. - Click the
**Data Type**drop-down and select the type of data that will result from the formula. - If there is a specific format in which you want to see the resulting data, select it in the Display Format drop-down. Otherwise, leave the drop-down blank.
- Click
**Add**. - You'll now see the new column with the resulting data added to the report.
- Click
**Save Changes**to save all changes you make to the report.

### Common Reporting Formulas

The dropdown list when adding a formula to a report contains many useful formulas. Below are some additional commonly used formulas for reference:

- Rename values (e.g. to create anonymous labels for evaluators in the report):
- IIF([Column] = “Bob”, “Evaluator 1”,IIF([Column] = “Joe”, “Evaluator 2”,IIF([Column] = “Mary”, “Evaluator 3”,IIF([Column] = “Jane”, “Evaluator 4”,IIF([Column] = “Sally”, “Evaluator 5”, 0)))))

- Correct blank number cells that should display a 0:
- [Column]+0

- Combine columns:

- [Column]+[Column]

- Formula for "contains":
- IIF(InStr(1,[Column], "What you're checking if it contains", 1)<>0, "Output if it does contain", "Output if it doesn't contain")

- Put installment (or decision date, payment date, etc.) into correct fiscal year:
- IIF(Month([Installment Due Date])<7,Year([Installment Due Date]),Year([Installment Due Date])-(-1))
- The "7" in the formula above indicates the month. In this example, 7 for July.

- IIF(Month([Installment Due Date])<7,Year([Installment Due Date]),Year([Installment Due Date])-(-1))

- Rename values (e.g. to create anonymous labels for evaluators in the report):