SUM dynamically till a condition is reached?

[suresh.79]

hi


i want to dynamically SUM cells till a certain condition is reached.

Cell A1 has a value y

Cell B1 has value x1, B2 has value x2, B3 has value x3, B4 has value x4 and so on

I want to write a formula in cell C1 which will keep on summing x1, x2, x3 etc till the sum value is just greater than or equal to y


eg A1 = 100, B1 = 50, B2 = 49, B3 = 5, B4 = 10, B5 = 41 ...

so Cell C1 should give me the sum as (50+49+5) = 104 (ie keep on summing until the sum just becomes greater than or equal to 100)

 

[Clarity]

Hi,


I have written some VBA that will do this for you. You can pick up an example file here:


http://cid-4702efbc51549018.skydrive.live.com/self.aspx/Chandoo%20Forum/VBA%20Dynamic%20range%20based%20on%20value.xls


Or here is the code:


You will need to define A1 as named range limit.


Sub NamedRange()


Dim Rcell, Ccell, rng As Range

Set Rcell = Cells(1, 2)

Set Ccell = Cells(1, 2)

Dim limit As Integer

Dim i As Integer


limit = Range("limit").Value

i = 1


Set rng = Range(Rcell, Ccell)

ActiveWorkbook.Names.Add Name:="Range", RefersTo:=rng


Do While Application.WorksheetFunction.Sum(Range("range")) < limit


Set Rcell = Cells(1 + i, 2)


Set rng = Range(Rcell, Ccell)


ActiveWorkbook.Names.Add Name:="Range", RefersTo:=rng


i = i + 1


Loop


End Sub

 

[Clarity]

Hi,


I have also added a non VBA solution into the file.


The non VBA solution uses a "helper" column to identify the cells to include in the sum and then uses the SUMIF forumula to calculate the total.


Helper formula:

=IF(SUM($B$1:B1)<Limit,1,0)


SUMIF

=SUMIF(C1:C11,1,B1:B11)


Myles