This website uses Cookies. Click Accept to agree to our website's cookie use as described in our Privacy Policy. Click Preferences to customize your cookie settings.

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Airtable Community
- Discussions
- Ask A Question
- Formulas
- Re: Simple Division of Two Fields

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

0
3082
1

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Comment Post Options

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Aug 20, 2020 10:32 AM

I am trying to divide the total of two fields. The first is currency, the second is a number. Wanting currency as a result; eg total weekly pay / total hours worked = average hourly pay.

Here’s what I’ve tried and both have come back as INFINITY:

SUM({Total Paid})/SUM(Hours)

SUM({Total Paid}/{Hours})

This gets an error: SUM({Total Paid})/SUM{(Hours)}

6 Replies 6

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Aug 20, 2020 07:41 PM

Welcome to the Airtable community!

Are you trying to divide the totals of two columns? Airtable formulas cannot do calculations down columns. Airtable formula fields can only do calculations across rows.

To get the total for a set of records, you need to use linked records. Then use a rollup field to calculate the sum.

If you describe your base in more detail, and perhaps include a screen capture, we might be able to help you more.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Aug 20, 2020 11:03 PM

Welcome to the community, @Debbie_Kagy! :grinning_face_with_big_eyes: First, you don’t need to wrap the `SUM()`

function around the field references. This should work:

```
{Total Paid} / Hours
```

From there you can format your field as currency.

The `SUM()`

function is used when you want to add several individual values, though frankly the `+`

operator is the more concise way of doing that, similar to how the `&`

operator is a more concise way of concatenating things into a string compared to the `CONCATENATE()`

function. However, `SUM()`

is quite useful when adding items as part of a rollup field, where all you have is an incoming array.

Regarding the placement of curly braces (referring to the version that threw an error), Airtable uses those braces to indicate a field name in a formula. However, they’re only required when the name contains spaces—e.g. “Total Paid” => `{Total Paid}`

—or when the name contains special characters (punctuation, emojis, etc.). By writing:

```
SUM({Total Paid})/SUM{(Hours)}
```

the latter half is looking for a field named “(Hours)”, which doesn’t exist because yours is just “Hours”. The parentheses are part of the `SUM()`

function, and separating them is probably the primary reason that the error was thrown.

As for why `SUM({Total Paid})/SUM(Hours)`

led to infinity, I’m not sure. `SUM({Total Paid})`

should just return the value from the `{Total Paid}`

field because you’re not adding it to anying, and the same for the `{Hours}`

side. In theory, that should return the same result as `{Total Paid} / Hours`

, so the infinity result is truly confusing.

Finally, re: the currency vs number thing, that’s all formatting. The underlying values of a currency field are just numbers. The same thing goes when you format a field as currency. It’s just controlling how the numbers *appear* in the field. It has no bearing on what you do with those numbers in formulas, or the output of those formulas. Just treat them like numbers, and then adjust the field formatting to your liking.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Aug 21, 2020 06:35 AM

`infinity`

because you are dividing by zero. In order to find out why Airtable thinks you’re dividing by zero, we would need a closer look at your data and your field types. For example, lookup fields often produce unexpected results in formula fields.

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Aug 25, 2020 03:30 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Aug 25, 2020 07:22 AM

Thank you for the screen shot. While what you want to do would be simple in a spreadsheet, Airtable is a database, not a spreadsheet, and thus the calculations are not as simple.

Airtable cannot calculate an hourly rate of pay per week in one table with your current base setup. Your weekly hours and weekly pay are in the summary bar, and Airtable cannot use numbers on the summary bar as inputs to calculations.

You could add another table for each week and do rollup calculations in that table.

You could also switch to one row per week and have Monday-Friday hours be individual fields. Then you could use a formula to add up the hours across the row.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Aug 26, 2020 01:24 PM

I agree with this suggestion. I’m doing something similar to track work for unemployment reporting, where I’m required to report work searches and income on a weekly basis. I have a `[Weekly Summary]`

table, and use a formula to build unique labels for each weekly record:

I then have two other tables: one where I record work that I do and income received from that work, and another that’s a log of my work search efforts. Each record links to a single entry from the `[Weekly Summary]`

table, which allows me to easily tally weekly hours worked and income received. To make the linking easier, I created an automation that looks for new work/search records, and automatically links to the week marked as “Current”.

As @kuovonne said, spreadsheets and databases treat data differently. Once you understand how database design works, you’ll discover how this kind of setup actually makes data organization easier than a spreadsheet in many ways.