How do you use an array formula to calculate a running total that decreases as you move up rows?

1
votes

When an employee works overtime for a client, we allocate those hours to the client's most recent shift (farthest row down for that employee), and then allocate the remaining OT up the rows. Column D lists total hours to allocate. Column E is a formula for OT hours available; I am trying to convert Col E to an array, but am running into a circular dependency error:

Formula that works

=IF(D2>0,D2,E3-C3)

Array formula that results in a circular dependency error:

=ARRAYFORMULA(IF(D2:D>0,D2:D,E3:E-C3:C))

I do not want to enable iterative calculations, as my sheet is about two thousand rows. I have read cumulative sum posts that incorporate MMULT and ROW, or SUMIF and ROW into the ARRAYFORMULA function, but I cannot figure out how to cumulatively decrease a number as it moves up rows.

enter image description here

Here is a link to the sheet Thank you.

1
arraysgoogle-sheetsgoogle-sheets-formulacumulative-sum
For me your formula =ARRAYFORMULA(IF(D2:D>0,D2:D,E3:E-C3:C)) is working fine and it outputs the same as column E. What is exaclty the problem you are having? – Raserhin
@Raserhin. Thanks. When I enter the array formula in Col E, I get this message: "Circular dependency detected. To resolve with iterative calculation, see File > Spreadsheet Settings." I could enter the formula in another Col, but then I still have Col E with formulas in each row, which I am trying to avoid. – Dustin
are negative values importtant too? – player0
@player0. Thanks for asking. No, negative values are not necessary. Col F will result in the same answer if Col E is 0, negative, or blank. – Dustin

1 Answers

2
votes

I made a new tab called MK.Help and put this formula in E2. If i understand what you're going for, i think it'll work?

=ARRAYFORMULA(MMULT(N(A2:A=TRANSPOSE(A2:A))*(ROW(B2:B)<=TRANSPOSE(ROW(B2:B))),D2:D-C2:C)+C2:C)