What formula do I use to find the average of certain columns, when columns
around it equal specific text?

example: I need the average of Column D, ONLY IF A equals "no" and B equals
"full". (in this case the average of D2 and D4)

A B C D
1 yes full 0 27
2 no full 3 15
3 yes empty 9 31
4 no full 1 16
5 no empty 7 16

Try this array formula** which will work in all versions of Excel:

=AVERAGE(IF((A1:A5="no")*(B1:B5="full"),D1:D5))

Try this normally entered version if you're using Excel 2007 only:

=AVERAGEIFS(D1:D5,A1:A5,"no",B1:B5,"full")

--
Biff
Microsoft Excel MVP


"taurus99" wrote in message
...
What formula do I use to find the average of certain columns, when columns
around it equal specific text?

example: I need the average of Column D, ONLY IF A equals "no" and B
equals
"full". (in this case the average of D2 and D4)

A B C D
1 yes full 0 27
2 no full 3 15
3 yes empty 9 31
4 no full 1 16
5 no empty 7 16

Forgot something:

Try this array formula**

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Try this array formula** which will work in all versions of Excel:

=AVERAGE(IF((A1:A5="no")*(B1:B5="full"),D1:D5))

Try this normally entered version if you're using Excel 2007 only:

=AVERAGEIFS(D1:D5,A1:A5,"no",B1:B5,"full")

--
Biff
Microsoft Excel MVP


"taurus99" wrote in message
...
What formula do I use to find the average of certain columns, when
columns
around it equal specific text?

example: I need the average of Column D, ONLY IF A equals "no" and B
equals
"full". (in this case the average of D2 and D4)

A B C D
1 yes full 0 27
2 no full 3 15
3 yes empty 9 31
4 no full 1 16
5 no empty 7 16

taurus99 wrote:
What formula do I use to find the average of certain columns, when columns
around it equal specific text?

example: I need the average of Column D, ONLY IF A equals "no" and B equals
"full". (in this case the average of D2 and D4)

A B C D
1 yes full 0 27
2 no full 3 15
3 yes empty 9 31
4 no full 1 16
5 no empty 7 16


=average(if(--(a1:a5="no"),--(b1:b5="full"),d1:d5))

smartin wrote:
taurus99 wrote:
What formula do I use to find the average of certain columns, when
columns around it equal specific text?

example: I need the average of Column D, ONLY IF A equals "no" and B
equals "full". (in this case the average of D2 and D4)

A B C D
1 yes full 0 27
2 no full 3 15
3 yes empty 9 31
4 no full 1 16
5 no empty 7 16


Oops I should have written

=average(if((a1:a5="no"),(b1:b5="full"),d1:d5))

and mentioned this is an array formula (press Ctrl+Shift+Enter to commit.)

Thanks, smartin, that worked!!

One more question, what if I wanted to add one more column in there?

The average of Column D, ONLY IF A equals "no" and B equals "full" and C
equals "open".

A B C D
1 yes full open 27
2 no full open 15
3 yes empty closed 31
4 no full open 16
5 no empty open 16


"smartin" wrote:

smartin wrote:
taurus99 wrote:
What formula do I use to find the average of certain columns, when
columns around it equal specific text?

example: I need the average of Column D, ONLY IF A equals "no" and B
equals "full". (in this case the average of D2 and D4)

A B C D
1 yes full 0 27
2 no full 3 15
3 yes empty 9 31
4 no full 1 16
5 no empty 7 16


Oops I should have written

=average(if((a1:a5="no"),(b1:b5="full"),d1:d5))

and mentioned this is an array formula (press Ctrl+Shift+Enter to commit.)

Glad it worked!

You can just add your new constraint to the IF condition:

=average(if((a1:a5="no"),(b1:b5="full"),(c1:c5="op en"),d1:d5))

Again, this is an array formula, so Ctrl+Shift+Enter.

taurus99 wrote:
Thanks, smartin, that worked!!

One more question, what if I wanted to add one more column in there?

The average of Column D, ONLY IF A equals "no" and B equals "full" and C
equals "open".

A B C D
1 yes full open 27
2 no full open 15
3 yes empty closed 31
4 no full open 16
5 no empty open 16


"smartin" wrote:

smartin wrote:
taurus99 wrote:
What formula do I use to find the average of certain columns, when
columns around it equal specific text?

example: I need the average of Column D, ONLY IF A equals "no" and B
equals "full". (in this case the average of D2 and D4)

A B C D
1 yes full 0 27
2 no full 3 15
3 yes empty 9 31
4 no full 1 16
5 no empty 7 16

Oops I should have written

=average(if((a1:a5="no"),(b1:b5="full"),d1:d5))

and mentioned this is an array formula (press Ctrl+Shift+Enter to commit.)

I tried that before I wrote back and it didn't work. When I hit CTRL SHIFT
ENTER I got an error message that reads:

"You've entered too many arguments for this function"

Any suggestions?

This is my formula ... (on my real spreadsheet)

=AVERAGE(IF((D55:D5000="0"),(E55:E5000="FTFB"),(G5 5:G5000="Y"),F55:F5000))

It's works just fine with just the "0" and "FTFB" but when I add the 3rd one
"Y", it won't work.



"smartin" wrote:

Glad it worked!

You can just add your new constraint to the IF condition:

=average(if((a1:a5="no"),(b1:b5="full"),(c1:c5="op en"),d1:d5))

Again, this is an array formula, so Ctrl+Shift+Enter.

taurus99 wrote:
Thanks, smartin, that worked!!

One more question, what if I wanted to add one more column in there?

