I have a Numbers sheet for tracking work on podcast editing. The source table includes columns for the episode name, the work start and end times, a work duration calculated from those two, and the final length of the edit (expressed as a duration value) at the end of that session of work. So like this:
I use another table to compile aggregate data for each individual project, and I want one column to show the final length of each episode once all editing was complete.
As an approximate I used MINIFS to find the shortest end length that matched the episode number. But on many episodes (as in the example above) the final editing task is splicing in additional audio, making the episode longer, so this method is inaccurate.
What I actually need is a column that returns the end length from the row in the source table which both matches the Episode number, and has the maximum (i.e. most recent) work end time.
Is this possible with just the two tables? Or do I need to add in an additional column or interim table to make this work?