Good morning,

I have a list of percentage values in a column (totalling 100%). I have to run 3 specific tests against this data set:

1) Find the highest sum total of ANY two values, returning the total and the two values that make up the total.

2) Find the highest sum total of ANY three values, returning the total and the three values that make up the total.

3) Find the highest sum total of ANY four values, returning the total and the four values that make up the total.

Is there a specific function to look at data sets in this way?

Many thanks in advance from a first time poster!

"H3dgehog" wrote:
I have a list of percentage values in a column (totalling 100%).
I have to run 3 specific tests against this data set:
1) Find the highest sum total of ANY two values, returning the
total and the two values that make up the total.
2) Find the highest sum total of ANY three values, returning the
total and the three values that make up the total.
3) Find the highest sum total of ANY four values, returning the
total and the four values that make up the total.
Is there a specific function to look at data sets in this way?

No. But there is a function that will tell you how many sums you must look
at: =COMBIN(n,k), where n is the total number of values in the list (you
might use COUNT(A1:A100), if you don't want to count them manually), and k
is the number of values to be summed (2, 3 and 4).

For example, if you have a list of 10 percentages, you would need to form
410 sums to find the largest of the sum of 4 values.

The general solution is best implemented using VBA.

However, if you do not want a general solution and your list is of
percentages is very small, you could use the MAX function in which you
enumerate all of the possible sums.

For example, if you have 5 percentages in A1:A5, the largest sum of any 4
is:

=MAX(SUM(A1:A4),SUM(A1:A3,A5),A1+A2+A4+A5,A1+A3+A4 +A5,SUM(A2:A5))

On Thu, 12 Jul 2012 07:50:38 -0700, "joeu2004" wrote:

"H3dgehog" wrote:
I have a list of percentage values in a column (totalling 100%).
I have to run 3 specific tests against this data set:
1) Find the highest sum total of ANY two values, returning the
total and the two values that make up the total.
2) Find the highest sum total of ANY three values, returning the
total and the three values that make up the total.
3) Find the highest sum total of ANY four values, returning the
total and the four values that make up the total.
Is there a specific function to look at data sets in this way?

No. But there is a function that will tell you how many sums you must look
at: =COMBIN(n,k), where n is the total number of values in the list (you
might use COUNT(A1:A100), if you don't want to count them manually), and k
is the number of values to be summed (2, 3 and 4).

For example, if you have a list of 10 percentages, you would need to form
410 sums to find the largest of the sum of 4 values.

The general solution is best implemented using VBA.

However, if you do not want a general solution and your list is of
percentages is very small, you could use the MAX function in which you
enumerate all of the possible sums.

For example, if you have 5 percentages in A1:A5, the largest sum of any 4
is:

=MAX(SUM(A1:A4),SUM(A1:A3,A5),A1+A2+A4+A5,A1+A3+A 4+A5,SUM(A2:A5))

What am I missing? Wouldn't the largest sum be the sum of the largest?

In other words, why not just =sum(large(data,{1,2,3,4}))

"Ron Rosenfeld" wrote:
On Thu, 12 Jul 2012 07:50:38 -0700, "joeu2004" wrote:
For example, if you have 5 percentages in A1:A5, the largest sum
of any 4 is:
=MAX(SUM(A1:A4),SUM(A1:A3,A5),A1+A2+A4+A5,A1+A3+A4 +A5,SUM(A2:A5))

What am I missing? Wouldn't the largest sum be the sum of the largest?
In other words, why not just =sum(large(data,{1,2,3,4}))

Well, duh! I believe you are right. That's the trouble with homework
assignments: they are often worded in such a way that if we don't think it
through, we find the hardest solution. I guess you get the A+ for this
problem. :-)

On Thu, 12 Jul 2012 09:28:02 -0700, "joeu2004" wrote:

Well, duh! I believe you are right. That's the trouble with homework
assignments: they are often worded in such a way that if we don't think it
through, we find the hardest solution. I guess you get the A+ for this
problem. :-)

Maybe there's an occasional advantage in not having formal training in math or statistics -- just a few introductory college level courses.

But it sure isn't unusual for me to NOT interpret a question clearly, or properly, or to miss some nuance; hence my inquiry.

On Thu, 12 Jul 2012 12:03:06 +0000, H3dgehog wrote:


Good morning,

I have a list of percentage values in a column (totalling 100%). I have
to run 3 specific tests against this data set:

1) Find the highest sum total of ANY two values, returning the total and
the two values that make up the total.

2) Find the highest sum total of ANY three values, returning the total
and the three values that make up the total.

3) Find the highest sum total of ANY four values, returning the total
and the four values that make up the total.

Is there a specific function to look at data sets in this way?

Many thanks in advance from a first time poster!

If I understand your question properly, I believe the "highest sum total" of ANY n values, would be the same as the sum of the n highest values.

=sum(large(data_range,{1,2, ... n})) would be a general solution

Two Highest:
=sum(large(data_range,{1,2}))

Three highest:
=sum(large(data_range,{1,2,3}))

and so forth.

On Thu, 12 Jul 2012 09:28:02 -0700, "joeu2004" wrote:

"Ron Rosenfeld" wrote:
On Thu, 12 Jul 2012 07:50:38 -0700, "joeu2004" wrote:
For example, if you have 5 percentages in A1:A5, the largest sum
of any 4 is:
=MAX(SUM(A1:A4),SUM(A1:A3,A5),A1+A2+A4+A5,A1+A3+A4 +A5,SUM(A2:A5))

What am I missing? Wouldn't the largest sum be the sum of the largest?
In other words, why not just =sum(large(data,{1,2,3,4}))

Well, duh! I believe you are right. That's the trouble with homework
assignments: they are often worded in such a way that if we don't think it
through, we find the hardest solution. I guess you get the A+ for this
problem. :-)

I'll post something then for the OP. Thanks.

Hi Guy's - Many thanks for your help with this - I agree, this was all about the approach and not over-complicating. The formula works great and I have been able to incorporate this into a simple VB process to flag values that fail the test.

FYI - Not a homework assignment - This relates to the monitoring of concentration limits for investments in US Life Insurance Regulations!