There are a list of numbers under column A and B, and there is a given number
in cell C1, such as 100

A B
2 4
4 13
7 45
5 33
9 23
13 40
23 12

For the first number in cell D1, starting from cell B1 counting downward in
order to determine the sum of number, which is less than / equal to 100, but
if adding the next number, then it will more than 100. In this case for the
first number in cell D1,
4+13+45+33 = 95, but adding the next number 23, then it will be
4+13+45+33+23 = 118. After that, I would like to determine the max. values
under column A: 2,4,7,5, but not including 9, and it should return 7 in cell
D1.

For the second number in cell D2, starting from cell B2 counting downward in
order to determine the sum of number, which is less than / equal to 100, but
if adding the next number, then it will more than 100. In this case for the
second number in cell D2,
13+45+33 = 91, but adding the next number 23, then it will be
13+45+33+23 = 114. After that, I would like to determine the max. values
under column A: 4,7,5, but not including 9, and it should return 7 in cell D2.

For the third number in cell D3, starting from cell B3 counting downward in
order to determine the sum of number, which is less than / equal to 100, but
if adding the next number, then it will more than 100. In this case for the
third number in cell D3,
45+33 = 78, but adding the next number 23, then it will be
45+33+23 = 101. After that, I would like to determine the max. values under
column A: 7,5, but not including 9, and it should return 7 in cell D3

For the fourth number in cell D4, starting from cell B4 counting downward in
order to determine the sum of number, which is less than / equal to 100, but
if adding the next number, then it will more than 100. In this case for the
third number in cell D4,
33+23+40 = 96, but adding the next number 12, then it will be
33+23+40+12 = 108. After that, I would like to determine the max. values
under column A: 5,9,13, but not including 23, and it should return 13 in cell
D4

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

Not extensively tested and no error checking, but this returns the results
you describe.

Array entered** :

