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
-----Original Message-----
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),Q3 0:Q515)))
I am not sure how to write this formula
"Biff" wrote:
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
-----Original Message-----
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!
"Biff" wrote:
Hi!
I want to add the "FALSE" LOGIC TO MY IF Statement.
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
-----Original Message-----
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?
.
.
.
|