|  

» Avoiding Errors when Using the And Function to Combine Multiple Conditions in Array Formulas

Problem:

The following Array formula was designed to calculate the average difference between each pair of values in columns A & B (providing both cells are not empty):
{=AVERAGE(IF(AND(ISNUMBER(A1:B5),ISNUMBER(A1:B5)),A1:A5-B1:B5))}
However, an incorrect result of "0" is returned.

Solution:

Use the AVERAGE, IF, and ISNUMBER functions as shown in the following Array formula:
{=AVERAGE(IF(ISNUMBER(A1:A5)*ISNUMBER(B1:B5),A1:A5-B1:B5))}


Rate This Tip
12 34 5
Rating: 2.94     Views: 19583
Should have more documentation
John
I mean, why does this work? A little more explanation is needed.
Click here to post comment
For Registered Users
Name
Comment Title
Comments