What's wrong with this array formula??

  • Thread starter Thread starter ModelerGirl
  • Start date Start date
M

ModelerGirl

Here is the formula I am using:
=+AVERAGE(IF(AND(ISNUMBER(AI12:AI39),ISNUMBER(AH12:AH39),I12:I39=1)),AI12:AI39-AH12:AH39)

I wanted to find the median of ai12-ah12,ai13-ah13,.....ai39-ah39, bu
I only wanted to include those rows where both the ah and the ai cel
weren't blank, and where the corresponding cell in the i row is 1.

Excel gives me a #VALUE error. If I change the parentheses around, i
gives me a zero value.

Can someone tell me how to do this??

Thanks,

Kat
 
Your IF() statement has a condition, but no actions.
AND() does not work well in array formulas.
"+" is unnecessary.

Try

=AVERAGE(IF(ISNUMBER(AI12:AI39)*ISNUMBER(AH12:AH39)*(I12:I39=1),AI12:AI39-AH12:AH39))

array entered (Ctrl-Shift-Enter)

Jerry
 
Back
Top