0

I have an array of numbers (a column in excel). I calculated the half of the set's total and now I need the minimum number of set's values that the sum of them would be greater or equal to the half of the total.

Example:

The set: 5, 5, 3, 3, 2, 1, 1, 1, 1
Half of the total is: 11
The least amount of set values that need to be added to get 11 is 3

What is the formula to get '3'?

It's probably something basic but I have not used calculus in a bit hence may have forgotten just forgotten it.

Normally I would use a simple while loop with a sort but I am in excel so I was wondering is there a more elegant solution.

P.S. I have the values sorted in descending order to make things easier.

EDIT: Example

PovilasID
  • 111
  • Not sure this is clear. The minimum number is clearly $1$, as there must be some element in the set with value $≤$ the average. Or did you mean something else? – lulu Jul 30 '19 at 11:56
  • If you have the values in descending order, the last element on the list should be the smallest value, and therefore just taking that, the value should be smaller than the average. Therefore you only need one. Weird question ... Are the values positive and/or negative? – Matti P. Jul 30 '19 at 11:58
  • @lulu I added an example of what I am trying to calculate. Sorry if I have not expressed myself clearly. – PovilasID Jul 30 '19 at 12:04
  • @MattiP. Updated the question and added an example to better explain what I am looking for. It's not the lowest value of the set it's the lowest number of set values that then summed up would be greater or equal to a specific number i.e. the avg Count(5+5+3) -> 3 – PovilasID Jul 30 '19 at 12:07
  • Ah....the earlier version of the question said "lesser or equal to the average" but you meant "greater or equal to half the total"? – lulu Jul 30 '19 at 12:19
  • What is the source of this problem? It looks like the sort of thing that appears in programming challenges...is it? – lulu Jul 30 '19 at 12:22
  • @PovilasID I´ve deleted my answer since it seems that you are not really interested in an answer. If I´m wrong leave a comment. – callculus42 Jul 30 '19 at 15:17
  • @callculus I just have not had the time to test it before accepting it :D I left it to the end of my day now the answers is gone :D – PovilasID Jul 30 '19 at 15:29
  • @PovilasID I´ve undeleted the answer. Feel free to ask a question if something is unclear. – callculus42 Jul 30 '19 at 15:42

2 Answers2

0

Let the values $5, 5, 3, 3, 2, 1, 1, 1, 1$ be input in the cells $A1$ to $A9$

In cell $A10$ you calculate the half of the sum: =sum(A1:A9)/2

In the cells $B1$ to $B9$ you input the values $1$ to $9$

In cell $C1$ you input the following formula: =if(sum(\$A\$1:A1)=>A10, 1,0)

You copy (drag and drop) the formula of $C1$ into cell $C2$ to $C9$

In cell $D1$ you input the formula: =If(C1=1,1,0)

In cell $D2$ your insert the formula =If(And(C1=0,C2=1),1,0)

You copy the formula of $D2$ into cells $D3$ to $D9$

In the cell $E1$ you insert the formula =D1*B1

Finally you copy the formula of $E1$ into cells $E2$ to $E9$

callculus42
  • 30,550
0

Though I don't know exactly what the excel formulas would be, I know this should be possible.

Create a new column that is the running sum so one column has your sorted numbers, and the next is a running column of the sum (there are a couple of ways to do thin, but its not hard)

Column 1: 5,5,3,3,2,1,1,1,1
Column 2: 0,5,10,13,16,18,19,20,21,22

Then, you should be able to do some sort of count with a condition, so count the number of entries in the 2nd column that are less than or equal to your half. You may have to play around a bit to get it so that it always works, but something like that should work.

Jacob R
  • 370