Calculating Average of Certain Cells

  • Thread starter Thread starter Tags
  • Start date Start date
T

Tags

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?
 
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
 
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
 
hi
how about 2 formulas in one.....
=SUMIF(B2:B10,"X",C2:C10)/COUNTIF(B2:B10,"X")

adjust ranges to suit
Regards
FSt1
 
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?
 
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
 
Thanks. It worked!!! I was hitting CTRL, SHift and Enter before I entered
the formula. WOOOOOOOOOHOOOOOOOOOO!!!!
 
Back
Top