Column Format - Formula option

The 'Formula' option available in the calculated column pop-up window's Column Format drop-down list is a special selection made available when the calculated field result is a percentage (Data Type = Percent). It is designed to replace the Sum option when the sum of all the values in that calculated column is not logical. Using this option, instead of calculating a total using the data in the calculated field column, we apply the formula across the totals of each field within the formula. This option was primarily added for those situations where the calculated column returns a percentage value.

 

 

 

Notes: The Formula option is only available on calculated fields. Formula is the only option when the calculated field Data Type is set to 'Percent'

 

 

 

Example when creating a calculated column that calculates the percentage completed (% Complete).

 

The calculated field is labeled '% Complete' and the Data Type is set to Percent. The formula divides the Hours Worked by the Budgeted Hours. The formula also includes terms to prevent a divide by zero error.

 

 

The completed report is shown below. In this case, simply displaying the sum of the data at the bottom of the '% Complete' column would not make sense. The 'Formula' option (shown in the Column Format drop-down list) calculates the '% Complete' using the totals of the Hours Worked and Budgeted Hours columns (70 / 75 = 93.33%).