IF Statement with Average Function results in #Value!

P

Paul

I wrote an IF Statement that takes the Average of a group of cells based on a
certain condition.

Here is the Formula:

{=AVERAGE(IF(($A$30:$A$515=A1)*($H$30:$H$515=B1),$Q$30:$Q$515))}

When a add a second part to the IF statement, I receive the error message
#Value! I am able to add a number, but not text. Example:

{=AVERAGE(IF(($A$30:$A$515=A1)*($H$30:$H$515=B1),$Q$30:$Q$515),"FALSE")}

I want to add the "FALSE" LOGIC TO MY IF Statement.

Ideas?
 
B

Biff

Hi!
I want to add the "FALSE" LOGIC TO MY IF Statement.
Click to expand...

Well, for starters you have the "FALSE" as an argument in
the AVERAGE function. That's why you're getting #VALUE!.

What do you want the FALSE logic to mean?

If (A30:A515=A1 * H30:H515=B1) = 0, you'll get a #DIV/0!
error. Is that what you want the FALSE logic to mean?

If so, try this: (array)

=IF((A30:A515=A1)*(H30:H515=B1)=0,"FALSE",AVERAGE(IF
((A30:A515=A1)*(H30:H515=B1),Q30:Q515)))

Biff
 
P

Paul

Biff,
I need to somehow make the cell =TEXT and not #DIV/0!. This cell is linked
to another workbook where I take the average of mutiple workbook cells. If
this cell = #div/0!, then the average cannot be taken.
For Example, Workbook1 A1 =5 and Workbook2 A1=5 and Workbook3 =#div/0! then
when I take the average of the three workbooks, I get #div/0!
 
B

Biff

Hi!

Ok, the formula I suggested will take care of that but if
you want to specifically test for any errors and trap them:

=IF(ISERROR(AVERAGE(IF((A30:A515=A1)*
(H30:H515=B1),Q30:Q515))),"FALSE",AVERAGE(IF((A30:A515=A1)*
(H30:H515-B1),Q30:Q515)))

Biff
 
P

Paul

When I apply this formula, if Q30:Q515 =FALSE, then I receive the #div/0! as
a value.
=IF((A30:A515=A1)*(H30:H515=B1)=0,"FALSE",AVERAGE(IF((A30:A515=A1)*(H30:H515=B1),Q30:Q515)))

IF Q30:Q515 =FALSE and H30:515 =B1,
"FALSE",AVERAGE(IF((A30:A515=A1)*(H30:H515=B1),Q30:Q515)))

I am not sure how to write this formula
 
B

Biff

Hi!

AVERAGE will exclude text.

Both formulas I've suggested work for me.

If you're able to, you can send me a copy of the file (if
it's not too big >1mb) and I'll take a look.

Biff is valko01 at comcast period net.

Biff
 
Top