Average if ...

[taurus99]

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

 

[T. Valko]

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

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

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

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

 

[T. Valko]

Forgot something:

Try this array formula**

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

 

[taurus99]

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

 

[taurus99]

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((D555000="0"),(E55:E5000="FTFB"),(G55: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.

 

[T. Valko]

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

 

[T. Valko]

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

Try it like this (array entered):

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

Does D555000 contain TEXT 0 or numeric 0?

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

 

[taurus99]

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

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

 

[T. Valko]

Remove the quotes from around the 0:

=AVERAGE(IF((D555000=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(D555000))*(D555000=0)*(E55:E5000="FTFB")*(G55:G5000="Y"),F55:F5000))

 

[taurus99]

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

 

[taurus99]

Nevermind, I figured it out. WOO HOO

Thanks, you have been a huge help!!

 

[T. Valko]

You're welcome. Thanks for the feedback!

 

[taurus99]

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((D556000=0)*(E55:E6000="PT
Flex")*(E55:E6000="PT")*(E55:E6000="FTLB")*(G55:G6000="Y"),F55:F6000))

 

[T. Valko]

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<>""),D17))

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))),D17))

Don't forget, array entered!

 

[taurus99]

Unbelievable! Worked perfectly.

You are a life saver!!!!!!

Thank you so much and have a great day!

 

[T. Valko]

You're quite welcome!