> ## Documentation Index
> Fetch the complete documentation index at: https://support.affinity.co/llms.txt
> Use this file to discover all available pages before exploring further.

# How to create list calculations using the formula field

> Use formula fields to calculate values from other fields in your list — sums, averages, weighted values, and other custom metrics.

<Note>
  **Advanced** — power-user technique.
</Note>

Use formula fields in your Affinity lists to create basic list calculations, eliminating the need to perform these calculations outside of Affinity.

## How to create formula fields within your Affinity lists

1. To create a formula field, click into any list and click on the **Customize View** button or **+ New Column** at the very right.
   <img src="https://mintcdn.com/support-affinity/IebNr0f0enU-dPwA/images/features-lists/how-to-create-list-calculations-using-the-formula-field/1.jpg?fit=max&auto=format&n=IebNr0f0enU-dPwA&q=85&s=dd7945a6d6bc6ac601ec4e1c6577af5e" alt="" width="2291" height="761" data-path="images/features-lists/how-to-create-list-calculations-using-the-formula-field/1.jpg" />
   * If you click the **Customize View** button, click on the **+** icon on the top-right corner to create.
     <img src="https://mintcdn.com/support-affinity/IebNr0f0enU-dPwA/images/features-lists/how-to-create-list-calculations-using-the-formula-field/2.jpg?fit=max&auto=format&n=IebNr0f0enU-dPwA&q=85&s=72ccdfdec47964720c776891b2a2bf09" alt="" width="2100" height="672" data-path="images/features-lists/how-to-create-list-calculations-using-the-formula-field/2.jpg" />
2. 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.

<img src="https://mintcdn.com/support-affinity/IebNr0f0enU-dPwA/images/features-lists/how-to-create-list-calculations-using-the-formula-field/3.jpg?fit=max&auto=format&n=IebNr0f0enU-dPwA&q=85&s=2af1e268539fd7d100c8953a20fbfb67" style={{ width:"43%" }} width="1040" height="1388" data-path="images/features-lists/how-to-create-list-calculations-using-the-formula-field/3.jpg" />

3. 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.

<img src="https://mintcdn.com/support-affinity/IebNr0f0enU-dPwA/images/features-lists/how-to-create-list-calculations-using-the-formula-field/4.jpg?fit=max&auto=format&n=IebNr0f0enU-dPwA&q=85&s=dad69104a119dfffdd36671471b12391" style={{ width:"47%" }} width="1020" height="1060" data-path="images/features-lists/how-to-create-list-calculations-using-the-formula-field/4.jpg" />

4. Select the function type you'd like to use - there are currently 5 different function types available (each function type supports up to 16 values):
   * Sum - Add up to 16 values from different fields
   * Subtract - Subtract up to 16 values from different fields
   * Multiply - Multiply up to 16 values from different fields
   * Divide - Divide up to 16 values from different fields
   * Average - Get the average of up to 16 values from different fields

<img src="https://mintcdn.com/support-affinity/IebNr0f0enU-dPwA/images/features-lists/how-to-create-list-calculations-using-the-formula-field/5.jpg?fit=max&auto=format&n=IebNr0f0enU-dPwA&q=85&s=7752b9afcaf9b090c24b7e17cea024ad" style={{ width:"42%" }} width="862" height="1128" data-path="images/features-lists/how-to-create-list-calculations-using-the-formula-field/5.jpg" />

5. 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**.

<img src="https://mintcdn.com/support-affinity/IebNr0f0enU-dPwA/images/features-lists/how-to-create-list-calculations-using-the-formula-field/6.jpg?fit=max&auto=format&n=IebNr0f0enU-dPwA&q=85&s=b2aa9632bacaeafeb225d0c7d2a40eb8" style={{ width:"49%" }} width="926" height="1134" data-path="images/features-lists/how-to-create-list-calculations-using-the-formula-field/6.jpg" />

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

<img src="https://mintcdn.com/support-affinity/IebNr0f0enU-dPwA/images/features-lists/how-to-create-list-calculations-using-the-formula-field/7.jpg?fit=max&auto=format&n=IebNr0f0enU-dPwA&q=85&s=335d1d4f609a104f1c1354b999a147f4" style={{ width:"38%" }} width="572" height="870" data-path="images/features-lists/how-to-create-list-calculations-using-the-formula-field/7.jpg" />

