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