Average if
 

Hi. Is there a way to do an average if? Basically, if the contents of
column A is "Y", then include the contents on column B in the average?
Thanks!

Average if
 

HI Steph,

Isn't this a SUMIF divided by a COUNTIF?

Ric

Average if
 

=IF(A1:A100="Y",B1:B100)

this is an array formula, so commit with Ctrl-Shift-Enter. It also means
that you cannot use a whole column, just a specific range.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Steph" wrote in message
...
Hi. Is there a way to do an average if? Basically, if the contents of
column A is "Y", then include the contents on column B in the average?
Thanks!

Average if
 

Thanks Bob, but I can't get it to work. It looks like the formula only
takes into account the first record in the array. Am I doing something
wrong? Thanks!

"Bob Phillips" wrote in message
...
=IF(A1:A100="Y",B1:B100)

this is an array formula, so commit with Ctrl-Shift-Enter. It also means
that you cannot use a whole column, just a specific range.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Steph" wrote in message
...
Hi. Is there a way to do an average if? Basically, if the contents of
column A is "Y", then include the contents on column B in the average?
Thanks!

Average if
 

Steph,

Use the following array formula. Change the ranges to suit your
needs.

=AVERAGE(IF(A1:A10="Y",B1:B10,FALSE))

Since this is an array formula, you must press Ctrl+Shift+Enter
rather than just Enter when you first enter the formula and
whenever you edit it later. If you do this properly, Excel will
display the formula enclosed it curly braces {}.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"Steph" wrote in message
...
Thanks Bob, but I can't get it to work. It looks like the
formula only
takes into account the first record in the array. Am I doing
something
wrong? Thanks!

"Bob Phillips" wrote in
message
...
=IF(A1:A100="Y",B1:B100)

this is an array formula, so commit with Ctrl-Shift-Enter. It
also means
that you cannot use a whole column, just a specific range.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Steph" wrote in message
...
Hi. Is there a way to do an average if? Basically, if the
contents of
column A is "Y", then include the contents on column B in
the average?
Thanks!

Average if
 

Oops, sorry I forgot the AVERAGE

=AVERAGE(IF(A1:A100="Y",B1:B100))

still an array formula.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Steph" wrote in message
...
Thanks Bob, but I can't get it to work. It looks like the formula only
takes into account the first record in the array. Am I doing something
wrong? Thanks!

"Bob Phillips" wrote in message
...
=IF(A1:A100="Y",B1:B100)

this is an array formula, so commit with Ctrl-Shift-Enter. It also means
that you cannot use a whole column, just a specific range.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Steph" wrote in message
...
Hi. Is there a way to do an average if? Basically, if the contents
of
column A is "Y", then include the contents on column B in the average?
Thanks!