Allocating expenses to people in another table

I have a really big complicated thing I want to do, but I am trying to break it down into smaller problems. I think I have many parts that I don’t quite know how to do, but we’ll start with this one.

Big picture:
We currently use a spreadsheet of expenses and that spreadsheet also has columns which calculate how much of each expense is allocated to each of 3 fixed/known people, according to a predefined split with each person’s allocation being in a fixed column for that person.

I want to convert this to Airtable and make the people part table driven with the People table listing the person, and their allocation percentage:

  • Person 1, .5
  • Person 2, .4
  • Person 3, .1

So if there are Expenses:

  • Item 1, 100
  • Item 2, 200
  • Item 3, 300

Then I want to have a table or view that ultimately gives me:

  • Person 1, 300 50 + 100 + 150
  • Person 2, 240 40 + 80 + 120
  • Person 3, 60 10 + 20 + 30

The math is not hard, but I don’t know how to configure a new column in the People table to create this conditional rollup.

Hi @Jenny_Nunemacher - the trick is to assign the expense to all people in your linked table:

(You can copy and paste the values from another row/record for quick linking too)

Then in your people table rollup the total value and apply the percentage formula to this:

JB

2 Likes

Thanks, JB. I’ll try that.

Is there a way to assign it to all people automatically?