Sum Range of Cells to a Target Value

[davo-1b]

I need to sum a range of cells until a taget value is reached or just
under target value is reached. Once I have reached the target, I
would like to get the cell address of tthe cell directly above that
point.

Simplified Example:
A1:F1 contains Jan, Feb, Mar, Apr, May, Jun
A2:F2 contains 2, 4, 7, 3, 1, 5

A5 contains the target, 16

I need the output to indicate "Apr" or the cell address "D1"

I can do this in VBA; however in this instance, I would prefer to use
cell-based formulas. I have tried to use array formulas, but with no
success. Any ideas?

Any help would be appreciated.

 

[Guest]

very easy with a helper row.

in A3 enter
=A2
in B3 enter
=IF(A3+B2<=$A$5,A3+B2,-99999) and copy across

to see:

jan feb mar apr may jun
2 4 7 3 1 5
2 6 13 16 -99999 -99994


in any cell enter
=OFFSET(A1,,MATCH(MAX(3:3),3:3,1)-1)

 

[davo-1b]

Many Thanks. I see how this works in the example I provided. If I
change the target to say, 13, then I get "June" as a response, not
"Mar". This appears to be due to the subtraction of 1 from the column
argument of the OFFSET function, correct? Is there a way to make it
solvable for whatever the target is (within reason)?

 

[Guest]

The subtraction of one is O.K. My error was using 1 as the last argument of
MATCH.

Here is the corrected formula:

=OFFSET(A1,,MATCH(MAX(3:3),3:3,0)-1)

sorry

 

[davo-1b]

The subtraction of one is O.K. My error was using 1 as the last argument of
MATCH.

Here is the corrected formula:

=OFFSET(A1,,MATCH(MAX(3:3),3:3,0)-1)

sorry

Not a problem. I appreciate the assistance.

 

[Guest]

In another post, Harlan constructed a formula which returns an array of
subtotals - which I believe can be used for your purposes.

=INDEX(A1:F1,MATCH(A5,MMULT(--(TRANSPOSE(COLUMN(A2:F2))>=COLUMN(A2:F2)),TRANSPOSE(A2:F2)),1))

array entered.