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.
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.
It's because of the blank in C11.
RANK.AVG(C11,C4:C11) is returning #N/A because C11 is blank.
D11 is also blank, but G11 is returning TRUE. Why? Formulas in both F and G columns should return same result.
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
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)))
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.
Like I said, Because RANK.AVG(C11,C4:C11) returns #N/AIt seems that F11 doesn’t recognize OR function in my formula and I wonder why
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
Many thanks for the explanation!
You're welcome.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks