• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Sum column until condition is reached - return number of cells in column counted

R

RichBehan

New Member
Here's my example:


Column

A1: Variable(let's say 20)

B1: 5

B2: 6

B3: 3

B4: 7

B5: 3


Looking to count the minimum number of cells in column B it takes to reach 20 (does not need to equal 20 exactly). Looking to return the answer (4) - as it took four cells (5+6+3+7) to reach variable (20) in cell A1.


Thanks
 
Hi Rich,


If you set up a helper column say in C to get a running total of the values, you can use the following formula to get the number:

[pre]
Code: 
=IF(INDEX(C1:C14,MATCH(20,C1:C14,1))<20,MATCH(20,C1:C14,1)+1,MATCH(20,C1:C14,1))
[/pre]
 
If you don't want to use a helper column, you could try using this UDF. The idea behind the solution is that you need to find how many of the largest values you need before you pass the goal mark. To install the UDF, press Alt+F11 to open the VBE, then go to Insert - Module, and paste this in.

[pre]
Code: 
Function LeastFactors(MyArray As Range, Goal As Double) As Integer
Dim RunningTotal As Double
Dim i As Integer

For i = 1 To MyArray.Count
RunningTotal = RunningTotal + WorksheetFunction.Large(MyArray, i)
If RunningTotal >= Goal Then
LeastFactors = i
Exit For
Else
LeastFactors = 0
End If
Next i
End Function
[/pre]
Formula in worksheet would be:

=LeastFactors(B1:B5,A1)
 
Hi RichBehan,


Here is another approach that does not require a UDF. Names are used to simplify (in my opinion)


Create 2 Names:

d - refers to:
Code: 
=Sheet1!$B$1:$B$5

dMMULT - refers to: [code]=MMULT(--(ROW(d)>=TRANSPOSE(ROW(d))),d)


Then use this formula

=MATCH(TRUE,INDEX(dMMULT>A1,,),0)

The result is 4


The non-name approach

=MATCH(TRUE,INDEX((MMULT(--(ROW(Sheet1!$B$1:$B$5)>=TRANSPOSE(ROW(Sheet1!$B$1:$B$5))),Sheet1!$B$1:$B$5)))>A1,,),0)[/code]

Ctrl+Shift+Enter is required due to the use of TRANSPOSE
 
Awesome Kyle!!


I was thinking about formula without any vba code, and had drawn conclusion has it is not possible. You proved me wrong.


i remember you had used mmult function previously in answering some other query as well.


Kyle, do you mind giving some more explanation on MMULT and TRANSPOSE function, as where all we can use this and how it works etc.


Regards,

Prasad DN

PS: of late, I have learned to use sumproduct, but I am still trying to sharpen my skills on CSE formulas and this is definitely one more topic for me to must learn. :)
 
Impressive Kyle. Like prasaddn, I'd be interested to see an explanation of MMULT.


However, I do see a small problem. Using this data set:

5

6

7

7

3


The correct result should be 3 (6+7+7). However, your formula and kchiba's both end up returning 4. There might still be a way to do it using only formulas, but like prasaddn, I could only think of it using VB.
 
Luke M,

based on the original question, 4 is the correct answer as RichBehan indicated the methodology and the desired answer; summing from top to bottom and 4.

Note my formula should read ">=A1" if 20 is to be included.


The minimum number of cells to reach 20 could be done like this.


CSE formula


Code: 
=MATCH(TRUE,(MMULT(--(ROW(B1:B5)>=TRANSPOSE(ROW(B1:B5))),LARGE(B1:B5,ROW(B1:B5))))>=A1,0)


Which gives the result of 3

Named version

[pre]d = B1:B5
dMMULT = MMULT(--(ROW(d)>=TRANSPOSE(ROW(d))),LARGE(d,ROW(d)))

=MATCH(TRUE,dMMULT>=20,0)
[/pre]
 
Kyle,


Beautiful formula. =)

I was reading the original post as being somewhat ambiguous, since 4 was the correct answer either way (starting at top, or absolute minimum) and RichBehan didn't define the exact method.


In the end, I still like yours better. Now to go figure out how it works. =)
 
Hi

I also need some help with this and cant seem to get my head around this.

I have a column (A) with multiple rows(A1:A20) containing distances.

I would like to add up the distances in column A till I get to a value of 4000.
Once this value is reached I want to display the word "Joint" in column (B) and restart the count.
 

Attachments

  • Joint Find.xlsx
    31.9 KB · Views: 6
Werner van den Heever said:
Hi

I also need some help with this and cant seem to get my head around this.

I have a column (A) with multiple rows(A1:A20) containing distances.

I would like to add up the distances in column A till I get to a value of 4000.
Once this value is reached I want to display the word "Joint" in column (B) and restart the count.
Click to expand...
Hi ,

Since your problem is a new one , and not a continuation of the original problem posted in this thread , and also since the original thread is quite old , please post your problem as a new thread.

To add to this , you have the text Joint in B5 , but the first 5 values in column A do not add up to more than 4000.

Narayan
 
You must log in or register to reply here.
Back
Top