Value Not Available Error when combining functions

[vbaovac]

Hello,
Combining AND, RANK.AVG and OR functions, I sometimes receive an error. In the attached worksheet, for example, there is a “Value Not Available Error” in cell F11 and not in G11. Why? Formulas in both columns should produce same result.

[Jonmo1]

It's because of the blank in C11.
RANK.AVG(C11,C4:C11) is returning #N/A because C11 is blank.

[vbaovac]

D11 is also blank, but G11 is returning TRUE. Why? Formulas in both F and G columns should return same result.

[Jonmo1]

The formula in G11 is NOT using this function RANK.AVG(C11,C4:C11)
Instead, it is merely doing D11<7
D11 being blank will not cause an error with that

[Jonmo1]

So what do you want to happen in the case that cell is blank ?
Do you want to assume the Rank.Avg<7 test is TRUE if C is blank?

Try
=AND(RANK.AVG(A11,A4:A11)<5,OR(RANK.AVG(B11,B4:B11)>4,IFERROR(RANK.AVG(C11,C4:C11)<7,TRUE)))

[vbaovac]

Formula in F11 should return TRUE if either RANK.AVG(B11,B4:B11)>4 or RANK.AVG(C11,C4:C11)<7 is true. It seems that F11 doesn’t recognize OR function in my formula and I wonder why.

[Jonmo1]

It seems that F11 doesn’t recognize OR function in my formula and I wonder why

Like I said, Because RANK.AVG(C11,C4:C11) returns #N/A

Once any function returns an error value like #N/A, then that error propegates throughout the entire formula, unluess it's trapped with an error trapping function like IFERROR.
Basically, the RanklAvg function is the source of the #N/A
Then the OR returns #N/A simply because an argument contained in within the OR is returning #N/A. Regardless if the other argument is TRUE or FALSE, both arguments of the OR are processed.
Then the AND returns #N/A simply because the OR within the AND is returning #N/A

[vbaovac]

Many thanks for the explanation!

[Jonmo1]

You're welcome.