Forum Discussion
Conditional Format for column cells that fit within a threshold
N/A
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?
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?
- Yes, I would still want the one. Lets say we only need $20 to reach the threshold. Even if there's 20 cells with $1, with each having a score of 10, but there is a cell with $19 with a score of 50 (and thats the next highest scoring cell that would fit in), I need that one $15 cell highlighted before the 20x$1 cells. I hope this makes sense.
It makes sense (i.e., I understand it)
AND
It doesn't make sense (i.e., it seems to me there still are too many ways to highlight within the bounds you've articulated)
Let me attach a sheet that may demonstrate why I find this next to impossible to write a conditional formatting formula--which is what this is about--that will satisfy.
- In the attached spreadsheet I use a helper column to show the running total down to the point where the specified limit ( 35,906,142)is exceeded.
- Then, off to the side, I calculate the gap--274,656.20--remaining to be filled
- Then I sorted the remaining numbers (and their scores) in descending order by size of the number (not the score).
- The next highest number that fits (regardless of score) is 273,967.1 (So as I understand the rule, this is the next number that fits; there is no number with a higher score where the number is higher than this one that still fits)
- That leaves a new gap of 689.1 and there's only one number left that is below that, clear down at the bottom
- But this highlights the prior question, since there are in fact many combinations of lower numbers with higher scores that could fit, but you say you want the single 15 rather than the several smaller that would come closer.
- In this case--and perhaps this is just a function of the set of numbers--but at this stage of analysis it would appear that the ranking numbers are actually of limited or no value.
I personally can't conceive of a conditional formatting rule that would highlight these numbers below the top set that you've already got under your belt. I hope this illustrates why. Or gives you reason to pause and reconsider the rules you want to enforce.
- Thank you, you've been extremely helpful. I will show this to my team when we troubleshoot our master workbook, and we may come up with better solutions.