Amend a list of numbers until their sum is less than a specified number

[ormy28]

Hi All

I would really appreciate some help with a task I have to complete in Excel.

I have a list of numbers in column A:

4
19
6

Next to them in column B, this list of numbers needs to be replicated, although the sum of the three numbers cannot exceed 20. The first number in the list (in this case 4), needs to be reduced by 1 until the sum of the three numbers reaches 20. After this, if 20 is not reached, then the first number should be zero and the 2nd number (19 in this case), also needs to be reduced until the sum of the three numbers reached 20. And if this target still isn't reached, the first two numbers will be zero, and the third number set to 20. Hope that makes sense!

Therefore, using the example above, the list of numbers in column B should be:

0
14
6

The original list of numbers in column A are generated using a variety of formulas from other tabs, therefore the combination will almost always be different.

I am completely stumped as to how to achieve this using Excel formula alone, and would rather avoid VBA. However, I sense that might be the only way.

Thanks
Mark.

 

[jasonb75]

Like this?

Book2
	A	B	C	D
1			Target	20
2	4	0
3	19	14
4	6	6
5
Sheet7

Cell Formulas
Range	Formula
B2:B4	B2	=MEDIAN(A2,0,$D$1-SUM(A3:A$5))

 

[ormy28]

Wow, that works great. Thank you so much for your help!