Sum Range of Cells to a Target Value
 

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.

Sum Range of Cells to a Target Value
 

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)

--
Gary''s Student - gsnu200729


" wrote:

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.

Sum Range of Cells to a Target Value
 

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)?

Sum Range of Cells to a Target Value
 

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
--
Gary''s Student - gsnu200729


" wrote:


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)?

Sum Range of Cells to a Target Value
 

On Jun 14, 2:14 pm, Gary''s Student
wrote:
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
--
Gary''s Student - gsnu200729

Not a problem. I appreciate the assistance.

Sum Range of Cells to a Target Value
 

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)),TRANSPOS E(A2:F2)),1))

array entered.


" wrote:

On Jun 14, 2:14 pm, Gary''s Student
wrote:
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
--
Gary''s Student - gsnu200729

Not a problem. I appreciate the assistance.