Having trouble with conditional formatting on IF formula result

[FrumpyJones]

I have the following formula that works fine to either give me a result or leave a cell blank if the value is zero:
=IF(SUM(OneTable[@[07/01/24]],TwoTable[@[07/01/24]],ThreeTable[@[07/01/24]],FourTable[@[07/01/24]])=0,"",(SUM(OneTable[@[07/01/24]],TwoTable[@[07/01/24]],ThreeTable[@[07/01/24]],FourTable[@[07/01/24]]))

At the bottom of the column I have it showing me the average of all cells which also works fine. For this example, let's say 6.33

I then wanted to do conditional formatting where if any cell in the column was Greater than the average, yellow fill it. Which works great UNTIL I get to the "null" items. It's filling them all with yellow.

I thought maybe put another rule after the first that had a simple formula of ="" no fill, but that did not work. Tried it above and below the first rule and with the "stop if true" checked and unchecked and changed nothing.

Guessing I'm missing something simple, which is why I come to you on bended knee for help.

 

[awoohaw]

Do you want that "" value included as a value in the average, or is it considered a zero?

 

[awoohaw]

Try this:

Book1
	A	B	C	D	E	F	G	H	I	J	K
1
2	CF rule				OneTable		TwoTable		ThreeTable		FourTable
3	TRUE	15			7		8		-8		8
4	TRUE	54			7		20		12		15
5	FALSE	3			-12		2		6		7
6	FALSE	-9			5		-5		2		-11
7	TRUE	12			15		-2		9		-10
8	TRUE	11			10		-7		15		-7
9	FALSE	-38			-4		-12		-13		-9
10	FALSE				-1		-6		5		2
11	FALSE	-15			-4		-5		7		-13
12	TRUE	24			2		5		7		10
13		6.33
14
Sheet2

Cell Formulas
Range	Formula
A3:A12	A3	=IF(B3<>"",B3>=$B$13)
B3:B12	B3	=IF(SUM(OneTable[@OneTable],TwoTable[@TwoTable],ThreeTable[@ThreeTable],FourTable[@FourTable])=0,"", SUM(OneTable[@OneTable],TwoTable[@TwoTable],ThreeTable[@ThreeTable],FourTable[@FourTable]))
B13	B13	=AVERAGE(B3:B12)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B3:B12	Expression	=IF(B3<>"",B3>=$B$13)	text	NO

 

[FrumpyJones]

=IF(B3<>"",B3>=$B$13)

THANK YOU! That conditional format line did it. Maybe it's because it's Friday after 5PM, but I'm trying to assure I understand how its logic is working with my limited logic brain. So the first part is saying if b3 is anything other than "" then check if it's greater than (or equal to) the average, and if that 2nd part is true, use the fill, but if the first part is false, or if the 2nd part is false, do nothing. Do I have that right?

 

[awoohaw]

Happy to help!

Sort of. Conditional Formatting rules only want a TRUE value.
IF the result of B3 <> "" is FALSE already, so there is nothing needed for the second argument of the IF statement.
Similarly, the Second B3>=$B$13 resolves to TRUE or FALSE, nothing else is needed.

To make it more visible you could actually use True and False values in the formula, but that would be just more work both you and excel would be doing, like this:

=IF(B3<>"",IF(B3>=$B$13,TRUE,FALSE),FALSE)

Best Wishes.