Cumulative Array

[markvdhouten]

Hi All,

Could someone please assist me with a corkscrew type array. I have attached a spreadsheet.

What I am trying to achieve is a result by subtracting Table B from Table A. However, if the result in any cell is negative, then I would like the negative value to be carried forward and netted off against the next cell in the sequence until it has been extinguished (from left to right, top to bottom with the desired result shown in the Required Table).

For instance, in cell H17 there is a -3: I would like not to show the -3 (just show a 0) but to cumulatively deduct it off the next cell in the sequence, until the negative -3 has been extinguished which happens to be the next cell H23. Similarly, cells J16 and J17 are cumulatively netted off by cell K20. The negative number in cell L15 is netted off in cell L21.

I hope my explanation is clearer than mud!

Any help would be appreciated.

[WideBoyDixon]

I can't figure out a way to do that just in a formula. Here's how it could be done with a macro:

Option Explicit
Public Sub SubractTest()

With Sheets("Sheet1")
    SubtractArrays .Range("G3:L6"), .Range("G9:L12"), .Range("G15:L18")
End With

End Sub
Public Sub SubtractArrays(firstArray As Range, secondArray As Range, targetArray As Range)

Dim thisRow As Long
Dim thisCol As Long
Dim carryValue As Long

carryValue = 0
For thisCol = 1 To firstArray.Columns.Count
    For thisRow = 1 To firstArray.Rows.Count
        carryValue = carryValue + firstArray.Cells(thisRow, thisCol) - secondArray.Cells(thisRow, thisCol)
        If carryValue >= 0 Then
            targetArray.Cells(thisRow, thisCol).Value = carryValue
            carryValue = 0
        Else
            targetArray.Cells(thisRow, thisCol).Value = 0
        End If
    Next thisRow
Next thisCol

End Sub

WBD

[markvdhouten]

Hi WBD,

Thank you - this is good. I appreciate the time that you have taken to help me.
Many thanks.

[Bo_Ry]

Please try at G20

=MAX(G3-G9-SUM($F$20:F$23,G$19:G19)+SUM($F$3:F$6,G$2:G2)-SUM($F$9:F$12,G$8:G8),0)

or Spill array for MS365 Insider with LAMBDA Function

=LET(c,SCAN(0,SCAN(0,TRANSPOSE(G3:L6-G9:L12),LAMBDA(a,v,a+v)),LAMBDA(a,v,IF(a>v,a,v))),s,SEQUENCE(ROWS(c),COLUMNS(c)),TRANSPOSE(SMALL(c,s)-IFERROR(SMALL(c,s-1),0)))

[markvdhouten]

Many thanks Bo_Ry,

This is terrific. I don't have the insider version, so I appreciate your giving me an alternative solution.
Thank you for the time that you have taken to help me.