The average of Column D, ONLY IF A equals "no" and B equals "full" and C
equals "open".

A B C D
1 yes full open 27
2 no full open 15
3 yes empty closed 31
4 no full open 16
5 no empty open 16


"smartin" wrote:

smartin wrote:
taurus99 wrote:
What formula do I use to find the average of certain columns, when
columns around it equal specific text?

example: I need the average of Column D, ONLY IF A equals "no" and B
equals "full". (in this case the average of D2 and D4)

A B C D
1 yes full 0 27
2 no full 3 15
3 yes empty 9 31
4 no full 1 16
5 no empty 7 16

Oops I should have written

=average(if((a1:a5="no"),(b1:b5="full"),d1:d5))

and mentioned this is an array formula (press Ctrl+Shift+Enter to commit.)

=average(if((a1:a5="no"),(b1:b5="full"),d1:d5))

If that returns the correct result then it's just a coincidence.

Try it like this (array entered):

=AVERAGE(IF((A1:A5="no")*(B1:B5="full"),C1:C5))


--
Biff
Microsoft Excel MVP


"smartin" wrote in message
...
smartin wrote:
taurus99 wrote:
What formula do I use to find the average of certain columns, when
columns around it equal specific text?

example: I need the average of Column D, ONLY IF A equals "no" and B
equals "full". (in this case the average of D2 and D4)

A B C D
1 yes full 0 27
2 no full 3 15
3 yes empty 9 31
4 no full 1 16
5 no empty 7 16


Oops I should have written

=average(if((a1:a5="no"),(b1:b5="full"),d1:d5))

and mentioned this is an array formula (press Ctrl+Shift+Enter to commit.)

AVERAGE(IF((D55:D5000="0"),(E55:E5000="FTFB"),(G5 5:G5000="Y"),F55:F5000))

Try it like this (array entered):

=AVERAGE(IF((D55:D5000="0")*(E55:E5000="FTFB")*(G5 5:G5000="Y"),F55:F5000))

Does D55:D5000 contain TEXT 0 or numeric 0?

In the formula you're testing for TEXT 0. TEXT 0 and numeric 0 are not the
same.


--
Biff
Microsoft Excel MVP


"taurus99" wrote in message
...
I tried that before I wrote back and it didn't work. When I hit CTRL SHIFT
ENTER I got an error message that reads:

"You've entered too many arguments for this function"

Any suggestions?

This is my formula ... (on my real spreadsheet)

=AVERAGE(IF((D55:D5000="0"),(E55:E5000="FTFB"),(G5 5:G5000="Y"),F55:F5000))

It's works just fine with just the "0" and "FTFB" but when I add the 3rd
one
"Y", it won't work.



"smartin" wrote:

Glad it worked!

You can just add your new constraint to the IF condition:

=average(if((a1:a5="no"),(b1:b5="full"),(c1:c5="op en"),d1:d5))

Again, this is an array formula, so Ctrl+Shift+Enter.

taurus99 wrote:
Thanks, smartin, that worked!!

One more question, what if I wanted to add one more column in there?

The average of Column D, ONLY IF A equals "no" and B equals "full" and
C
equals "open".

A B C D
1 yes full open 27
2 no full open 15
3 yes empty closed 31
4 no full open 16
5 no empty open 16


"smartin" wrote:

smartin wrote:
taurus99 wrote:
What formula do I use to find the average of certain columns, when
columns around it equal specific text?

example: I need the average of Column D, ONLY IF A equals "no" and
B
equals "full". (in this case the average of D2 and D4)

A B C D
1 yes full 0 27
2 no full 3 15
3 yes empty 9 31
4 no full 1 16
5 no empty 7 16

Oops I should have written

=average(if((a1:a5="no"),(b1:b5="full"),d1:d5))

and mentioned this is an array formula (press Ctrl+Shift+Enter to
commit.)

I copied that exact formula in and got a #DIV/0! error

D = numeric 0
E = Text FTFB
G = Text Y

"T. Valko" wrote:

AVERAGE(IF((D55:D5000="0"),(E55:E5000="FTFB"),(G5 5:G5000="Y"),F55:F5000))

Try it like this (array entered):

=AVERAGE(IF((D55:D5000="0")*(E55:E5000="FTFB")*(G5 5:G5000="Y"),F55:F5000))

Does D55:D5000 contain TEXT 0 or numeric 0?

In the formula you're testing for TEXT 0. TEXT 0 and numeric 0 are not the
same.


--
Biff
Microsoft Excel MVP


"taurus99" wrote in message
...
I tried that before I wrote back and it didn't work. When I hit CTRL SHIFT
ENTER I got an error message that reads:

"You've entered too many arguments for this function"

Any suggestions?

This is my formula ... (on my real spreadsheet)

=AVERAGE(IF((D55:D5000="0"),(E55:E5000="FTFB"),(G5 5:G5000="Y"),F55:F5000))

It's works just fine with just the "0" and "FTFB" but when I add the 3rd
one
"Y", it won't work.



"smartin" wrote:

Glad it worked!

You can just add your new constraint to the IF condition:

=average(if((a1:a5="no"),(b1:b5="full"),(c1:c5="op en"),d1:d5))

Again, this is an array formula, so Ctrl+Shift+Enter.

taurus99 wrote:
Thanks, smartin, that worked!!

One more question, what if I wanted to add one more column in there?

The average of Column D, ONLY IF A equals "no" and B equals "full" and
C
equals "open".

A B C D
1 yes full open 27
2 no full open 15
3 yes empty closed 31
4 no full open 16
5 no empty open 16


