Evenly divide number value between objects in a lookup column

Hi all, I’m working on a personal time-tracking calculator for my freelance work. I’m using the table called “Deliverables” to average the amount of money I’ll earn from each deliverable. This happens by targeting the “Time tracker” table, filtering by the deliver that matches with the row in “Deliverables” and then summing the a column called “Hourly cost.”

What I’d like to be able to do, but can’t figure out how to write a formula for, is get that same calculation even when a single row in “Time tracker” has multiple deliverables in its “Deliverables” column. An example being, I’m spending 15 minutes adding in UPC codes to 3 different beer labels. I’d rather have one task called “Adding in UPC codes” and then split the time spent on that between the 3 deliverables, rather than have 3 separate rows for each one. The cost of that 15 minutes (which would be multiplied by an hourly rate set in the “projects” table) would need to be evenly divided betewen all 3 deliverables.

Eventually I’ll average these sums in the last table, “Deliverable types", so I can get a sense of how much I’ve charged past clients for things.

Originally I thought a formula like this applied to the “Total” column in the “Deliverables” table would do the trick, but I think I’m misunderstanding how contain() works:

If([Time tracker].Deliverable.Contains(thisRowthisRow.[Deliverable name]),[Time tracker].[Hourly cost].Sum()/Count([Time tracker].Deliverable,""))

Open to all and any advice and suggestions!

Hi @Alice_Packard,

This is a review of your doc:

Assuming I correctly understood your need (do point me in the right direction, otherwise), three things:

  1. Deliverable column should be a lookup for the Deliverables table: it’s easier - and more consistent - than checking text matching
  2. I added a handy Works column that is actually retrieving the rows form Time Tracker table related to that specific row, to simplify the Total computation.
  3. [optional] I added a decoupled table for the hourly adjustment (that with my vivid imagination I called Hourly Rate Adjustments), so that you can both better control potential new/different rates without compromising the overall table formula. And the formula is itself much more readable.

I hope this helps: please let me know.
Cheers

1 Like

Federico! Thanks for taking the time to reply, really appreciate it.

First, I really appreciate the de-coupled table to control the hourly rate adjustments—the SwitchIf formula I was using did seem quite bulky, and I love this as an alternative.

I think you’ve mostly understood my original ask, but I’ll rephrase here as I don’t think we’re at a solution yet. With the example data in the Doc, I’m looking to divide the $255 worth of work between all three deliverables that are tied to the task of “Adding UPC codes.” This would mean that in the “Deliverables” table, Carrot Ginger Saison, Double hopped IPA, and Grapefruit Radler would each have a total of $85, not $255. That’s the formula I’m stuck on how to properly write.

Do let me know if that makes sense, if not I’m happy to explain it another way!

Hi @Alice_Packard,
OK, I guess I understood: I changed the formula in the Total column, by adding the unique Deliverable occurrences among all the works.

Let me know it this is the expected outcome.

Cheers!

Edit:
Just to better explain the formula:
thisRow.Works.Deliverable.ListCombine().Unique().Count()

Works: is a list of rows from Time tracker table
Deliverable: is therefore a list of a list of rows (all the deliverables for each potential row)
ListCombine(): creates a single list of all the Deliverable occurrences.
Unique(): takes only unique values (in case of duplicates, as in the last deliverables row)
Count(): well… their count

2 Likes

Ah, wonderful! It all seems so simple now that I’m looking at a working formula :sweat_smile: thanks so much for helping me out with this one Federico, very much appreciate it :pray:

1 Like

Actually I’m going to re-open this up to solutions—I looked more closely at the formulas and they don’t work as intended. I tested them by putting in more data into the tables, and the math doesn’t add up to what it should be:

In this scenario, the deliverable named “Carrot ginger saison” should have a total cost of $1,320 based on the three tasks where it’s been worked on. $600 for a task named “edits” + $600 for a second task also named “edits”, and then $120 which comes out of the “Adding UPC codes” task (because the $360 total would be evenly distributed amongst the 3 deliverables, which is how you arrive at $120).

I think I’ve come to a solution but wanted to share and see if it makes sense to folks:

  1. I added a column to the “Time Tracker” table called “Distributed cost” with this formula applied: thisRow.[Total Cost]/thisRow.Deliverable.Count() which gives me the even distribution of the money earned by each task amongst all deliverables associated with it. This solves my problem of some tasks having only one deliverable associated with them, and some tasks having multiple.
  2. I changed the formula on the column “Total” in the “Deliverables” table (which is meant to tell me how much money I’ve earned by working on that deliverable) to: thisRow.Tasks.[Distributed Cost].Sum()

Here’s the updated copy of my sandbox dock so you can see these changes for yourself:

Hi @Alice_Packard,

I think this is correct.
So basically you need the approximate average earn by delivery within a project, right?
(approximate because the effective hourly cost taken by each deliverable is evened).

If I’m not wrong, you could have reached the same outcome with the previous implementations without dividing the deliverable costs by number of Tasks (not sure because you directly changed it).

Anyway, great job!