3

I have a Google Sheet with two columns. Column A contains a list of names and column B a list of numerical values associated with those names.

I need to find the minimum number of cells in column B whose sum is greater than or equal to at least 75% of the sum of all the values in column B.

For those cells I need to return:

  1. The cell count
  2. The associated names from Column A

Sample Data

A B                                 
Name-01 10
Name-02 20
Name-03 15
Name-04 40
Name-05 10
Name-06    6

In the sample data above the column B total is 100 so the minimum number of cells needed to achieve at least 75% of that is 3:

Count: 3                                 
Name-02 20
Name-03 15
Name-04 40

If possible no array formulas.

1 Answer 1

2

To get the names in column A2:A that represent at least 75% of the sum of the values in column B2:B, filter by the running total. To guarantee that the result always matches or exceeds the target, you have to subtract the current value from the tally, like this:

=let( 
  data, sort(A2:B, 2, false), 
  names, choosecols(data, 1), 
  values, choosecols(data, 2), 
  target, 75% * sum(values), 
  tally, scan(0, values, lambda(a, c, a + c)), 
  filter(names, tally - values < target) 
)

With data like this:

name value
alice 10
bob 20
charly 15
dorothy 39
eve 10
frank 6

...the formula will get results like this:

name value
david 39
bob 20
charly 15
alice 10

(note: using filter(data instead of filter(names for illustration)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.