| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Calculating Average based on 3 filled cells from left in a row | Excel Discussion (Misc queries) | |||
| Calculating the Average for non consecutive cells using custom for | Excel Discussion (Misc queries) | |||
| calculating average with blank cells | Excel Worksheet Functions | |||
| Calculating the average of cells | New Users to Excel | |||
| How do I ignore cells with errors when calculating an average? | Excel Worksheet Functions | |||
Hybrid Mode