1. Option 1 - Format as **Number**
2. Option 2 - Format as **Currency (e.g. Dollar**, **Pound**, **Yen/Yuan**, **Euro**, **Rupee**, and **New Shekel)**
3. Option 3 - Format as **Percent**
4. 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

1. Depending on the columns used, you may come across empty cells (as shown with a dash "-") indicating that one of the values from the columns used is either incompatible or empty.**Note**: When it comes to formula fields based on addition, subtraction, division, and multiplication, empty cells will be automatically treated as zeros (0) to complete the calculation. However, formula fields based on **averages** will consider empty cells as null and therefore will **not** complete the calculation.
   <img src="https://mintcdn.com/support-affinity/IebNr0f0enU-dPwA/images/features-lists/how-to-create-list-calculations-using-the-formula-field/8.jpg?fit=max&auto=format&n=IebNr0f0enU-dPwA&q=85&s=5a9c945ed42533de3e715777d265bb56" alt="" width="2192" height="768" data-path="images/features-lists/how-to-create-list-calculations-using-the-formula-field/8.jpg" />
2. Click on the cell to see a preview of the formula so you can identify the issue. In this example, the "Team 1 Ranking" field value is empty for **Airbnb**, which is why the "Average Ranking" field value is empty as well.
   <img src="https://mintcdn.com/support-affinity/IebNr0f0enU-dPwA/images/features-lists/how-to-create-list-calculations-using-the-formula-field/9.jpg?fit=max&auto=format&n=IebNr0f0enU-dPwA&q=85&s=e289486fef28d5eb9187a8be86e7256a" alt="" width="2186" height="946" data-path="images/features-lists/how-to-create-list-calculations-using-the-formula-field/9.jpg" />
3. From here, you can decide whether to:
   * Edit the values of the columns being used for the formula field.
     <img src="https://mintcdn.com/support-affinity/IebNr0f0enU-dPwA/images/features-lists/how-to-create-list-calculations-using-the-formula-field/10.jpg?fit=max&auto=format&n=IebNr0f0enU-dPwA&q=85&s=4e25cf4374e16ebcd22b9515fd5e1d3c" alt="" width="2190" height="762" data-path="images/features-lists/how-to-create-list-calculations-using-the-formula-field/10.jpg" />
   * Edit the formula field itself by clicking on the dropdown arrow next to the column name.
     <img src="https://mintcdn.com/support-affinity/IebNr0f0enU-dPwA/images/features-lists/how-to-create-list-calculations-using-the-formula-field/11.jpg?fit=max&auto=format&n=IebNr0f0enU-dPwA&q=85&s=31060d51cd43c8d5466ff6355222a3ce" alt="" width="2196" height="764" data-path="images/features-lists/how-to-create-list-calculations-using-the-formula-field/11.jpg" />
   * Or leave it as is for now.

## 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)

1. On **sheet views**, you can click the **Customize View** button to show the formula fields within the sheet itself.
   <img src="https://mintcdn.com/support-affinity/IebNr0f0enU-dPwA/images/features-lists/how-to-create-list-calculations-using-the-formula-field/12.jpg?fit=max&auto=format&n=IebNr0f0enU-dPwA&q=85&s=7202e1b07d47928b6b68adebaab875f7" alt="" width="2200" height="766" data-path="images/features-lists/how-to-create-list-calculations-using-the-formula-field/12.jpg" />
2. On **board views**, you can click the **Customize View** button to show the formula fields within each card.
   <img src="https://mintcdn.com/support-affinity/IebNr0f0enU-dPwA/images/features-lists/how-to-create-list-calculations-using-the-formula-field/13.jpg?fit=max&auto=format&n=IebNr0f0enU-dPwA&q=85&s=b545d93048f7af94971ddefa6450add5" alt="" width="2194" height="1310" data-path="images/features-lists/how-to-create-list-calculations-using-the-formula-field/13.jpg" />
3. Whether it's a sheet view or board view, you can filter and sort by these formula fields as well.
   <img src="https://mintcdn.com/support-affinity/IebNr0f0enU-dPwA/images/features-lists/how-to-create-list-calculations-using-the-formula-field/14.jpg?fit=max&auto=format&n=IebNr0f0enU-dPwA&q=85&s=8cf11b63877d6db84e1cd9bbe114b001" alt="" width="2202" height="924" data-path="images/features-lists/how-to-create-list-calculations-using-the-formula-field/14.jpg" />

