Trying to find average time per job type

[Cole_Hamel]

Thanks in advance for any help you can offer.

I’ve been wracking my brain for the past week, trying to figure out how I can calculate my teams hours spent on a particular job type.

I’ve imported our teams hours from our time tracking software via CSV file. Right now, I have the items grouped by Client > Project. I’ve filtered by “Project contains > web” Within is also how many hours were spent on that job. These groups are able to tell me the sum of all the hours, per group. What I need to know now, is how to calculate the average amount of time spent on a certain job type, for example, “web design”.

Like I said, been trying this for quite a while, with no results. Any help is much appreciated.

[ScottWorld]

In your screenshot, where it says “Sum”, you can click on that and change it to say “Average”. Does that work for you?

[Cole_Hamel]

Unfortunately that wouldn’t work as it would only calculate the average within one project. I’m looking for the average amongst all projects within a certain job type.

[ScottWorld]

Ah okay, then you would need to group by “Job Type” to get that.

So currently, you’ve got it grouped by “Client” then “Project”. You would add a 3rd grouping after that: “Job Type”.

If that’s too much grouping for you to have on one view, you could create an entirely new view that could be simply grouped by Job Type.

[Cole_Hamel]

The only problem there is that the time tracking software we use doesn’t label by “Job Type”. The way I’ve combatted this is by making two tables with the same dat a, and filtering one for “Project name contains > web” and the other “Project name contains > Identity”. But then I’m still at a loss for how to take the average from there.

[ScottWorld]

You get the average by grouping your records in the way that you want, and then you can average each group.

The only other way to get an average is by manually linking your desired records to the same exact record in another table. Then, from that other table, you can create a ROLLUP field that gives you an average of the linked records. That would probably be your best way of accomplishing this, if you don’t have the ability to group your records.

You may also be able to write a JavaScript script to help you find the average that you’re looking for, but I think the linked table method is probably easier.

[Bill_French]

ScottWorld:

The only other way to get an average … by manually linking your desired records to the same exact record in another table … from that other table, you can create a ROLLUP field …

Where does the insanity end. :winking_face:

Um, how about a simple script that performs a precise aggregation?

[ScottWorld]

Bill.French:

Um, how about a simple script that performs a precise aggregation?

We really just need formula functions that can tap into those summary fields that already appear on the screen. All those summary fields are RIGHT THERE!! Visible on the screen!! But Airtable gives us absolutely no way to access them in our formulas.

[Bill_French]

ScottWorld:

Airtable gives us absolutely no way to access them in our formulas.

It’s clear that you answered the question and had the data model represented the nature of the question, you’d have earned a quick check mark for this response. Unfortunately, data doesn’t always cooperate with basic summation and aggregation features.

Given that the data model isn’t ideally suited for averages by groups, @Cole_Hamel has to either mimic the shape of the data to use that approach, or use a Script Block to compute the aggregations.

[Cole_Hamel]

Couldn’t agree more! Frustrating, but I’ll keep truckin to see if I can find a solution.
Thank you immensely for your help and input @ScottWorld @Bill.French.