I am adding some functionality to a spreadsheet I've used for ages and it seems that conditional formatting behaves inconsistently with empties and formula blanks. Please see snippets below, followed by checks I've done so far.
Intended action: Both places in the spreadsheet are checking what "band" a value falls into, and the band thresholds are defined in "Movement limits". The spreadsheet was originally made on Excel 2007 if not older but all CF was redone when we upgraded to 2016 which allows "CF by formula referencing other sheets".
Things I've tried so far: 1. ABS() wrapped around the formula in the condition - doesn't make a difference 2. Applying "CF by formula" to multiple cells (you may have spotted in case 1 the cell highlighted is the first in the range, but it's not in case 2) - this seems to work as expected otherwise. So in case 2 above, if I enter values that should get green/orange/red, they do. 3. Manual/direct formatting: There is no fill or text color applied. The number format in both cases is "Custom +#;-#;0". 4. Recalculated. Saved, closed and reopened. 5. The values that I'm comparing with - in both cases, the formula checks against a simple positive number, and a 0 should show green. 6. Excel versions - both cases are in the same .xlsx in Excel 2016.
The main question: why is the blank not formatted in case 1 but formatted in case 2?
What else can I check? I know there are workarounds to get the results I need, but I want the entire spreadsheet to behave the same as it used to for a seamless user experience. The CF on empty P45 is only included for troubleshooting - the final product will not have CF on empties, but I do need it on some formula blanks, and I would like them not to format. So obviously I could add a new rule at the top to leave blanks unformatted and stop if true, but it didn't use to be necessary.