Common Reporting Formulas

Written by Lauren Neulinger

Last published at: April 1st, 2020

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 (for example, if you want 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))
    • Note: the "7" in the formula above indicates the month. In this example, 7 for July.