Calculating Average of Certain Cells
 

I want to find the average time of cells that only have X in them. For
example:
Name Comercial TIME
Belair X 0:30
York 0:15
Dulaney X 0:45
Providence 0:15
Joppa X 0:55

What formula do I use?

Calculating Average of Certain Cells
 

Hi,

Try this ARRAY formula

=AVERAGE(IF(B1:B20="X",C1:C20))

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.

Mike
"Tags" wrote:

I want to find the average time of cells that only have X in them. For
example:
Name Comercial TIME
Belair X 0:30
York 0:15
Dulaney X 0:45
Providence 0:15
Joppa X 0:55

What formula do I use?

Calculating Average of Certain Cells
 

MikeH has given you a great answer but if you are using Excel 2070 a better
way is to use AVERAGEIF which does not need to be array entered
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"Tags" wrote in message
...
I want to find the average time of cells that only have X in them. For
example:
Name Comercial TIME
Belair X 0:30
York 0:15
Dulaney X 0:45
Providence 0:15
Joppa X 0:55

What formula do I use?

Calculating Average of Certain Cells
 

hi
how about 2 formulas in one.....
=SUMIF(B2:B10,"X",C2:C10)/COUNTIF(B2:B10,"X")

adjust ranges to suit
Regards
FSt1

"Tags" wrote:

I want to find the average time of cells that only have X in them. For
example:
Name Comercial TIME
Belair X 0:30
York 0:15
Dulaney X 0:45
Providence 0:15
Joppa X 0:55

What formula do I use?

Calculating Average of Certain Cells
 

It didn't work. It didn't put the {} around it. I did what you said to do.
Do I have to change the format of the X cells to text or number?

"Mike H" wrote:

Hi,

Try this ARRAY formula

=AVERAGE(IF(B1:B20="X",C1:C20))

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.

Mike
"Tags" wrote:

I want to find the average time of cells that only have X in them. For
example:
Name Comercial TIME
Belair X 0:30
York 0:15
Dulaney X 0:45
Providence 0:15
Joppa X 0:55

What formula do I use?

Calculating Average of Certain Cells
 

Hi.

Paste the formula in to formula bar and alter the ranges to suit your needs
THEN
press and hold down CTRL and SHIFT key and tap ENTER

Mike

"Tags" wrote:

It didn't work. It didn't put the {} around it. I did what you said to do.
Do I have to change the format of the X cells to text or number?

"Mike H" wrote:

Hi,

Try this ARRAY formula

=AVERAGE(IF(B1:B20="X",C1:C20))

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.

Mike
"Tags" wrote:

I want to find the average time of cells that only have X in them. For
example:
Name Comercial TIME
Belair X 0:30
York 0:15
Dulaney X 0:45
Providence 0:15
Joppa X 0:55

What formula do I use?

Calculating Average of Certain Cells
 

Thanks. It worked!!! I was hitting CTRL, SHift and Enter before I entered
the formula. WOOOOOOOOOHOOOOOOOOOO!!!!

"Mike H" wrote:

Hi.

Paste the formula in to formula bar and alter the ranges to suit your needs
THEN
press and hold down CTRL and SHIFT key and tap ENTER

Mike

"Tags" wrote:

It didn't work. It didn't put the {} around it. I did what you said to do.
Do I have to change the format of the X cells to text or number?

"Mike H" wrote:

Hi,

Try this ARRAY formula

=AVERAGE(IF(B1:B20="X",C1:C20))

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.

Mike
"Tags" wrote:

I want to find the average time of cells that only have X in them. For
example:
Name Comercial TIME
Belair X 0:30
York 0:15
Dulaney X 0:45
Providence 0:15
Joppa X 0:55

What formula do I use?

Calculating Average of Certain Cells
 

You seem please, glad I could help

"Tags" wrote:

Thanks. It worked!!! I was hitting CTRL, SHift and Enter before I entered
the formula. WOOOOOOOOOHOOOOOOOOOO!!!!

"Mike H" wrote:

Hi.

Paste the formula in to formula bar and alter the ranges to suit your needs
THEN
press and hold down CTRL and SHIFT key and tap ENTER

Mike

"Tags" wrote:

It didn't work. It didn't put the {} around it. I did what you said to do.
Do I have to change the format of the X cells to text or number?

"Mike H" wrote:

Hi,

Try this ARRAY formula

=AVERAGE(IF(B1:B20="X",C1:C20))

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.

Mike
"Tags" wrote:

I want to find the average time of cells that only have X in them. For
example:
Name Comercial TIME
Belair X 0:30
York 0:15
Dulaney X 0:45
Providence 0:15
Joppa X 0:55

What formula do I use?