Forum Discussion
I have conditional formatting formula is not working. I help with it.
I happened to have helped you a long time ago so had a file that was basically the same. I did exactly what I suggested above and it worked perfectly. I inserted a blank column A to push the Games column to B; highlighted C3:I18; added new rule and pasted the above formula (but replaced "p" with "*" for this old file) and it worked perfect:
notice the light green is the new rule, the dark green rules and red rules are the old rules.
Can send copy on file I am confuse
sry i meant to attach it
Please attach file. I downloaded the image is not clear.
hmm, I know I attached it the 2nd time. I'm thinking the system is filtering it out. If I recall correctly it is a .xlsm (has macros) and that might be the issue. When I get home tonight, I will try to remember to do a 'save as' into .xlsx and see if that works.
That said, the formula is literally what I posted above but with "*" instead of "p" because that old file used "*" instead:
=ISNUMBER(XMATCH("*", A3:C3))*(SUM(C3:E3)>SUM($E3,$I3)/2)
and the applied to range is:
C3:I18
as an explanation I used the floating range A3:C3 in the XMATCH to highlight the pick cell AND the 2 cells to the right of the picked cell IF the score in that group is > than 1/2 the sum of the 2 scores on that line
alternatively you could do a more literal formula like:
=($C3="*")*($E3>$I3)*(COLUMN(C3)<6)+($G3="*")*($E3<$I3)*(COLUMN(C3)>6)
or go back to the old way you had and break each color into 2 separate formulas
=($C3="*")*($E3>$I3) for the left 1/2 and =($G3="*")*($E3>$I3) for the right 1/2