Find what values equal a total

[vtpaul]

Say I have a list of various values that add up to 100. I also have
two subtotals, say 35 and 65. Is there a formula or a way to easily
find which of the values would add up 35 and which would add to 65?
Thanks.

 

[olasa]

Insert this formula in column B, and copy down:
=INDEX($A$1:$A$20,MATCH(100,(A1+$A$1:$A$20),0))

The formula will return the first matching value, that adds up to 100.

HTH
Ola Sandström


Note:
Confirm the formula by holding down Ctrl and Shift, then hit Enter
(since it's an Array formula)
Array formulas:
http://www.personal-computer-tutor.com/arrayformulas.htm