Simple Division of Two Fields

[Debbie_Kagy]

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

[kuovonne]

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.

[Justin_Barrett]

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.

[kuovonne]

You’re probably getting 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.

[Debbie_Kagy]

Thanks for the advice everyone. Here’s a screenshot of what I’m working with currently. I’ve tried the rollup and linking within the table and to a different table but can’t seem to figure it out. I’d prefer to keep this all on the same table since it’s theoretically so simple. Again, just trying to get an hourly rate of pay each week.

[kuovonne]

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.

[Justin_Barrett]

kuovonne:

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

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.