# How to create list calculations using the formula field

We’re excited to announce the newest field type in Affinity - the **formula** field. Formula fields are now available in your Affinity lists to create basic list calculations, eliminating the need to perform these calculations outside of Affinity.

**Overview**

- How to create formula fields within your Affinity lists
- How to identify and fix errors for miscalculations
- Common use cases and examples
- Where you can use formula fields within Affinity
- Important Callouts

## How to create formula fields within your Affinity lists

- To create a formula field, click into any list and click on the
**Customize View**button or**+ New Column**at the very right.

- If you click the
**Customize View**button, click on the**+**icon on the top-right corner to create.

- If you click the
- Give the new field a name. For this example, we’ll create a new formula field called
**Average Ranking**. Then, select the**Formula**option below. - Select either
**list-specific**or**global**depending on whether you’d like this new formula field to only appear on the list you’re creating it in or to appear across all other lists of the same type (e.g. for this example, it would appear across all other organization lists). Then, click**Add**.- If you make a
**list-specific**formula field, then you can use**both**list-specific fields (from that particular list) and global fields within the formula itself. - If you make a
**global**formula field, then you can**only**use global fields within the formula itself.

- If you make a
- Select the function type you’d like to use - there are currently 5 different function types available (each function type supports up to 8 values):
- Sum - Add up to 8 values from different fields
- Subtract - Subtract up to 8 values from different fields
- Multiply - Multiply up to 8 values from different fields
- Divide - Divide up to 8 values from different fields
- Average - Get the average of up to 8 values from different fields

- For this example column called
**Average Ranking**, these are the steps we would take:- Select
**Average**for the function type. - Then, we will select three number-type fields that will be used for the formula (e.g. Team 1 Ranking, Team 2 Ranking, and Team 3 Ranking).
- Check the formula preview section below to make sure that it’s correct, then click
**Save**.

- Select
- Last but not least, formula fields can be converted into 3 different formats depending on the use case:

- Option 1 - Format as
**Number** - Option 2 - Format as
**Currency (e.g.****Dollar**,**Pound**,**Yen/Yuan**,**Euro**,**Rupee**, and**New Shekel)** - Option 3 - Format as
**Percent**

- Option 1 - Format as
- Not sure what types of calculations to start off with? Check out the “
**Common use cases and examples**” section just below.

## How to identify and fix errors for miscalculations

- Depending on the columns used, you may come across empty cells indicating that one of the values from the columns used is either incompatible or empty.
- Click on the cell to see a preview of the formula so you can identify the issue.
- From there, you can decide whether to:
- Edit the values of the columns being used for the formula field.
- Edit the formula field itself by clicking on the dropdown arrow next to the column name.
- Or leave it as is for now.

- Edit the values of the columns being used for the formula field.

## Common use cases and examples

Here are some of the most common use cases and examples of list calculations you can create for your own lists - note that each bracket containing names or numbers represents a single column:

Example of Column Name |
Formula |

Sum of Commitments | = [Commitment 1 Amount] + [Commitment 2 Amount] + … |

Weighted Investment Amount | = [a number between 0 and 1] * [Investment Size] |

EBITDA Margin | = [EBITDA Amount] / [Revenue Amount] |

Sum of EBITDA and Revenue | = [EBITDA Amount] + [Revenue Amount] |

Post-Money Valuation | = [Pre-Money Valuation Amount] + [Invested Amount] |

Sum of Financial Metrics | = [Valuation Amount] + [Investment Amount] + … |

Average Score | = ([Column 1] + [Column 2] + … [Column 4]) / # of columns |

% of Total | = [a number between 0 and 1] * [Total Amount] |

## Where you can use formula fields within Affinity

Once you’ve created your formula fields, check out all the areas where they can be used:

### Lists (Sheet Views and Board Views)

- On
**sheet views**, you can click the**Customize View**button to show the formula fields within the sheet itself. - On
**board views**, you can click the**Customize View**button to show the formula fields within each card. - Whether it’s a sheet view or board view, you can filter and sort by these formula fields as well.

### Affinity Analytics (Dashboard Views)

- In the list that contains a dashboard view that you’d like to leverage your formula fields within, click the 3-dotted icon and click
**Edit dashboard**(for**Creator**roles only). - From here, you can click
**Add**to create a new visualization tile or click the 3-dotted icon for an existing visualization tile that you’d like to edit. For this example, we’ll show you how to edit an existing visualization tile. - Since we’re editing the tile from showing “Top 10 Orgs by Amount” to “Top 10 Orgs by Average Ranking”, we will do the following:
- Rename the tile.
- Then, search for the
**Average Ranking**formula field, then click on it to make it appear. - Finally, remove any fields that you wouldn’t like to see - in this example, we’ll remove
**Sum of Amount**since it’s no longer necessary.

- To clean up the results, we will do the following:
- Click the
**Filter**icon next to**Average Ranking**on the left sidebar to remove any rows that may include null values. - Click the
**Arrow**icon next to “List Summary**Average Ranking**” in the**Data**section to sort by highest to lowest. - Click
**Run**to generate the new results, then click**Save**.

- Click the
- Now, you’ll see the visualization tile showing “Top 10 Orgs by Average Ranking” - click
**Save**one more time to save all the changes made to the dashboard view and you’re all set.

### Profile Pages

- Go to
**Settings**>**Profile Field Customization**and click the**Menu**icon to select from**General Fields**or from a specific list depending on whether the formula fields are global or list-specific. - Once you click
**Save**, navigate to a profile page that would include the formula fields you selected.

## Important Callouts

- Currently, each formula field that you create can only use one function type at a time - you
**cannot**combine two or more function types (e.g. multiplication and subtraction, addition and division, etc.). - If you make a
**list-specific**formula field, then you can use**both**list-specific fields (from that particular list) and global fields within the formula itself. - If you make a
**global**formula field, then you can**only**use global fields within the formula itself. - Formula fields will
**not**be available for display on the Affinity mobile app, Affinity extensions (Pathfinder and Outlook), or Basic Reporting. - Only single-value number-type fields can be used as inputs within formula fields during this time. Multi-value number-type fields or any other field types
**cannot**be used as inputs. - Formula fields
**cannot**use other formula fields as inputs during this time. - If you're using the
**Sum**function type, please note that any empty cells from the input columns will render the formula invalid, rather treating the empty cell as zeros and completing the function (see below).**Note**: If you prefer for these empty cells to be treated as zeros for**Sum**function types, please provide your feedback at support@affinity.co.