Refer to the question under Worksheet function.
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 on how to do it in Excel?
Thanks in advance for any suggestions
Eric
Similar Threads
