0

I have a dataset in the form of 8 columns (separated based on additional variables that aren't directly relevant here) that vary in size each week and each data point is an effectively random positive integer from 0-100 inclusive.

I am trying to design a function to output the minimum number of data points that sum to at least 50% of the total sum of the column - so if a column happened to be the integers 0-100 then the sum would be 5050 and the desired output would be 30. Additionally as I need to repeat this function 8 separate times I need a solution that is compact - in addition to working with the variable data size.

So far I have tried various SUMIFs and COUNTIFs, but trying to set the range using the LARGE function became recursive and spat out error messages. I also tried a few iterative solutions at first - like setting the minimum range by using the total sum divided by the largest value and increasing the range by 1 until it hit the 50% threshold, but given the dataset could run into the thousands this would not meet my compactness requirement.

2
  • You're talking about 8 columns, would you need the result per column? It would be more clear if you add some dummy data and expected result(s) with that. Also not sure why you say 26 would be equal to 50% of 5050.
    – P.b
    Commented Nov 27, 2023 at 14:11
  • @P.b the actual data is arbitrary and was irrelevant to my ability to create a compact solution. I apologise if the parameters for the data were unclear to you. Commented Nov 27, 2023 at 14:27

2 Answers 2

2

I understand that the total number of columns doesn't matter as calculations are carried out for each column independently.
If you have a new version of Excel you can use a formula as in the screenshot below.
Random numbers 0..100 are in the column A. Number of data doesn't matter. The result is in the cell F7.
SUM_LARGE

0

Alternatively:

=LET(a,TOCOL(A:A,1),XMATCH(SUM(a)/2,SCAN(,SORT(a),LAMBDA(b,c,b+c)),1))

Where a is declared to only keep the non-empty cell values in column A (you could also use TOCOL(A:A/ISNUMBER(A:A),2) to keep number values only, but it works without it as well)).

Then we use XMATCH to find the first value equal to or greater than the sum of a divided by 2 (=50%) in the SCAN of the SUM of the sorted values of a from smallest to largest value (if you included text, these will result in errors, but are in the end of the error, prior to the match being found).

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .