Finding largest total of ANY two values in a data set

[H3dgehog]

Good morning,

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

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

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

3) Find the highest sum total of ANY four values, returning the tota
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

 

[joeu2004]

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))

 

[Ron Rosenfeld]

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))

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}))

 

[joeu2004]

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.

 

[Ron Rosenfeld]

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.

 

[Ron Rosenfeld]

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.

 

[Ron Rosenfeld]

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.