Hi there,
I needed to highlight the same positive and negative figures in column S, if columns C and F were the same, aka:
Apple Orange -1
Apple Orange 1
Apple Banana 1
Apple orange 1
Apple orange -1
When I created the formula below to find true/false values on a separate column, it worked like a charm:
=SUMPRODUCT(--($C43&$F43&$S43=$C$2:C43&$F$2:F43&$S$2:S43))<=SUMPRODUCT(--($C43&$F43&-$S43=$C$2:$C$282&$F$2:$F$282&$S$2:$S$282))
But when I use/tyoe in same formula in conditioning formating, so it highlights 'true' values (i.e. positive and negative figures of the same kind:
Apple Orange -1
Apple Orange 1
it does highlight but not everything. I can cross-reference it with that extra column with 'true/false' values.
Ideally, I would not want to have any extra column, I want to make sure conditional formating works fine, but it is not, as it also highlighted a couple of 'false' values.
I needed to highlight the same positive and negative figures in column S, if columns C and F were the same, aka:
Apple Orange -1
Apple Orange 1
Apple Banana 1
Apple orange 1
Apple orange -1
When I created the formula below to find true/false values on a separate column, it worked like a charm:
=SUMPRODUCT(--($C43&$F43&$S43=$C$2:C43&$F$2:F43&$S$2:S43))<=SUMPRODUCT(--($C43&$F43&-$S43=$C$2:$C$282&$F$2:$F$282&$S$2:$S$282))
But when I use/tyoe in same formula in conditioning formating, so it highlights 'true' values (i.e. positive and negative figures of the same kind:
Apple Orange -1
Apple Orange 1
it does highlight but not everything. I can cross-reference it with that extra column with 'true/false' values.
Ideally, I would not want to have any extra column, I want to make sure conditional formating works fine, but it is not, as it also highlighted a couple of 'false' values.