How to determine the values?
 

Does anyone have any suggestions on how to determine the values under
following conditions?
Under column A, there is a list of numbers.
Under column C, there is a list of numbers.
There is 145 in cell G1.

Starting to sum from C1 to bottom, if the sum from C1 to C9 < 145 in cell G1
and the sum from C1 to C10 = 145 in cell G1, then return highest number from
A1 to A10 into cell D1.

For the next number
Starting to sum from C2 to bottom, if the sum from C2 to C9 < 145 in cell G1
and the sum from C2 to C10 = 145 in cell G1, then return highest number from
A2 to A10 into cell D2.

Does anyone have any suggestions?
Thanks in advance for any suggestions
Eric

How to determine the values?
 

hi, Eric !

not sure if I'm following you accurately, but you could try as first approach w/ the following (CSE) array formula:

[D1] =max(offset(a1,,,match($g$1,subtotal(9,offset(c1,, ,row(indirect("1:20")),)))+1,))

hth,
hector.

Does anyone have any suggestions on how to determine the values under following conditions?
Under column A, there is a list of numbers.
Under column C, there is a list of numbers.
There is 145 in cell G1.

Starting to sum from C1 to bottom, if the sum from C1 to C9 < 145 in cell G1
and the sum from C1 to C10 = 145 in cell G1, then return highest number from A1 to A10 into cell D1.

For the next number
Starting to sum from C2 to bottom, if the sum from C2 to C9 < 145 in cell G1
and the sum from C2 to C10 = 145 in cell G1, then return highest number from A2 to A10 into cell D2.

Does anyone have any suggestions?
Thanks in advance for any suggestions
Eric

How to determine the values?
 

Under column A, 2,5,3,4,7,8,4,3,8,10
Under column C, 12,5,7,11,13,5,8,23,20,3
There is 50 in cell G1

For the first number in cell D1
Starting to sum from C1 to bottom, if the sum from C1 to C5 <= 50 in cell G1
and the sum from C1 to C6 50 in cell G1, then return highest number from
A1 to A5 into cell D1, which is 7

For the next number in cell D2
Starting to sum from C2 to bottom, if the sum from C2 to C7 <= 50 in cell G1
and the sum from C2 to C8 50 in cell G1, then return highest number from
A2 to A7 into cell D2, which is 8
....

Thank you very much for any suggestions
Eric


"Héctor Miguel" wrote:

hi, Eric !

not sure if I'm following you accurately, but you could try as first approach w/ the following (CSE) array formula:

[D1] =max(offset(a1,,,match($g$1,subtotal(9,offset(c1,, ,row(indirect("1:20")),)))+1,))

hth,
hector.

Does anyone have any suggestions on how to determine the values under following conditions?
Under column A, there is a list of numbers.
Under column C, there is a list of numbers.
There is 145 in cell G1.

Starting to sum from C1 to bottom, if the sum from C1 to C9 < 145 in cell G1
and the sum from C1 to C10 = 145 in cell G1, then return highest number from A1 to A10 into cell D1.

For the next number
Starting to sum from C2 to bottom, if the sum from C2 to C9 < 145 in cell G1
and the sum from C2 to C10 = 145 in cell G1, then return highest number from A2 to A10 into cell D2.

Does anyone have any suggestions?
Thanks in advance for any suggestions
Eric

How to determine the values?
 

hi, Eric !

try eliminating the "plus one" from the final part of the formula:

from: =max(offset(a1,,,match($g$1,subtotal(9,offset(c1,, ,row(indirect("1:20")),)))+1,))
to: =max(offset(a1,,,match($g$1,subtotal(9,offset(c1,, ,row(indirect("1:20")),))),))

hth,
hector.

Under column A, 2,5,3,4,7,8,4,3,8,10
Under column C, 12,5,7,11,13,5,8,23,20,3
There is 50 in cell G1

For the first number in cell D1
Starting to sum from C1 to bottom, if the sum from C1 to C5 <= 50 in cell G1
and the sum from C1 to C6 50 in cell G1, then return highest number from A1 to A5 into cell D1, which is 7

For the next number in cell D2
Starting to sum from C2 to bottom, if the sum from C2 to C7 <= 50 in cell G1
and the sum from C2 to C8 50 in cell G1, then return highest number from A2 to A7 into cell D2, which is 8

not sure if I'm following you accurately, but you could try as first approach w/ the following (CSE) array formula:

[D1] =max(offset(a1,,,match($g$1,subtotal(9,offset(c1,, ,row(indirect("1:20")),)))+1,))

Does anyone have any suggestions on how to determine the values under following conditions?
Under column A, there is a list of numbers.
Under column C, there is a list of numbers.
There is 145 in cell G1.

Starting to sum from C1 to bottom, if the sum from C1 to C9 < 145 in cell G1
and the sum from C1 to C10 = 145 in cell G1, then return highest number from A1 to A10 into cell D1.

For the next number
Starting to sum from C2 to bottom, if the sum from C2 to C9 < 145 in cell G1
and the sum from C2 to C10 = 145 in cell G1, then return highest number from A2 to A10 into cell D2.

How to determine the values?
 

Or maybe this formula filled down from D1 (entered normally):

=LOOKUP(G$1,SUBTOTAL({9,4},OFFSET(C1,,{0,-2},ROW(C1:C$10)-ROW()+1)))


"Eric" wrote:

Under column A, 2,5,3,4,7,8,4,3,8,10
Under column C, 12,5,7,11,13,5,8,23,20,3
There is 50 in cell G1

For the first number in cell D1
Starting to sum from C1 to bottom, if the sum from C1 to C5 <= 50 in cell G1
and the sum from C1 to C6 50 in cell G1, then return highest number from
A1 to A5 into cell D1, which is 7

