Flexible Sum to achieve given total

[Ravi Malpani]

I have 12 cells in a row. Each cell contains a number for example 12, 15,10, 5,7,15,25,12, 15,10, 5,7. on the other hand I have a number (50). I want to create a flexible formula to know total of how many cells will reach to a total of 50. So in the given example starting from first column total of 7 columns will be higher than 50. Can you please help me with this formula.

 

[AlanSidman]

Here is a VBA solution for you

Option Explicit

Sub FindFifty()
    Dim i As Long, j As Long
    Dim x As Long, y As Long
    Dim lr As Long
    lr = Range("A" & Rows.Count).End(xlUp).Row
    x = 0
    For j = 1 To lr
5:
        For i = 1 To 12
            y = Cells(j, i)
            x = x + y
            If x >= 50 Then
                MsgBox (Cells(j, i).Address)
                x = 0
                j = j + 1
                GoTo 5
            End If
        Next i
    Next j
End Sub

How to install your new code
Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Press Alt+F11 to open the Visual Basic Editor
Choose Insert > Module
Edit > Paste the macro into the module that appeared
Close the VBEditor
Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

To run the Excel VBA code:
Press Alt-F8 to open the macro list
Select a macro in the list
Click the Run button

 

[bosco_yip]

A formula solution



1] Assume 12, 15,10, 5, 7,15, 25,12, 15,10, 5 and 7 located in A2:L2

2] "Given total" in N2 : 50

3] In "Result" O2, enter :

=MATCH(1,INDEX(0+(SUMIF(OFFSET(A2,,,,COLUMN(A2:L2)),"<>")>N2),0),0)

Regards
Bosco

 

[John Jairo V]

Hi, to all!

Another option, without volatile functions, could be (in bosco's file):

=MATCH(N2,MMULT(N(+A2:L2),--(COLUMN(A2:L2)>TRANSPOSE(MMULT(1,COLUMN(A2:L2))))))

Blessings!

 

[Ravi Malpani]

Hi, to all!

Another option, without volatile functions, could be (in bosco's file):

=MATCH(N2,MMULT(N(+A2:L2),--(COLUMN(A2:L2)>TRANSPOSE(MMULT(1,COLUMN(A2:L2))))))

Blessings!

Thanks a lot John. This is really great.

 

[Ravi Malpani]

A formula solution

View attachment 53039

1] Assume 12, 15,10, 5, 7,15, 25,12, 15,10, 5 and 7 located in A2:L2

2] "Given total" in N2 : 50

3] In "Result" O2, enter :

=MATCH(1,INDEX(0+(SUMIF(OFFSET(A2,,,,COLUMN(A2:L2)),"<>")>N2),0),0)

Regards
Bosco

Thanks a lot Bosco. I was really looking for something like this. Great Help.

 

[Ravi Malpani]

Thanks a lot Bosco. I was really looking for something like this. Great Help.

I have a additional query on this. Currently we applied this formula adding number from left to right, how can we modify this to add the numbers from right to left.

 

[bosco_yip]

I have a additional query on this. Currently we applied this formula adding number from left to right, how can we modify this to add the numbers from right to left.

Count A2:L2 cumulative numbers > 50 from right to left, try this :

=MATCH(1,INDEX(0+(SUMIF(OFFSET(L2,,,,-COLUMN(A2:L2)),"<>")>N2),0),0)

Regards
Bosco