"smartin" wrote:

smartin wrote:
taurus99 wrote:
What formula do I use to find the average of certain columns, when
columns around it equal specific text?

example: I need the average of Column D, ONLY IF A equals "no" and
B
equals "full". (in this case the average of D2 and D4)

A B C D
1 yes full 0 27
2 no full 3 15
3 yes empty 9 31
4 no full 1 16
5 no empty 7 16

Oops I should have written

=average(if((a1:a5="no"),(b1:b5="full"),d1:d5))

and mentioned this is an array formula (press Ctrl+Shift+Enter to
commit.)

Remove the quotes from around the 0:

=AVERAGE(IF((D55:D5000=0)*(E55:E5000="FTFB")*(G55: G5000="Y"),F55:F5000))

Make sure you array enter.

When you quote numbers, "0", Excel evaluates them as TEXT.

Now, this leads to another possible glitch. An *empty cell* will evaluate to
0. To exclude empty cells from be evaluated (if they might be present) you
need to add another test:

=AVERAGE(IF((ISNUMBER(D55:D5000))*(D55:D5000=0)*(E 55:E5000="FTFB")*(G55:G5000="Y"),F55:F5000))


--
Biff
Microsoft Excel MVP


"taurus99" wrote in message
...
I copied that exact formula in and got a #DIV/0! error

D = numeric 0
E = Text FTFB
G = Text Y

"T. Valko" wrote:

AVERAGE(IF((D55:D5000="0"),(E55:E5000="FTFB"),(G5 5:G5000="Y"),F55:F5000))

Try it like this (array entered):

=AVERAGE(IF((D55:D5000="0")*(E55:E5000="FTFB")*(G5 5:G5000="Y"),F55:F5000))

Does D55:D5000 contain TEXT 0 or numeric 0?

In the formula you're testing for TEXT 0. TEXT 0 and numeric 0 are not
the
same.


--
Biff
Microsoft Excel MVP


"taurus99" wrote in message
...
I tried that before I wrote back and it didn't work. When I hit CTRL
SHIFT
ENTER I got an error message that reads:

"You've entered too many arguments for this function"

Any suggestions?

This is my formula ... (on my real spreadsheet)

=AVERAGE(IF((D55:D5000="0"),(E55:E5000="FTFB"),(G5 5:G5000="Y"),F55:F5000))

It's works just fine with just the "0" and "FTFB" but when I add the
3rd
one
"Y", it won't work.



"smartin" wrote:

Glad it worked!

You can just add your new constraint to the IF condition:

=average(if((a1:a5="no"),(b1:b5="full"),(c1:c5="op en"),d1:d5))

Again, this is an array formula, so Ctrl+Shift+Enter.

taurus99 wrote:
Thanks, smartin, that worked!!

One more question, what if I wanted to add one more column in there?

The average of Column D, ONLY IF A equals "no" and B equals "full"
and
C
equals "open".

A B C D
1 yes full open 27
2 no full open 15
3 yes empty closed 31
4 no full open 16
5 no empty open 16


"smartin" wrote:

smartin wrote:
taurus99 wrote:
What formula do I use to find the average of certain columns,
when
columns around it equal specific text?

example: I need the average of Column D, ONLY IF A equals "no"
and
B
equals "full". (in this case the average of D2 and D4)

A B C D
1 yes full 0 27
2 no full 3 15
3 yes empty 9 31
4 no full 1 16
5 no empty 7 16

Oops I should have written

=average(if((a1:a5="no"),(b1:b5="full"),d1:d5))

and mentioned this is an array formula (press Ctrl+Shift+Enter to
commit.)

T. Valko wrote:
=average(if((a1:a5="no"),(b1:b5="full"),d1:d5))

If that returns the correct result then it's just a coincidence.

Try it like this (array entered):

=AVERAGE(IF((A1:A5="no")*(B1:B5="full"),C1:C5))


Definitely, I was not correct. Apologies, and thanks Biff for catching
my mistake.

There are no empty cells, but that is good to know.

It worked PERFECTLY! Thanks!!

So, one more final question ... what is this array stuff all about? I
noticed I can't just copy my formula down the page and have excel change the
values for me like other formulas ...

"T. Valko" wrote:

Remove the quotes from around the 0:

=AVERAGE(IF((D55:D5000=0)*(E55:E5000="FTFB")*(G55: G5000="Y"),F55:F5000))

Make sure you array enter.

When you quote numbers, "0", Excel evaluates them as TEXT.

Now, this leads to another possible glitch. An *empty cell* will evaluate to
0. To exclude empty cells from be evaluated (if they might be present) you
need to add another test:

=AVERAGE(IF((ISNUMBER(D55:D5000))*(D55:D5000=0)*(E 55:E5000="FTFB")*(G55:G5000="Y"),F55:F5000))


--
Biff
Microsoft Excel MVP


"taurus99" wrote in message
...
I copied that exact formula in and got a #DIV/0! error

D = numeric 0
E = Text FTFB
G = Text Y

"T. Valko" wrote:

AVERAGE(IF((D55:D5000="0"),(E55:E5000="FTFB"),(G5 5:G5000="Y"),F55:F5000))

Try it like this (array entered):

=AVERAGE(IF((D55:D5000="0")*(E55:E5000="FTFB")*(G5 5:G5000="Y"),F55:F5000))

Does D55:D5000 contain TEXT 0 or numeric 0?

In the formula you're testing for TEXT 0. TEXT 0 and numeric 0 are not
the
same.


--
Biff
Microsoft Excel MVP


