Forum Discussion
Conditional Format for column cells that fit within a threshold
Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?
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."
- I want the numbers that come next in the sequence, in the attached spreadsheet you can see that each $ value has a score attached, I want the next lowest numbers with the highest scores to be highlighted as fitting in the threshold
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.