=MAX(A1:INDEX(A1:A$7,MATCH(TRUE,SUBTOTAL(9,OFFSET( B1:B$7,,,ROW(B1:B$7)-MIN(ROW(B1:B$7))+1,1))C$1,0)-1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Eric" wrote in message
...
There are a list of numbers under column A and B, and there is a given
number
in cell C1, such as 100

A B
2 4
4 13
7 45
5 33
9 23
13 40
23 12

For the first number in cell D1, starting from cell B1 counting downward
in
order to determine the sum of number, which is less than / equal to 100,
but
if adding the next number, then it will more than 100. In this case for
the
first number in cell D1,
4+13+45+33 = 95, but adding the next number 23, then it will be
4+13+45+33+23 = 118. After that, I would like to determine the max. values
under column A: 2,4,7,5, but not including 9, and it should return 7 in
cell
D1.

For the second number in cell D2, starting from cell B2 counting downward
in
order to determine the sum of number, which is less than / equal to 100,
but
if adding the next number, then it will more than 100. In this case for
the
second number in cell D2,
13+45+33 = 91, but adding the next number 23, then it will be
13+45+33+23 = 114. After that, I would like to determine the max. values
under column A: 4,7,5, but not including 9, and it should return 7 in cell
D2.

For the third number in cell D3, starting from cell B3 counting downward
in
order to determine the sum of number, which is less than / equal to 100,
but
if adding the next number, then it will more than 100. In this case for
the
third number in cell D3,
45+33 = 78, but adding the next number 23, then it will be
45+33+23 = 101. After that, I would like to determine the max. values
under
column A: 7,5, but not including 9, and it should return 7 in cell D3

For the fourth number in cell D4, starting from cell B4 counting downward
in
order to determine the sum of number, which is less than / equal to 100,
but
if adding the next number, then it will more than 100. In this case for
the
third number in cell D4,
33+23+40 = 96, but adding the next number 12, then it will be
33+23+40+12 = 108. After that, I would like to determine the max. values
under column A: 5,9,13, but not including 23, and it should return 13 in
cell
D4

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

Thank you very much for your suggestions
For the first 4 numbers, it is fine, but for the fifth number, it shows
error #N/A. Do you have any suggestions on how to fix it?
Thank you very much for any suggestions
Eric

"T. Valko" wrote:

Not extensively tested and no error checking, but this returns the results
you describe.

Array entered** :

=MAX(A1:INDEX(A1:A$7,MATCH(TRUE,SUBTOTAL(9,OFFSET( B1:B$7,,,ROW(B1:B$7)-MIN(ROW(B1:B$7))+1,1))C$1,0)-1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Eric" wrote in message
...
There are a list of numbers under column A and B, and there is a given
number
in cell C1, such as 100

A B
2 4
4 13
7 45
5 33
9 23
13 40
23 12

For the first number in cell D1, starting from cell B1 counting downward
in
order to determine the sum of number, which is less than / equal to 100,
but
if adding the next number, then it will more than 100. In this case for
the
first number in cell D1,
4+13+45+33 = 95, but adding the next number 23, then it will be
4+13+45+33+23 = 118. After that, I would like to determine the max. values
under column A: 2,4,7,5, but not including 9, and it should return 7 in
cell
D1.

For the second number in cell D2, starting from cell B2 counting downward
in
order to determine the sum of number, which is less than / equal to 100,
but
if adding the next number, then it will more than 100. In this case for
the
second number in cell D2,
13+45+33 = 91, but adding the next number 23, then it will be
13+45+33+23 = 114. After that, I would like to determine the max. values
under column A: 4,7,5, but not including 9, and it should return 7 in cell
D2.

For the third number in cell D3, starting from cell B3 counting downward
in
order to determine the sum of number, which is less than / equal to 100,
but
if adding the next number, then it will more than 100. In this case for
the
third number in cell D3,
45+33 = 78, but adding the next number 23, then it will be
45+33+23 = 101. After that, I would like to determine the max. values
under
column A: 7,5, but not including 9, and it should return 7 in cell D3

For the fourth number in cell D4, starting from cell B4 counting downward
in
order to determine the sum of number, which is less than / equal to 100,
but
if adding the next number, then it will more than 100. In this case for
the
third number in cell D4,
33+23+40 = 96, but adding the next number 12, then it will be
33+23+40+12 = 108. After that, I would like to determine the max. values
under column A: 5,9,13, but not including 23, and it should return 13 in
cell
D4

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

It works now, please ignore previous post.
I would like to know if I want to determine the min. value based on the same
condition? what should I change about the given coding?
Could you please give me any suggestions?
Thank you very much for any suggestions
Eric

"T. Valko" wrote:

Not extensively tested and no error checking, but this returns the results
you describe.

Array entered** :

=MAX(A1:INDEX(A1:A$7,MATCH(TRUE,SUBTOTAL(9,OFFSET( B1:B$7,,,ROW(B1:B$7)-MIN(ROW(B1:B$7))+1,1))C$1,0)-1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Eric" wrote in message
...
There are a list of numbers under column A and B, and there is a given
number
in cell C1, such as 100

A B
2 4
4 13
7 45
5 33
9 23
13 40
23 12

For the first number in cell D1, starting from cell B1 counting downward
in
order to determine the sum of number, which is less than / equal to 100,
but
if adding the next number, then it will more than 100. In this case for
the
first number in cell D1,
4+13+45+33 = 95, but adding the next number 23, then it will be
4+13+45+33+23 = 118. After that, I would like to determine the max. values
under column A: 2,4,7,5, but not including 9, and it should return 7 in
cell
D1.

For the second number in cell D2, starting from cell B2 counting downward
in
order to determine the sum of number, which is less than / equal to 100,
but
if adding the next number, then it will more than 100. In this case for
the
second number in cell D2,
13+45+33 = 91, but adding the next number 23, then it will be
13+45+33+23 = 114. After that, I would like to determine the max. values
under column A: 4,7,5, but not including 9, and it should return 7 in cell
D2.

For the third number in cell D3, starting from cell B3 counting downward
in
order to determine the sum of number, which is less than / equal to 100,
but
if adding the next number, then it will more than 100. In this case for
the
third number in cell D3,
45+33 = 78, but adding the next number 23, then it will be
45+33+23 = 101. After that, I would like to determine the max. values
under
column A: 7,5, but not including 9, and it should return 7 in cell D3

For the fourth number in cell D4, starting from cell B4 counting downward
in
order to determine the sum of number, which is less than / equal to 100,
but
if adding the next number, then it will more than 100. In this case for
the
third number in cell D4,
33+23+40 = 96, but adding the next number 12, then it will be
33+23+40+12 = 108. After that, I would like to determine the max. values
under column A: 5,9,13, but not including 23, and it should return 13 in
cell
D4

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

I would like ... the min. value based on the same condition?

Just replace MAX with MIN.

Don't forget, it needs to be array entered.

--
Biff
Microsoft Excel MVP


"Eric" wrote in message
...
It works now, please ignore previous post.
I would like to know if I want to determine the min. value based on the
same
condition? what should I change about the given coding?
Could you please give me any suggestions?
Thank you very much for any suggestions
Eric

"T. Valko" wrote:

Not extensively tested and no error checking, but this returns the
results
you describe.

Array entered** :

=MAX(A1:INDEX(A1:A$7,MATCH(TRUE,SUBTOTAL(9,OFFSET( B1:B$7,,,ROW(B1:B$7)-MIN(ROW(B1:B$7))+1,1))C$1,0)-1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Eric" wrote in message
...
There are a list of numbers under column A and B, and there is a given
number
in cell C1, such as 100

A B
2 4
4 13
7 45
5 33
9 23
13 40
23 12

For the first number in cell D1, starting from cell B1 counting
downward
in
order to determine the sum of number, which is less than / equal to
100,
but
if adding the next number, then it will more than 100. In this case for
the
first number in cell D1,
4+13+45+33 = 95, but adding the next number 23, then it will be
4+13+45+33+23 = 118. After that, I would like to determine the max.
values
under column A: 2,4,7,5, but not including 9, and it should return 7 in
cell
D1.

For the second number in cell D2, starting from cell B2 counting
downward
in
order to determine the sum of number, which is less than / equal to
100,
but
if adding the next number, then it will more than 100. In this case for
the
second number in cell D2,
13+45+33 = 91, but adding the next number 23, then it will be
13+45+33+23 = 114. After that, I would like to determine the max.
values
under column A: 4,7,5, but not including 9, and it should return 7 in
cell
D2.

For the third number in cell D3, starting from cell B3 counting
downward
in
order to determine the sum of number, which is less than / equal to
100,
but
if adding the next number, then it will more than 100. In this case for
the
third number in cell D3,
45+33 = 78, but adding the next number 23, then it will be
45+33+23 = 101. After that, I would like to determine the max. values
under
column A: 7,5, but not including 9, and it should return 7 in cell D3

For the fourth number in cell D4, starting from cell B4 counting
downward
in
order to determine the sum of number, which is less than / equal to
100,
but
if adding the next number, then it will more than 100. In this case for
the
third number in cell D4,
33+23+40 = 96, but adding the next number 12, then it will be
33+23+40+12 = 108. After that, I would like to determine the max.
values
under column A: 5,9,13, but not including 23, and it should return 13
in
cell
D4

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