"taurus99" wrote in message
...
I tried that before I wrote back and it didn't work. When I hit CTRL
SHIFT
ENTER I got an error message that reads:

"You've entered too many arguments for this function"

Any suggestions?

This is my formula ... (on my real spreadsheet)

=AVERAGE(IF((D55:D5000="0"),(E55:E5000="FTFB"),(G5 5:G5000="Y"),F55:F5000))

It's works just fine with just the "0" and "FTFB" but when I add the
3rd
one
"Y", it won't work.



"smartin" wrote:

Glad it worked!

You can just add your new constraint to the IF condition:

=average(if((a1:a5="no"),(b1:b5="full"),(c1:c5="op en"),d1:d5))

Again, this is an array formula, so Ctrl+Shift+Enter.

taurus99 wrote:
Thanks, smartin, that worked!!

One more question, what if I wanted to add one more column in there?

The average of Column D, ONLY IF A equals "no" and B equals "full"
and
C
equals "open".

A B C D
1 yes full open 27
2 no full open 15
3 yes empty closed 31
4 no full open 16
5 no empty open 16


"smartin" wrote:

smartin wrote:
taurus99 wrote:
What formula do I use to find the average of certain columns,
when
columns around it equal specific text?

example: I need the average of Column D, ONLY IF A equals "no"
and
B
equals "full". (in this case the average of D2 and D4)

A B C D
1 yes full 0 27
2 no full 3 15
3 yes empty 9 31
4 no full 1 16
5 no empty 7 16

Oops I should have written

=average(if((a1:a5="no"),(b1:b5="full"),d1:d5))

and mentioned this is an array formula (press Ctrl+Shift+Enter to
commit.)

Nevermind, I figured it out. WOO HOO

Thanks, you have been a huge help!!

"taurus99" wrote:

There are no empty cells, but that is good to know.

It worked PERFECTLY! Thanks!!

So, one more final question ... what is this array stuff all about? I
noticed I can't just copy my formula down the page and have excel change the
values for me like other formulas ...

"T. Valko" wrote:

Remove the quotes from around the 0:

=AVERAGE(IF((D55:D5000=0)*(E55:E5000="FTFB")*(G55: G5000="Y"),F55:F5000))

Make sure you array enter.

When you quote numbers, "0", Excel evaluates them as TEXT.

Now, this leads to another possible glitch. An *empty cell* will evaluate to
0. To exclude empty cells from be evaluated (if they might be present) you
need to add another test:

=AVERAGE(IF((ISNUMBER(D55:D5000))*(D55:D5000=0)*(E 55:E5000="FTFB")*(G55:G5000="Y"),F55:F5000))


--
Biff
Microsoft Excel MVP


"taurus99" wrote in message
...
I copied that exact formula in and got a #DIV/0! error

D = numeric 0
E = Text FTFB
G = Text Y

"T. Valko" wrote:

AVERAGE(IF((D55:D5000="0"),(E55:E5000="FTFB"),(G5 5:G5000="Y"),F55:F5000))

Try it like this (array entered):

=AVERAGE(IF((D55:D5000="0")*(E55:E5000="FTFB")*(G5 5:G5000="Y"),F55:F5000))

Does D55:D5000 contain TEXT 0 or numeric 0?

In the formula you're testing for TEXT 0. TEXT 0 and numeric 0 are not
the
same.


--
Biff
Microsoft Excel MVP


"taurus99" wrote in message
...
I tried that before I wrote back and it didn't work. When I hit CTRL
SHIFT
ENTER I got an error message that reads:

"You've entered too many arguments for this function"

Any suggestions?

This is my formula ... (on my real spreadsheet)

=AVERAGE(IF((D55:D5000="0"),(E55:E5000="FTFB"),(G5 5:G5000="Y"),F55:F5000))

It's works just fine with just the "0" and "FTFB" but when I add the
3rd
one
"Y", it won't work.



"smartin" wrote:

Glad it worked!

You can just add your new constraint to the IF condition:

=average(if((a1:a5="no"),(b1:b5="full"),(c1:c5="op en"),d1:d5))

Again, this is an array formula, so Ctrl+Shift+Enter.

taurus99 wrote:
Thanks, smartin, that worked!!

One more question, what if I wanted to add one more column in there?

The average of Column D, ONLY IF A equals "no" and B equals "full"
and
C
equals "open".

A B C D
1 yes full open 27
2 no full open 15
3 yes empty closed 31
4 no full open 16
5 no empty open 16


"smartin" wrote:

smartin wrote:
taurus99 wrote:
What formula do I use to find the average of certain columns,
when
columns around it equal specific text?

example: I need the average of Column D, ONLY IF A equals "no"
and
B
equals "full". (in this case the average of D2 and D4)

A B C D
1 yes full 0 27
2 no full 3 15
3 yes empty 9 31
4 no full 1 16
5 no empty 7 16

Oops I should have written

=average(if((a1:a5="no"),(b1:b5="full"),d1:d5))

and mentioned this is an array formula (press Ctrl+Shift+Enter to
commit.)

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"taurus99" wrote in message
...
Nevermind, I figured it out. WOO HOO

Thanks, you have been a huge help!!

"taurus99" wrote:

There are no empty cells, but that is good to know.

It worked PERFECTLY! Thanks!!

So, one more final question ... what is this array stuff all about? I
noticed I can't just copy my formula down the page and have excel change
the
values for me like other formulas ...

"T. Valko" wrote:

Remove the quotes from around the 0:

=AVERAGE(IF((D55:D5000=0)*(E55:E5000="FTFB")*(G55: G5000="Y"),F55:F5000))

