NOTE: Due to a recent update to the reporting module, you may notice some differences between your system and the screenshots in this tutorial. We are working to update the screenshots, but in the meantime, the steps in the tutorial below are accurate/have not changed
Basic Reporting Part V FAQ: If/Then Formulas
Webinar Recording (written tutorial below the webinar recording details)
- If/then statements are powerful formulas/functions that users can use to manipulate the data that is displayed in a report. For example, if you want to create one column on your report that contains one value if the record meets a certain criteria and a different value if it doesn’t, you would use an if/then statement to accomplish this. For instance, if you want that one column to contain the household salutation if the contact is married and the individual salutation if the contact is single, or if you want to group your donors into a couple of categories (maybe “major donors” and all others or “lapsed donor” and “current donor”).
- If/then statements cannot be used with Express View reports (as functions/formulas cannot be used in Express View).
- To begin, edit the report you wish to add the if/then statement to (our example uses a standard/advanced report. You can also use if/then statements in Crosstab Reports).
- The syntax/format for if/then statements is: if(condition to be met, value if condition is met, value if condition is not met).
- For example, the “Individual or Organization” field displays an “I” if the contact is an individual and an “O” if the contact is an organization. That’s nice, but it’s not necessarily easy or intuitive to read. Perhaps we want to replace the “I” with the word Individual and the “O” with the word Organization. We can use an if/then statement to do this; the three parts of the formula would be:
- Condition: if the “Individual or Organization” field equals “I” (we could just as easily set the condition as Contact Type equals “O” – it doesn’t matter which we do)
- Value if condition is met: display the word “Individual”
- Value if condition is not met: display the word “Organization”
- In the example below, we have inserted a blank column into the report where we want the word Individual or Organization field to go (we have called this field “Contact Type”).
- To insert the if/then statement, click in the cell where you want it to go (in this example it’s cell C4) and then click the formula editor button (fx).
- In the Formula Editor, click the drop down next to Logical and then click and drag If to the formula editing window at the bottom of the screen.
- Next, add the condition to the beginning of the if statement. In this case, we are clicking and dragging the “Individual or Organization” field to the editing window and then adding the condition [=”I”] to indicate that the condition to assess is if the “Individual or Organization” field contains “I” (for an individual contact” or not.
- Next, add the value if the condition is true. Make sure this is separated from the condition by a comma. In this case, if the condition is met (the “Individual or Organization” field value is “I”) we want the report to show the word Individual so we add that field to the place for “if condition is true then do this” spot in the formula. We have surrounded it by quotation marks because when we want the report to display text, we must put the word(s) in quotation marks.
- Finally, add the value if the condition is not true/met. Make sure this is separated from the value if the condition is true by a comma. In this case, if the “Individual/Organization” field does NOT contain an “I”, then we want the report to show the word “Organization”. This is added to the third segment of the formula, in the “do this if the condition is false/not met” spot, and, as above, surrounded by quotation marks.
- Click OK to save the formula.
- Now you will see the formula on the report.
- Run the report and you will now see the results.
- Another way we can use if/then statements it to create a field we can count when there is nothing else to count. For example, there is no “Count If” function in Exago, which would only count particular values (for example, “count up how many people have the last name Smith). However, we can use an if/then formula to approximate a “count if” function by telling a cell to display a numerical value if another cell meets our criteria and to display nothing if the cell doesn’t meet the criteria. We can then add an “AggSum” to the group or report footer to add up these numerical values to get our count.
- In this example report, want to count how many of our donors live in CA. We’ve added a blank column where we display a “1” if they do live in CA and nothing if they don’t.
- Next, we click on the cell where we want to enter our formula and click on the formula editor.
- In the formula enter we enter our if/then formula. In this case, the condition to be met is [State=”CA”], the value if the condition is true is “1” and the value if the condition is true is “” (double quotes with nothing between is Exago syntax for nothing or empty).
- When we run the report, we see either a 1 or a blank/nothing in the “Live in CA?” column.
- We can then edit the report to sum the 1s in the Live in CA column (here, we are not summing/counting the value in a particular field so we can’t use the field name. Instead, we are using a cell reference).
- Now, when we run the report and scroll to the end (where the report footer/the formula is) we can see a count/total of how many people live in CA.
- There are just two simple examples of how if/then can be used; if/then formulas are very powerful tools for changing the way data is displayed on a report, getting at data you might not be able to get at otherwise, and for manipulating data to make it easier to count, sum, or otherwise aggregate.