I have a series of unsorted values and I need to identify the top values that sum to at least 50. I want to then use this in a formula somehow to count the number of associated "yes" and "no" values.
Here is some example data:
In case of ties I want to include all the duplicate values in my final result. So in this example the top values are 40, 10 and 10 and my final result would be 1 yes and 2 no's.
I know I can use the LARGE function to get the nth top value so I tried fetching the top values in a separate range and then calculating the running sum:
I could then create a formula to find the row at which the sum reaches >= 50. Now the problem is how to get the yes/no values associated with the numbers and how to deal with ties.
Any ideas would be appreciated! Even better would be a simpler formula without all the intermediate steps.