Make sure you array enter.

When you quote numbers, "0", Excel evaluates them as TEXT.

Now, this leads to another possible glitch. An *empty cell* will
evaluate to
0. To exclude empty cells from be evaluated (if they might be present)
you
need to add another test:

=AVERAGE(IF((ISNUMBER(D55:D5000))*(D55:D5000=0)*(E 55:E5000="FTFB")*(G55:G5000="Y"),F55:F5000))


--
Biff
Microsoft Excel MVP


"taurus99" wrote in message
...
I copied that exact formula in and got a #DIV/0! error

D = numeric 0
E = Text FTFB
G = Text Y

"T. Valko" wrote:

AVERAGE(IF((D55:D5000="0"),(E55:E5000="FTFB"),(G5 5:G5000="Y"),F55:F5000))

Try it like this (array entered):

=AVERAGE(IF((D55:D5000="0")*(E55:E5000="FTFB")*(G5 5:G5000="Y"),F55:F5000))

Does D55:D5000 contain TEXT 0 or numeric 0?

In the formula you're testing for TEXT 0. TEXT 0 and numeric 0 are
not
the
same.


--
Biff
Microsoft Excel MVP


"taurus99" wrote in message
...
I tried that before I wrote back and it didn't work. When I hit
CTRL
SHIFT
ENTER I got an error message that reads:

"You've entered too many arguments for this function"

Any suggestions?

This is my formula ... (on my real spreadsheet)

=AVERAGE(IF((D55:D5000="0"),(E55:E5000="FTFB"),(G5 5:G5000="Y"),F55:F5000))

It's works just fine with just the "0" and "FTFB" but when I add
the
3rd
one
"Y", it won't work.



"smartin" wrote:

Glad it worked!

You can just add your new constraint to the IF condition:

=average(if((a1:a5="no"),(b1:b5="full"),(c1:c5="op en"),d1:d5))

Again, this is an array formula, so Ctrl+Shift+Enter.

taurus99 wrote:
Thanks, smartin, that worked!!

One more question, what if I wanted to add one more column in
there?

The average of Column D, ONLY IF A equals "no" and B equals
"full"
and
C
equals "open".

A B C D
1 yes full open 27
2 no full open 15
3 yes empty closed 31
4 no full open 16
5 no empty open 16


"smartin" wrote:

smartin wrote:
taurus99 wrote:
What formula do I use to find the average of certain
columns,
when
columns around it equal specific text?

example: I need the average of Column D, ONLY IF A equals
"no"
and
B
equals "full". (in this case the average of D2 and D4)

A B C D
1 yes full 0 27
2 no full 3 15
3 yes empty 9 31
4 no full 1 16
5 no empty 7 16

Oops I should have written

=average(if((a1:a5="no"),(b1:b5="full"),d1:d5))

and mentioned this is an array formula (press Ctrl+Shift+Enter
to
commit.)

Ok, so while I have you still (and I am learning so much tonight) ... I came
upon another scenerio.

What if I wanted to include one or two more text words in a column.

In this example:

Average of D, if A = "NO", B = "FULL" and C = "open", "partial", or "closed".
(I need the average of D2, D4 and D7)

A B C D
1 yes full open 27
2 no full partial 15
3 yes empty closed 31
4 no full open 19
5 no empty open 16
6 yes full closed 7
7 no full closed 10

In my real formula I put this, which I KNOW is wrong! LOL
E is the column which has 3 different items of text.

