Running Sum across columns but only to a certain point

[Steven Cheng]

I have a some values in three columns that basically snake from Day 1 to Day
45 and I will like to add them up starting from Day 1 going to a particular
Day that adds up to a value in another field.

Day1 $100,000.00 Day21 Day41
Day2 Day22 Day42
Day3 $5,000.00 Day23 Day43
Day4 $(5,000.00) Day24 Day44
Day5 $57,000.00 Day25 Day45
Day6 $80,000.00 Day26 Day46
Day7 $- Day27 Day47
Day8 $46,000.00 Day28 Day48
Day9 $- Day29 Day49
Day10 $- Day30 Day50
Day11 $80,000.00 Day31 Day51
Day12 $90,000.00 Day32 Day52
Day13 $10,000.00 Day33 Day53
Day14 Day34 Day54
Day15 Day35 Day55
Day16 Day36 Day56
Day17 Day37 Day57
Day18 Day38 Day58
Day19 Day39 Day59
Day20 Day40 Day60
what I want to return is the day in which it finally meets that particular
value.

 

[Domenic]

Try the following, which uses a defined name and three helper cells...

Assumptions:

A2:F21 contains the data

Columns A, C, and E contain the day

Columns B, D, and F contain the corresponding values

H2 contains the value of interest

Defined name:

Insert > Name > Define

Name: BigNum

Refers to:

=9.99999999999999E+307

Click Ok

Formulas:

I2:

=IF(N(H2),MATCH(TRUE,SUM(B2:B21)+SUM(D221)+SUBTOTAL(9,OFFSET(F2:F21,,,R
OW(F2:F21)-ROW(F2)+1))>=H2,0),"")

....confirmed with CONTROL+SHIFT+ENTER

J2:

=IF(N(H2),MATCH(TRUE,SUM(B2:B21)+SUBTOTAL(9,OFFSET(D221,,,ROW(D221)-R
OW(D2)+1))>=H2,0),"")

....confirmed with CONTROL+SHIFT+ENTER

K2:

=IF(N(H2),MATCH(TRUE,SUBTOTAL(9,OFFSET(B2:B21,,,ROW(B2:B21)-ROW(B2)+1))>=
H2,0),"")

....confirmed with CONTROL+SHIFT+ENTER

L2:

=IF(N(H2),INDEX((A2:A21,C2:C21,E2:E21),LOOKUP(BigNum,I2:K2),,3-MATCH(BigN
um,I2:K2)+1),"")

....confirmed with just ENTER

Hope this helps!

 

[Steven Cheng]

Thanks Domenic....looks like a complicated formula....maybe I should have
just created a function for this instead...

 

[Domenic]

If you change the layout of your spreadsheet so that only two columns
contain the data, where Column A contains the date, and Column B
contains the corresponding values, you could use the following formula
instead...

=IF(N(H2),INDEX(A2:A21,MATCH(TRUE,SUBTOTAL(9,OFFSET(B2:B21,,,ROW(B2:B21)-
ROW(B2)+1))>=H2,0)),"")

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!