# Value Not Available Error when combining functions

1. ## Value Not Available Error when combining functions

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.

2. ## Re: Value Not Available Error when combining functions

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

3. ## Re: Value Not Available Error when combining functions

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

4. ## Re: Value Not Available Error when combining functions

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

5. ## Re: Value Not Available Error when combining functions

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)))

6. ## Re: Value Not Available Error when combining functions

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.

7. ## Re: Value Not Available Error when combining functions

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

8. ## Re: Value Not Available Error when combining functions

Many thanks for the explanation!

9. ## Re: Value Not Available Error when combining functions

You're welcome.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1