0

i'm working on a schedule tool, but can't figure something out. I'd like to use the arrayformula function in Google Sheets. It should sum up two values from the previous row.

At the moment I'm using this, but i'd like to convert it into an arrayformula. I've tried several things, but end up with the 'Circular dependency detected' message.

=iferror(IF(REGEXMATCH(C5;"DAY");E5;index(G:G;ROW(G5)-1;1)+H6);index(G:G;ROW(G5)-1;1)+H6)

Here's the doc:

https://docs.google.com/spreadsheets/d/15jXayEN_vjK3nMRjLjaiLspT_rh_rBD_WX_LAZETRSU/

player0
  • 124,011
  • 12
  • 67
  • 124
Marcel Buunk
  • 23
  • 4
  • 1
    Kindly add input table and expected output table as [**text table**](https://webapps.stackexchange.com/a/161855/)(NOT as IMAGE) to the question. Adding such [md tables](https://www.tablesgenerator.com/markdown_tables) greatly increases your chances of getting a elegant answer, as **it is easier to copy/paste**. If you share spreadsheets, your question maybe closed, as questions here must be [self contained](https://meta.stackoverflow.com/a/260455). [Your email address can also be accessed by the public](https://meta.stackoverflow.com/questions/394304/), if you share Google files. – TheMaster Oct 06 '22 at 08:50
  • Ask exactly one question per post. – TheMaster Oct 06 '22 at 08:51

2 Answers2

0

Use the sumif(row()) pattern in cell G4, like this:

=arrayformula( 
  if( 
    isnumber(H4:H); 
    E3 + sumif(row(H3:H); "<=" & row(H3:H); H3:H); 
    iferror(1/0) 
  ) 
)

Format cell G4 as hh":"mm. You should turn off File > Settings > Calculation > Iterative calculation.

doubleunary
  • 13,842
  • 3
  • 18
  • 51
  • Thanks! That's very helpful. One thing I forgot to mention; sometimes a second "day" row is added. If this happens, the time calculation should start from the time of the new day (see G12). I've updated the link with a new day. If i turn off the formula in A3 and C3 won't work. In A the order of the scene numbers is calculated. And in C3 the start and end time is added, if the value in this cell is "DAY". Would be great if you could have one more look at it :). – Marcel Buunk Oct 06 '22 at 16:51
  • That is a different question that will require a different solution. Please post a [new question](https://webapps.stackexchange.com/questions/ask). – doubleunary Oct 06 '22 at 16:56
  • I gather that this is your first question at Stack Overflow. See [What should I do when someone answers my question?](https://stackoverflow.com/help/someone-answers) – doubleunary Oct 06 '22 at 16:57
0

try:

=INDEX(IF(IFERROR(REGEXMATCH(C3:C; "(?i)DAY")); E3:E; SCAN(E3; H3:H; LAMBDA(x; y; x+y))))

enter image description here

player0
  • 124,011
  • 12
  • 67
  • 124