CODEWITHSUNDEEP
if-statementgoogle-sheetscircular-dependencygoogle-sheets-formulaarray-formulas
Citadel
Citadel

Reputation: 13

'Arrayformula' which references values in cells above, within array column?

I'm attempting to array formula so that specific values from future Forms responses will be calculated automatically. Unfortunately, the formula uses the value in the above cell in the same column to return a running total, and I can't figure out how to turn it into an array without running into circular dependency errors, or having the formula break down and stop working properly.

The formula in A1 takes all my unique product SKUs from a pivot table, transposes them across the top row, and adds three columns between them:

A1: =(SPLIT(JOIN("|"&rept("|",3),TABLE!A2:A),"|",1,0))

The formula in A2, E2, etc. queries my Live Form responses (DATA!) for the item SKU, returning timestamps, actions, and quantities for each form response relating to that SKU:

A2 =query(DATA!$A:$D,"select A,C,D where (B="&A1&") and C<>'SUBTRACT'",1)

My problem arises in trying to make an array out of the formula in D3. This column is meant to give a running tab of the current number of pieces of stock which are "reserved" for upcoming jobs. As the stock is "ADD"ed, the reserve qty is satisfied and decreases, to a minimum of 0:

D3 =if(B3="RESERVE",D2+C3,if(and(B3="ADD",C3>D2),D2-D2,D2-C3))

This formula gives me the desired results and works if I click and drag it down to copy it into below cells in the same column, but I'd really like it to automatically apply new form responses add data to additional rows.

My clumsy attempt at a fix, based on my limited experience with ARRAYFORMULA only returns RESERVE values as-is, and an error message:

=arrayformula(if(F3:F="RESERVE",H2:H+G3:G,if(and(F3:F="ADD",G3:G>H2:H),H2:H-H2:H,H2:H-G3:G)))

Error. Array arguments to GT are of different size.

Could someone please help me figure it out?

Upvotes: 1

Views: 2929

Answers (1)

player0
player0

Reputation: 1

  • enter spreadsheet settings
  • go to Calculation
  • turn Iterative calculation to On

  • then use this formula

    =ARRAYFORMULA(IF(LEN(B3:B),
 IF(B3:B="RESERVE",D2:D+C3:C,
 IF((B3:B="ADD")*(C3:C>D2:D), D2:D-D2:D, D2:D-C3:C)), ))

Upvotes: 1

Related Questions