Remember Me?

#1
December 28th 04, 04:05 AM
 Paul Posts: n/a
IF Statement with Average Function results in #Value!

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?

#2
December 28th 04, 06:29 AM
 Biff Posts: n/a

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?

.

#3
December 28th 04, 06:43 AM
 Paul Posts: n/a

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?

.

#4
December 28th 04, 07:08 AM
 Biff Posts: n/a

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!.

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

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?

.

.

#5
December 28th 04, 07:49 AM
 Paul Posts: n/a

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

"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!.

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

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?

.

.

#6
December 28th 04, 08:11 AM
 Biff Posts: n/a

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

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!.

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

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?

.

.

.

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Adam Excel Discussion (Misc queries) 1 December 23rd 04 02:48 PM Nate Oliver Excel Discussion (Misc queries) 0 December 14th 04 04:57 PM RPS Excel Discussion (Misc queries) 1 December 8th 04 02:36 AM PE Excel Discussion (Misc queries) 2 December 7th 04 01:00 AM Somecallmejosh Excel Discussion (Misc queries) 3 December 6th 04 04:25 PM

All times are GMT +1. The time now is 09:40 AM.