How to create list calculations using the formula field

Avatar
Brian Lee
  • Updated

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

  1. To create a formula field, click into any list and click on the Customize View button or + New Column at the very right.
    1 create new column in a list.png

    1. If you click the Customize View button, click on the + icon on the top-right corner to create.
      1.1 click plus icon in customize view.png

  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.
    2 select formula option.png

  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.
    1. 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.
    2. If you make a global formula field, then you can only use global fields within the formula itself.
      3 choose list specific or global.png

  4. 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):
    1. Sum - Add up to 8 values from different fields
    2. Subtract - Subtract up to 8 values from different fields
    3. Multiply - Multiply up to 8 values from different fields
    4. Divide - Divide up to 8 values from different fields
    5. Average - Get the average of up to 8 values from different fields
      4 select a function type.png

  5. For this example column called Average Ranking, these are the steps we would take:
    1. Select Average for the function type.
    2. 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).
    3. Check the formula preview section below to make sure that it’s correct, then click Save.
      5 fill out fields then click save.png

  6. Last but not least, formula fields can be converted into 3 different formats depending on the use case:
    9 formula field format options.png
    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

  7. 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 indicating that one of the values from the columns used is either incompatible or empty.
    6 new formula field on the right.png

  2. Click on the cell to see a preview of the formula so you can identify the issue.
    7 click on empty cell to see formula.png

  3. From there, you can decide whether to:
    1. Edit the values of the columns being used for the formula field.
      8.1 edit field value.png

    2. Edit the formula field itself by clicking on the dropdown arrow next to the column name.
      8.2 edit formula field itself.png

    3. Or leave it as is for now.

 

 

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)

  1. On sheet views, you can click the Customize View button to show the formula fields within the sheet itself.
    1 sheet view.png

  2. On board views, you can click the Customize View button to show the formula fields within each card.
    2 board view.png

  3. Whether it’s a sheet view or board view, you can filter and sort by these formula fields as well.
    3 filter or sort in both sheet view and board view.png

 

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).
    1 click edit dashboard.png

  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.
    2 create new visualization tile or edit existing tile.png

  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.
      3 edit tile name and add average ranking dimension and remove other dimension.png

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

  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.
    5 click save for entire dashboard view.png

 

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.
    1 click profile field customization in settings.png

  2. Once you click Save, navigate to a profile page that would include the formula fields you selected.
    2 view formula field on profile page.png

 

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).
    empty cell inputs not treated as zero for sum funtion types.png
    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.

Related articles

Articles in this section

See more