How to determine the max. value?

[Eric]

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

 

[T. Valko]

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)

 

[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

 

[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]

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.