0

I'm not sure how to word this, but im creating a pilot episode for a film I'm making and I am not sure what function(s) to do so that when I add a duration to a specicfic shot, it updates a row down and a column right to it (adding to the time stamp of the episode). Anybody have any ideas?

enter image description here

enter image description here

2 Answers 2

1

SCAN Formula

Make sure the number format for all relevant columns is set to Duration and place the following formula in B2.

={0; (SCAN(0,TOCOL(A2:A,1), 
   LAMBDA(s, n, s+n)))}
  • The SCAN function is perfect for this task. It returns the intermediary calculations from a LAMBDA function that adds s an accumulator (subtotal) to n a new number.
  • variable names are arbitrary, use whatever you prefer.
  • TOCOL is used because it can remove empty rows. In this way, the entire column A can be specified as the range allowing the formula to expand dynamically as new data is added.
  • An initial duration of 0 is added to the array to offset it because you want starting, not ending, timestamps.

A table showing initial durations, formula, and starting timestamps

Both Starting and Ending Timestamps

The formula can be easily adapted to return a two-column array of starting and ending timestamps in a single formula, since it is essentially the same formula, simply offset by one row:

=LET(end,SCAN(0,TOCOL(A2:A,1), 
   LAMBDA(s, n, s+n)),
   {{0;end},{end;""}})
  • LET is used to store the result of
    SCAN(0,TOCOL(A2:A,1), 
       LAMBDA(s, n, s+n))
    
    in the variable end
  • an array is returned where
    • column 1 is end with a 0 prepended as the first row
    • column 2 is end with a blank value appended as the last row

table showing initial durations, formula, and starting and ending timestamps

Functions

LAMBDALETSCANTOCOL

0

A simple = P3 + P4 in the Q4 should work. Q3 doesn't need a formula; it'll just be 0:00:00. Just drag down the formula by the bottom-right square and it'll add it up for you.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.