For the next number in cell D2
Starting to sum from C2 to bottom, if the sum from C2 to C7 <= 50 in cell G1
and the sum from C2 to C8 50 in cell G1, then return highest number from
A2 to A7 into cell D2, which is 8
...

Thank you very much for any suggestions
Eric


"Héctor Miguel" wrote:

hi, Eric !

not sure if I'm following you accurately, but you could try as first approach w/ the following (CSE) array formula:

[D1] =max(offset(a1,,,match($g$1,subtotal(9,offset(c1,, ,row(indirect("1:20")),)))+1,))

hth,
hector.

Does anyone have any suggestions on how to determine the values under following conditions?
Under column A, there is a list of numbers.
Under column C, there is a list of numbers.
There is 145 in cell G1.

Starting to sum from C1 to bottom, if the sum from C1 to C9 < 145 in cell G1
and the sum from C1 to C10 = 145 in cell G1, then return highest number from A1 to A10 into cell D1.

For the next number
Starting to sum from C2 to bottom, if the sum from C2 to C9 < 145 in cell G1
and the sum from C2 to C10 = 145 in cell G1, then return highest number from A2 to A10 into cell D2.

Does anyone have any suggestions?
Thanks in advance for any suggestions
Eric

How to determine the values?
 

Thank everyone very much for suggestions

Could you please tell me how to change the code in order to determine the
minimum number under column A instead of maximum one?
Regards
Eric

"Lori" wrote:

Or maybe this formula filled down from D1 (entered normally):

=LOOKUP(G$1,SUBTOTAL({9,4},OFFSET(C1,,{0,-2},ROW(C1:C$10)-ROW()+1)))


"Eric" wrote:

Under column A, 2,5,3,4,7,8,4,3,8,10
Under column C, 12,5,7,11,13,5,8,23,20,3
There is 50 in cell G1

For the first number in cell D1
Starting to sum from C1 to bottom, if the sum from C1 to C5 <= 50 in cell G1
and the sum from C1 to C6 50 in cell G1, then return highest number from
A1 to A5 into cell D1, which is 7

For the next number in cell D2
Starting to sum from C2 to bottom, if the sum from C2 to C7 <= 50 in cell G1
and the sum from C2 to C8 50 in cell G1, then return highest number from
A2 to A7 into cell D2, which is 8
...

Thank you very much for any suggestions
Eric


"Héctor Miguel" wrote:

hi, Eric !

not sure if I'm following you accurately, but you could try as first approach w/ the following (CSE) array formula:

[D1] =max(offset(a1,,,match($g$1,subtotal(9,offset(c1,, ,row(indirect("1:20")),)))+1,))

hth,
hector.

Does anyone have any suggestions on how to determine the values under following conditions?
Under column A, there is a list of numbers.
Under column C, there is a list of numbers.
There is 145 in cell G1.

Starting to sum from C1 to bottom, if the sum from C1 to C9 < 145 in cell G1
and the sum from C1 to C10 = 145 in cell G1, then return highest number from A1 to A10 into cell D1.

For the next number
Starting to sum from C2 to bottom, if the sum from C2 to C9 < 145 in cell G1
and the sum from C2 to C10 = 145 in cell G1, then return highest number from A2 to A10 into cell D2.

Does anyone have any suggestions?
Thanks in advance for any suggestions
Eric

How to determine the values?
 

Hi Eric, for the min value change the 4 to 5 - see help on the subtotal
function.

The logic of the formula is to create a two column array of running sums and
associated max values from which to lookup the threshold value.

"Eric" wrote:

Thank everyone very much for suggestions

Could you please tell me how to change the code in order to determine the
minimum number under column A instead of maximum one?
Regards
Eric

"Lori" wrote:

Or maybe this formula filled down from D1 (entered normally):

=LOOKUP(G$1,SUBTOTAL({9,4},OFFSET(C1,,{0,-2},ROW(C1:C$10)-ROW()+1)))


"Eric" wrote:

Under column A, 2,5,3,4,7,8,4,3,8,10
Under column C, 12,5,7,11,13,5,8,23,20,3
There is 50 in cell G1

For the first number in cell D1
Starting to sum from C1 to bottom, if the sum from C1 to C5 <= 50 in cell G1
and the sum from C1 to C6 50 in cell G1, then return highest number from
A1 to A5 into cell D1, which is 7

For the next number in cell D2
Starting to sum from C2 to bottom, if the sum from C2 to C7 <= 50 in cell G1
and the sum from C2 to C8 50 in cell G1, then return highest number from
A2 to A7 into cell D2, which is 8
...

Thank you very much for any suggestions
Eric


"Héctor Miguel" wrote:

hi, Eric !

not sure if I'm following you accurately, but you could try as first approach w/ the following (CSE) array formula:

[D1] =max(offset(a1,,,match($g$1,subtotal(9,offset(c1,, ,row(indirect("1:20")),)))+1,))

hth,
hector.

Does anyone have any suggestions on how to determine the values under following conditions?
Under column A, there is a list of numbers.
Under column C, there is a list of numbers.
There is 145 in cell G1.

Starting to sum from C1 to bottom, if the sum from C1 to C9 < 145 in cell G1
and the sum from C1 to C10 = 145 in cell G1, then return highest number from A1 to A10 into cell D1.

For the next number
Starting to sum from C2 to bottom, if the sum from C2 to C9 < 145 in cell G1
and the sum from C2 to C10 = 145 in cell G1, then return highest number from A2 to A10 into cell D2.

Does anyone have any suggestions?
Thanks in advance for any suggestions
Eric