Formula Multiple 3 fields


#1

What I want to do is insert a formula in an airtable field that multiply’s values I input in Hourly rate, hours and rate fields. This is the formula I have tried but I get an error
{Hourly Rate}HoursRate

I’ve attached the spead sheet image of what I want to do on airtable


#2

I don’t understand your image, but the formula misses some kind of operation:

  1. If the hourly rate is always the same, you can out it in the formula: {Hours} * 40
  2. If you have several rates, you could use a Single Select field, so formula is something like: {Hours} * {Rate}. Maybe you have to use INT() in the formula to get the number from the select field.
  3. If you enter a different rate for every task, you could have a Currency field, and the same previous formula.

#3

There’s your problem, right there. :wink:

Without breaking into my ‘Airtable Is Not a Spreadsheet’ sermon, let me note that, um, Airtable is not a spreadsheet; while that metaphor may be an excellent way to introduce someone to Airtable’s look and feel, it often leads to confusion for new users, as it suggests false equivalences. Let me try to explain using an analogy that no doubt is equally as far off the mark…

The obvious — and erroneous — way to visualize the comparison between Airtable and a spreadsheet is:

Airtable spreadsheet
row row
table spreadsheet
base workbook

Based on data visibility, though, the reality is more like this:

Airtable spreadsheet
row spreadsheet
table workbook
base workbooks(?)

As I said, the analogy isn’t perfect. The thing to remember, though, is that every item in a spreadsheet has visibility of any other item in its two-dimensional plane. In Airtable, though, an item can see only those items along the same row. There are ways around this, but they all involve somehow getting data to surface on the necessary row.

If one were to implement your sample screenshot literally within Airtable, the result would be five records containing such fields as {Date}, {Hours}, {Rate}, and so forth, along with a sixth record containing only the {Hourly Rate}. Since none of the records has any visibility into any other record, as you discovered, you cannot use {Hourly Rate} in a calculation.

The easiest solution, then, is to add {Hourly Rate} as a seventh column of your table; then, the formula

{Hours}*{Rate}*{Hourly Rate}

will work.

To keep employees (or you!) from having to enter {Hourly Rate} for each record, there are options:

  1. You can create {Hourly Rate} as a numeric field with a default value; collaborators with edit ability can change the default value to reflect their own hourly rates before submitting an overtime request,
  2. Similarly, you could create a table per employee, with that employee’s hourly rate ‘baked’ into the table.
  3. You could create a third table, [Employees], which includes (at least) the employee’s name or number and his or her hourly rate. By using a field linked to the former, you could then access the latter for calculations. (Of course, then you’d face the problem of having to enter the employee’s name or number into each record… :wink: )
  4. There are ways, involving linking every record in one table to a single record in another table and getting tricky with passing data back and forth between tables, to share common data across multiple records — but, frankly, they all are probably overkill for this solution. If none of the other four options are acceptable, reply to this response, and I’ll toss together an approach.

(Hmmm… I see that all of my replies today should come with an embedded TL;DR tag…)


#4

Thanks. Sort of working now…

So it seems in order to perform the calculation, I can’t have a column set as currency (for the hourly rate) and another column set as multiple select (for the overtime rate)

Now that all columns are set as numeric, the formula is working !!!

Weirdly, setting the default value for hourly rate is not pre-filling the cell ?


#5

Actually the default value IS working when I add a new record :slight_smile:


#6

Single-select fields evaluate as strings, so to use the field in a numeric calculation, simply wrap it with the VALUE() function:

{Hours}*VALUE({Overtime Rate})*{Hourly Rate}

#7

Brilliant
Working perfectly
Thanks again