How to determine the values?

E

Eric

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
 
H

Héctor Miguel

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.
 
E

Eric

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 said:
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
Click to expand...
Click to expand...
 
H

Héctor Miguel

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.
Click to expand...
Click to expand...
Click to expand...
 
L

Lori

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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
E

Eric

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 said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
L

Lori

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 said:
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 said:
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 said:
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


:

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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to determine the max. value? 4
Sum of an array 1
Sum of an array 4
How to determine the value? 8
use cell text in lookup function 3
Combining SUMIF and SUBTOTAL 4
formula to make the value in a cell, to a row in a sum function 5
Complex decisions, maybe? 7

Top