Forum Discussion
Conditional Format for column cells that fit within a threshold
While I'm on the subject....your question poses some questions of its own. Presumably those values lower in the sequence are all smaller, which means there could be any number of cells that could be included from that lower range. Do you want the "N largest" of those remaining smaller values, or the largest number of the remaining smaller values? Does it matter? Perhaps the easiest way to get as close as possible to that stated limit would be to take the remaining values from the bottom of the available range. Doing it from the next lowest would still be getting the "bigger pieces."
Hmmm.....when you said at the start that the "numbers are in descending order" I assumed you were referring to the numbers to be added. But it seems you're referring to the scores. The numbers to be added in column B, the scores in column A. And since the numbers to be added are NOT in descending order, but rather somewhat random, that gets quite complicated. It's still no doubt possible, but ... I'm going to have to play with it. In fact, what happens if the next lowest numbers with the highest score still leave a gap that can be filled by yet another round of similar searching.
(This is comparable, you must realize, to the puzzle of fitting stones of various sizes into a jar, with the puzzle always ending with some that are barely larger than grains of sand. So I think it's legitimate to ask how many layers--ranges of "next largest with next highest scores-- do you want to go through?)
Perhaps my friend HansVogelaar will have a dynamic array function at his fingertips.
- My answer would be however many, in descent from highest to lowest SCORE, can still fit within the threshold, even if its just one or two cells. It is a complex system.
- But you're insisting they have to be sequences per the highest score. In other words you seem to be saying that if two with lower scores could fit better than the last one with a higher score, you'd still want the 1. Is that a correct interpretation?
There will come a time in all of this puzzle when you'll be faced with the choice to sacrifice high score for getting closer to the specified limit. And if you want a defensible system--also known as coherent system--you need to specify what really matters in those final stages. Or is there a point at which--say within X%--that you'd say we should stop, even if there might be another grain of sand that could fit in the jar?