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

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.