Formula to figure an average on a range of cells with an "if" form
 

I have a spreadsheet that has a formula in column g that is an "if" formula
for the data in column f. I need to get an average of the data in column
"g". For example

column f column g
yellow 2
green 3
red 1
red 1
red 1
average yellow need formula for this

help!

Formula to figure an average on a range of cells with an "if" form
 

Do you mean like average()?

=Average(G1:G5)

Hope this helps, Jim
--
I appreciate any feedback.


"lucyo912" wrote:

I have a spreadsheet that has a formula in column g that is an "if" formula
for the data in column f. I need to get an average of the data in column
"g". For example

column f column g
yellow 2
green 3
red 1
red 1
red 1
average yellow need formula for this

help!

Formula to figure an average on a range of cells with an "if" form
 

Hi,

Try this

=AVERAGE(IF(F1:F20="Yellow",G1:G20,FALSE))

This is an ARRAY formula so commit the formula by pressing Ctrl+Shift+Enter
NOT just Enter. If you do it correctly Excel will put curly brackets around
it {}. You can't type these yourself.

Mike


"lucyo912" wrote:

I have a spreadsheet that has a formula in column g that is an "if" formula
for the data in column f. I need to get an average of the data in column
"g". For example

column f column g
yellow 2
green 3
red 1
red 1
red 1
average yellow need formula for this

help!

Formula to figure an average on a range of cells with an "if" form
 

On Tue, 5 Aug 2008 11:40:00 -0700, lucyo912
wrote:

I have a spreadsheet that has a formula in column g that is an "if" formula
for the data in column f. I need to get an average of the data in column
"g". For example

column f column g
yellow 2
green 3
red 1
red 1
red 1
average yellow need formula for this

help!

Try this formula in cell G6:

=AVERAGEIF(F1:F5, "=yellow", G1:G5)

Hope this helps / Lars-Åke

Formula to figure an average on a range of cells with an "if"
 

Hi,

I guess that's an Excel 2007 enhancement?

Mike

"Lars-Ã…ke Aspelin" wrote:

On Tue, 5 Aug 2008 11:40:00 -0700, lucyo912
wrote:

I have a spreadsheet that has a formula in column g that is an "if" formula
for the data in column f. I need to get an average of the data in column
"g". For example

column f column g
yellow 2
green 3
red 1
red 1
red 1
average yellow need formula for this

help!

Try this formula in cell G6:

=AVERAGEIF(F1:F5, "=yellow", G1:G5)

Hope this helps / Lars-Ã…ke

Formula to figure an average on a range of cells with an "if"
 

nope - no dice - get the #DIV/0! error

"Mike H" wrote:

Hi,

Try this

=AVERAGE(IF(F1:F20="Yellow",G1:G20,FALSE))

This is an ARRAY formula so commit the formula by pressing Ctrl+Shift+Enter
NOT just Enter. If you do it correctly Excel will put curly brackets around
it {}. You can't type these yourself.

Mike


"lucyo912" wrote:

I have a spreadsheet that has a formula in column g that is an "if" formula
for the data in column f. I need to get an average of the data in column
"g". For example

column f column g
yellow 2
green 3
red 1
red 1
red 1
average yellow need formula for this

help!

Formula to figure an average on a range of cells with an "if"
 

No need for the FALSE part in the formula, regardless this will return a div
error if there are no yellow in F1:F20
so make sure you don't have extra spaces etc in F1:F20 where it looks like
you have yellow

--


Regards,


Peo Sjoblom

"lucyo912" wrote in message
...
nope - no dice - get the #DIV/0! error

"Mike H" wrote:

Hi,

Try this

=AVERAGE(IF(F1:F20="Yellow",G1:G20,FALSE))

This is an ARRAY formula so commit the formula by pressing
Ctrl+Shift+Enter
NOT just Enter. If you do it correctly Excel will put curly brackets
around
it {}. You can't type these yourself.

Mike


"lucyo912" wrote:

I have a spreadsheet that has a formula in column g that is an "if"
formula
for the data in column f. I need to get an average of the data in
column
"g". For example

column f column g
yellow 2
green 3
red 1
red 1
red 1
average yellow need formula for this

help!