### Affinity Analytics (Dashboard Views)

1. 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).
   <img src="https://mintcdn.com/support-affinity/IebNr0f0enU-dPwA/images/features-lists/how-to-create-list-calculations-using-the-formula-field/15.jpg?fit=max&auto=format&n=IebNr0f0enU-dPwA&q=85&s=789c549994c7c2a1119f0de7939c54de" alt="" width="2190" height="1160" data-path="images/features-lists/how-to-create-list-calculations-using-the-formula-field/15.jpg" />

2. 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.

<img src="https://mintcdn.com/support-affinity/IebNr0f0enU-dPwA/images/features-lists/how-to-create-list-calculations-using-the-formula-field/16.jpg?fit=max&auto=format&n=IebNr0f0enU-dPwA&q=85&s=c4d2bb9ba101b1472646792bd15871ac" style={{ width:"69%" }} width="2202" height="1394" data-path="images/features-lists/how-to-create-list-calculations-using-the-formula-field/16.jpg" />

3. 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:
   1. Rename the tile.
   2. Then, search for the **Average Ranking** formula field, then click on it to make it appear.
   3. 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.

<img src="https://mintcdn.com/support-affinity/IebNr0f0enU-dPwA/images/features-lists/how-to-create-list-calculations-using-the-formula-field/17.jpg?fit=max&auto=format&n=IebNr0f0enU-dPwA&q=85&s=43def8d39fd99e9d10531bbcb4367f16" style={{ width:"90%" }} width="2758" height="1476" data-path="images/features-lists/how-to-create-list-calculations-using-the-formula-field/17.jpg" />

4. 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**.
     <img src="https://mintcdn.com/support-affinity/IebNr0f0enU-dPwA/images/features-lists/how-to-create-list-calculations-using-the-formula-field/18.jpg?fit=max&auto=format&n=IebNr0f0enU-dPwA&q=85&s=87a530f126449a7cc5a5dc0328cc2dc7" alt="" width="2758" height="1476" data-path="images/features-lists/how-to-create-list-calculations-using-the-formula-field/18.jpg" />

5. 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.
   <img src="https://mintcdn.com/support-affinity/IebNr0f0enU-dPwA/images/features-lists/how-to-create-list-calculations-using-the-formula-field/19.jpg?fit=max&auto=format&n=IebNr0f0enU-dPwA&q=85&s=8afc9c3b3de80b8d23d2add672c50d26" alt="" width="2846" height="1068" data-path="images/features-lists/how-to-create-list-calculations-using-the-formula-field/19.jpg" />

### Profile Pages

1. 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.
   <img src="https://mintcdn.com/support-affinity/IebNr0f0enU-dPwA/images/features-lists/how-to-create-list-calculations-using-the-formula-field/20.jpg?fit=max&auto=format&n=IebNr0f0enU-dPwA&q=85&s=efccce3f638a1a435bddff62b1ea039c" alt="" width="2652" height="1032" data-path="images/features-lists/how-to-create-list-calculations-using-the-formula-field/20.jpg" />

2. Once you click **Save**, navigate to a profile page that would include the formula fields you selected.
   <img src="https://mintcdn.com/support-affinity/IebNr0f0enU-dPwA/images/features-lists/how-to-create-list-calculations-using-the-formula-field/21.jpg?fit=max&auto=format&n=IebNr0f0enU-dPwA&q=85&s=d654289d87c0f893bda6c7ff43af7594" alt="" width="1520" height="1114" data-path="images/features-lists/how-to-create-list-calculations-using-the-formula-field/21.jpg" />

## Common use cases and examples

Here are some of the most common list calculations you can create. Each bracket containing names or numbers represents a single column:

| Example 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]`             |

## Important Callouts

* Each formula field can currently only use **one function type at a time** — you cannot combine two or more function types (e.g., multiplication and subtraction, addition and division).
* If you make a **list-specific** formula field, 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, you can **only** use global fields within the formula itself.
* Formula fields are currently **not** available via the Affinity API, 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. Multi-value number-type fields or any other field types cannot be used as inputs.
* Formula fields **cannot** use other formula fields as inputs at this time.