=AVERAGE(IF((D55:D6000=0)*(E55:E6000="PT
Flex")*(E55:E6000="PT")*(E55:E6000="FTLB")*(G55:G6 000="Y"),F55:F6000))


"T. Valko" wrote:

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"taurus99" wrote in message
...
Nevermind, I figured it out. WOO HOO

Thanks, you have been a huge help!!

"taurus99" wrote:

There are no empty cells, but that is good to know.

It worked PERFECTLY! Thanks!!

So, one more final question ... what is this array stuff all about? I
noticed I can't just copy my formula down the page and have excel change
the
values for me like other formulas ...

"T. Valko" wrote:

Remove the quotes from around the 0:

=AVERAGE(IF((D55:D5000=0)*(E55:E5000="FTFB")*(G55: G5000="Y"),F55:F5000))

Make sure you array enter.

When you quote numbers, "0", Excel evaluates them as TEXT.

Now, this leads to another possible glitch. An *empty cell* will
evaluate to
0. To exclude empty cells from be evaluated (if they might be present)
you
need to add another test:

=AVERAGE(IF((ISNUMBER(D55:D5000))*(D55:D5000=0)*(E 55:E5000="FTFB")*(G55:G5000="Y"),F55:F5000))


--
Biff
Microsoft Excel MVP


"taurus99" wrote in message
...
I copied that exact formula in and got a #DIV/0! error

D = numeric 0
E = Text FTFB
G = Text Y

"T. Valko" wrote:

AVERAGE(IF((D55:D5000="0"),(E55:E5000="FTFB"),(G5 5:G5000="Y"),F55:F5000))

Try it like this (array entered):

=AVERAGE(IF((D55:D5000="0")*(E55:E5000="FTFB")*(G5 5:G5000="Y"),F55:F5000))

Does D55:D5000 contain TEXT 0 or numeric 0?

In the formula you're testing for TEXT 0. TEXT 0 and numeric 0 are
not
the
same.


--
Biff
Microsoft Excel MVP


"taurus99" wrote in message
...
I tried that before I wrote back and it didn't work. When I hit
CTRL
SHIFT
ENTER I got an error message that reads:

"You've entered too many arguments for this function"

Any suggestions?

This is my formula ... (on my real spreadsheet)

=AVERAGE(IF((D55:D5000="0"),(E55:E5000="FTFB"),(G5 5:G5000="Y"),F55:F5000))

It's works just fine with just the "0" and "FTFB" but when I add
the
3rd
one
"Y", it won't work.



"smartin" wrote:

Glad it worked!

You can just add your new constraint to the IF condition:

=average(if((a1:a5="no"),(b1:b5="full"),(c1:c5="op en"),d1:d5))

Again, this is an array formula, so Ctrl+Shift+Enter.

taurus99 wrote:
Thanks, smartin, that worked!!

One more question, what if I wanted to add one more column in
there?

The average of Column D, ONLY IF A equals "no" and B equals
"full"
and
C
equals "open".

A B C D
1 yes full open 27
2 no full open 15
3 yes empty closed 31
4 no full open 16
5 no empty open 16


"smartin" wrote:

smartin wrote:
taurus99 wrote:
What formula do I use to find the average of certain
columns,
when
columns around it equal specific text?

example: I need the average of Column D, ONLY IF A equals
"no"
and
B
equals "full". (in this case the average of D2 and D4)

A B C D
1 yes full 0 27
2 no full 3 15
3 yes empty 9 31
4 no full 1 16
5 no empty 7 16

Oops I should have written

=average(if((a1:a5="no"),(b1:b5="full"),d1:d5))

and mentioned this is an array formula (press Ctrl+Shift+Enter
to
commit.)

C = "open", "partial", or "closed".

If those 3 are the only possible entries then all you need to do is check
and make sure column C isn't empty:

=AVERAGE(IF((A1:A7="no")*(B1:B7="full")*(C1:C7<"" ),D1:D7))

If there are more possible entries than those 3...

List the variables in a range of cells:

H1 = open
H2 = partial
H3 = closed

Then:

=AVERAGE(IF((A1:A7="no")*(B1:B7="full")*(ISNUMBER( MATCH(C1:C7,H1:H3,0))),D1:D7))

Don't forget, array entered!

--
Biff
Microsoft Excel MVP


"taurus99" wrote in message
...
Ok, so while I have you still (and I am learning so much tonight) ... I
came
upon another scenerio.

What if I wanted to include one or two more text words in a column.

In this example:

Average of D, if A = "NO", B = "FULL" and C = "open", "partial", or
"closed".
(I need the average of D2, D4 and D7)

A B C D
1 yes full open 27
2 no full partial 15
3 yes empty closed 31
4 no full open 19
5 no empty open 16
6 yes full closed 7
7 no full closed 10

In my real formula I put this, which I KNOW is wrong! LOL
E is the column which has 3 different items of text.

=AVERAGE(IF((D55:D6000=0)*(E55:E6000="PT
Flex")*(E55:E6000="PT")*(E55:E6000="FTLB")*(G55:G6 000="Y"),F55:F6000))


"T. Valko" wrote:

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"taurus99" wrote in message
...
Nevermind, I figured it out. WOO HOO

Thanks, you have been a huge help!!

"taurus99" wrote:

There are no empty cells, but that is good to know.

It worked PERFECTLY! Thanks!!

So, one more final question ... what is this array stuff all about? I
noticed I can't just copy my formula down the page and have excel
change
the
values for me like other formulas ...

"T. Valko" wrote:

Remove the quotes from around the 0:

=AVERAGE(IF((D55:D5000=0)*(E55:E5000="FTFB")*(G55: G5000="Y"),F55:F5000))

Make sure you array enter.

When you quote numbers, "0", Excel evaluates them as TEXT.

Now, this leads to another possible glitch. An *empty cell* will
evaluate to
0. To exclude empty cells from be evaluated (if they might be
present)
you
need to add another test:

=AVERAGE(IF((ISNUMBER(D55:D5000))*(D55:D5000=0)*(E 55:E5000="FTFB")*(G55:G5000="Y"),F55:F5000))


--
Biff
Microsoft Excel MVP


"taurus99" wrote in message
...
I copied that exact formula in and got a #DIV/0! error

D = numeric 0
E = Text FTFB
G = Text Y

"T. Valko" wrote:

AVERAGE(IF((D55:D5000="0"),(E55:E5000="FTFB"),(G5 5:G5000="Y"),F55:F5000))

Try it like this (array entered):

=AVERAGE(IF((D55:D5000="0")*(E55:E5000="FTFB")*(G5 5:G5000="Y"),F55:F5000))

Does D55:D5000 contain TEXT 0 or numeric 0?

In the formula you're testing for TEXT 0. TEXT 0 and numeric 0
are
not
the
same.


--
Biff
Microsoft Excel MVP


"taurus99" wrote in message
...
I tried that before I wrote back and it didn't work. When I hit
CTRL
SHIFT
ENTER I got an error message that reads:

"You've entered too many arguments for this function"

Any suggestions?

This is my formula ... (on my real spreadsheet)

=AVERAGE(IF((D55:D5000="0"),(E55:E5000="FTFB"),(G5 5:G5000="Y"),F55:F5000))

It's works just fine with just the "0" and "FTFB" but when I
add
the
3rd
one
"Y", it won't work.



"smartin" wrote:

Glad it worked!

You can just add your new constraint to the IF condition:

=average(if((a1:a5="no"),(b1:b5="full"),(c1:c5="op en"),d1:d5))

Again, this is an array formula, so Ctrl+Shift+Enter.

taurus99 wrote:
Thanks, smartin, that worked!!

One more question, what if I wanted to add one more column
in
there?

The average of Column D, ONLY IF A equals "no" and B equals
"full"
and
C
equals "open".

A B C D
1 yes full open 27
2 no full open 15
3 yes empty closed 31
4 no full open 16
5 no empty open 16


"smartin" wrote:

smartin wrote:
taurus99 wrote:
What formula do I use to find the average of certain
columns,
when
columns around it equal specific text?

example: I need the average of Column D, ONLY IF A
equals
"no"
and
B
equals "full". (in this case the average of D2 and D4)

A B C D
1 yes full 0 27
2 no full 3 15
3 yes empty 9 31
4 no full 1 16
5 no empty 7 16

Oops I should have written

=average(if((a1:a5="no"),(b1:b5="full"),d1:d5))

and mentioned this is an array formula (press
Ctrl+Shift+Enter
to
commit.)

Unbelievable! Worked perfectly.

You are a life saver!!!!!!

Thank you so much and have a great day!

"T. Valko" wrote:

C = "open", "partial", or "closed".

If those 3 are the only possible entries then all you need to do is check
and make sure column C isn't empty:

=AVERAGE(IF((A1:A7="no")*(B1:B7="full")*(C1:C7<"" ),D1:D7))

If there are more possible entries than those 3...

List the variables in a range of cells:

H1 = open
H2 = partial
H3 = closed

Then:

=AVERAGE(IF((A1:A7="no")*(B1:B7="full")*(ISNUMBER( MATCH(C1:C7,H1:H3,0))),D1:D7))

Don't forget, array entered!

--
Biff
Microsoft Excel MVP


"taurus99" wrote in message
...
Ok, so while I have you still (and I am learning so much tonight) ... I
came
upon another scenerio.

What if I wanted to include one or two more text words in a column.

In this example:

Average of D, if A = "NO", B = "FULL" and C = "open", "partial", or
"closed".
(I need the average of D2, D4 and D7)

A B C D
1 yes full open 27
2 no full partial 15
3 yes empty closed 31
4 no full open 19
5 no empty open 16
6 yes full closed 7
7 no full closed 10

In my real formula I put this, which I KNOW is wrong! LOL
E is the column which has 3 different items of text.

=AVERAGE(IF((D55:D6000=0)*(E55:E6000="PT
Flex")*(E55:E6000="PT")*(E55:E6000="FTLB")*(G55:G6 000="Y"),F55:F6000))


"T. Valko" wrote:

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"taurus99" wrote in message
...
Nevermind, I figured it out. WOO HOO

Thanks, you have been a huge help!!

"taurus99" wrote:

There are no empty cells, but that is good to know.

It worked PERFECTLY! Thanks!!

So, one more final question ... what is this array stuff all about? I
noticed I can't just copy my formula down the page and have excel
change
the
values for me like other formulas ...

"T. Valko" wrote:

Remove the quotes from around the 0:

=AVERAGE(IF((D55:D5000=0)*(E55:E5000="FTFB")*(G55: G5000="Y"),F55:F5000))

Make sure you array enter.

When you quote numbers, "0", Excel evaluates them as TEXT.

Now, this leads to another possible glitch. An *empty cell* will
evaluate to
0. To exclude empty cells from be evaluated (if they might be
present)
you
need to add another test:

=AVERAGE(IF((ISNUMBER(D55:D5000))*(D55:D5000=0)*(E 55:E5000="FTFB")*(G55:G5000="Y"),F55:F5000))


--
Biff
Microsoft Excel MVP


"taurus99" wrote in message
...
I copied that exact formula in and got a #DIV/0! error

D = numeric 0
E = Text FTFB
G = Text Y

"T. Valko" wrote:

AVERAGE(IF((D55:D5000="0"),(E55:E5000="FTFB"),(G5 5:G5000="Y"),F55:F5000))

Try it like this (array entered):

=AVERAGE(IF((D55:D5000="0")*(E55:E5000="FTFB")*(G5 5:G5000="Y"),F55:F5000))

Does D55:D5000 contain TEXT 0 or numeric 0?

In the formula you're testing for TEXT 0. TEXT 0 and numeric 0
are
not
the
same.


--
Biff
Microsoft Excel MVP


"taurus99" wrote in message
...
I tried that before I wrote back and it didn't work. When I hit
CTRL
SHIFT
ENTER I got an error message that reads:

"You've entered too many arguments for this function"

Any suggestions?

This is my formula ... (on my real spreadsheet)

=AVERAGE(IF((D55:D5000="0"),(E55:E5000="FTFB"),(G5 5:G5000="Y"),F55:F5000))

It's works just fine with just the "0" and "FTFB" but when I
add
the
3rd
one
"Y", it won't work.



"smartin" wrote:

Glad it worked!

You can just add your new constraint to the IF condition:

=average(if((a1:a5="no"),(b1:b5="full"),(c1:c5="op en"),d1:d5))

Again, this is an array formula, so Ctrl+Shift+Enter.

taurus99 wrote:
Thanks, smartin, that worked!!

One more question, what if I wanted to add one more column
in
there?

The average of Column D, ONLY IF A equals "no" and B equals
"full"
and
C
equals "open".

A B C D
1 yes full open 27
2 no full open 15
3 yes empty closed 31
4 no full open 16
5 no empty open 16


"smartin" wrote:

smartin wrote:
taurus99 wrote:
What formula do I use to find the average of certain
columns,
when
columns around it equal specific text?

example: I need the average of Column D, ONLY IF A
equals
"no"
and
B
equals "full". (in this case the average of D2 and D4)

A B C D
1 yes full 0 27
2 no full 3 15
3 yes empty 9 31
4 no full 1 16
5 no empty 7 16

Oops I should have written

=average(if((a1:a5="no"),(b1:b5="full"),d1:d5))

and mentioned this is an array formula (press
Ctrl+Shift+Enter
to
commit.)

You're quite welcome!

--
Biff
Microsoft Excel MVP


"taurus99" wrote in message
...
Unbelievable! Worked perfectly.

You are a life saver!!!!!!

Thank you so much and have a great day!

"T. Valko" wrote:

C = "open", "partial", or "closed".

If those 3 are the only possible entries then all you need to do is check
and make sure column C isn't empty:

=AVERAGE(IF((A1:A7="no")*(B1:B7="full")*(C1:C7<"" ),D1:D7))

If there are more possible entries than those 3...

List the variables in a range of cells:

H1 = open
H2 = partial
H3 = closed

Then:

=AVERAGE(IF((A1:A7="no")*(B1:B7="full")*(ISNUMBER( MATCH(C1:C7,H1:H3,0))),D1:D7))

Don't forget, array entered!

--
Biff
Microsoft Excel MVP


"taurus99" wrote in message
...
Ok, so while I have you still (and I am learning so much tonight) ... I
came
upon another scenerio.

What if I wanted to include one or two more text words in a column.

In this example:

Average of D, if A = "NO", B = "FULL" and C = "open", "partial", or
"closed".
(I need the average of D2, D4 and D7)

A B C D
1 yes full open 27
2 no full partial 15
3 yes empty closed 31
4 no full open 19
5 no empty open 16
6 yes full closed 7
7 no full closed 10

In my real formula I put this, which I KNOW is wrong! LOL
E is the column which has 3 different items of text.

=AVERAGE(IF((D55:D6000=0)*(E55:E6000="PT
Flex")*(E55:E6000="PT")*(E55:E6000="FTLB")*(G55:G6 000="Y"),F55:F6000))


"T. Valko" wrote:

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"taurus99" wrote in message
...
Nevermind, I figured it out. WOO HOO

Thanks, you have been a huge help!!

"taurus99" wrote:

There are no empty cells, but that is good to know.

It worked PERFECTLY! Thanks!!

So, one more final question ... what is this array stuff all about?
I
noticed I can't just copy my formula down the page and have excel
change
the
values for me like other formulas ...

"T. Valko" wrote:

Remove the quotes from around the 0:

=AVERAGE(IF((D55:D5000=0)*(E55:E5000="FTFB")*(G55: G5000="Y"),F55:F5000))

Make sure you array enter.

When you quote numbers, "0", Excel evaluates them as TEXT.

Now, this leads to another possible glitch. An *empty cell* will
evaluate to
0. To exclude empty cells from be evaluated (if they might be
present)
you
need to add another test:

=AVERAGE(IF((ISNUMBER(D55:D5000))*(D55:D5000=0)*(E 55:E5000="FTFB")*(G55:G5000="Y"),F55:F5000))


--
Biff
Microsoft Excel MVP


"taurus99" wrote in message
...
I copied that exact formula in and got a #DIV/0! error

D = numeric 0
E = Text FTFB
G = Text Y

"T. Valko" wrote:

AVERAGE(IF((D55:D5000="0"),(E55:E5000="FTFB"),(G5 5:G5000="Y"),F55:F5000))

Try it like this (array entered):

=AVERAGE(IF((D55:D5000="0")*(E55:E5000="FTFB")*(G5 5:G5000="Y"),F55:F5000))

Does D55:D5000 contain TEXT 0 or numeric 0?

In the formula you're testing for TEXT 0. TEXT 0 and numeric 0
are
not
the
same.


--
Biff
Microsoft Excel MVP


"taurus99" wrote in
message
...
I tried that before I wrote back and it didn't work. When I
hit
CTRL
SHIFT
ENTER I got an error message that reads:

"You've entered too many arguments for this function"

Any suggestions?

This is my formula ... (on my real spreadsheet)

=AVERAGE(IF((D55:D5000="0"),(E55:E5000="FTFB"),(G5 5:G5000="Y"),F55:F5000))

It's works just fine with just the "0" and "FTFB" but when I
add
the
3rd
one
"Y", it won't work.



"smartin" wrote:

Glad it worked!

You can just add your new constraint to the IF condition:

=average(if((a1:a5="no"),(b1:b5="full"),(c1:c5="op en"),d1:d5))

Again, this is an array formula, so Ctrl+Shift+Enter.

taurus99 wrote:
Thanks, smartin, that worked!!

One more question, what if I wanted to add one more
column
in
there?

The average of Column D, ONLY IF A equals "no" and B
equals
"full"
and
C
equals "open".

A B C D
1 yes full open 27
2 no full open 15
3 yes empty closed 31
4 no full open 16
5 no empty open 16


"smartin" wrote:

smartin wrote:
taurus99 wrote:
What formula do I use to find the average of certain
columns,
when
columns around it equal specific text?

example: I need the average of Column D, ONLY IF A
equals
"no"
and
B
equals "full". (in this case the average of D2 and
D4)

A B C D
1 yes full 0 27
2 no full 3 15
3 yes empty 9 31
4 no full 1 16
5 no empty 7 16

Oops I should have written

=average(if((a1:a5="no"),(b1:b5="full"),d1:d5))

and mentioned this is an array formula (press
Ctrl+Shift+Enter
to
commit.)