Average if ...

T

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

T. Valko

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")
 
S

smartin

taurus99 said:
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
Click to expand...

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

smartin

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

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
 
S

smartin

Glad it worked!

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

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

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

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((D55:D5000="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

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

T. Valko

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

Try it like this (array entered):

=AVERAGE(IF((D55:D5000="0")*(E55:E5000="FTFB")*(G55: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.
 
T

taurus99

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

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

T. Valko

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

smartin

T. Valko said:
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))
Click to expand...

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

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

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

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<>""),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!
 
T

taurus99

Unbelievable! Worked perfectly.

You are a life saver!!!!!!

Thank you so much and have a great day!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Average problem 1
how to specify the first cell with data in it inside an if statement 1
Changing Average - Ongoing for Excel 2007 2
Need some PWA help 0
Making inputs for average formula dynamic 1
Formula not working properly 2
Help!! find two number recurring numbers in row?? 1
Countif, IF condition is met 2

Top