Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate AVERAGe with #MISSING!
Hi! In Excel I try to calculate AVERAGE by using the normal function
=AVERAGE(cell1:cell2). However if I have a cell that says #MISSING! the average also becomes #MISSING!. I have asked this question before but then I got the answer that Excel is able to deal with that and that I should be able to calcualte the average anyway. That is not the case with my Excel (and neither for my coworkers so it is not just my computer:-)). I have a Swedish Excel (and yes I write MEDEL instead of AVERAGE..) version 2003 (part of Microsoft Office Professional Edition 2003). I would be most grateful for any help. This is not just related to calculating AVERAGE but all sorts of quick things you want to do. I know there are ways to gop around it (like search and replace) but that takes time. PLs help! Thanks! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate AVERAGe with #MISSING!
Maybe this
=AVERAGE(IF(ISNUMBER(A1:A4),A1:A4)) 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 "Arne Hegefors" wrote: Hi! In Excel I try to calculate AVERAGE by using the normal function =AVERAGE(cell1:cell2). However if I have a cell that says #MISSING! the average also becomes #MISSING!. I have asked this question before but then I got the answer that Excel is able to deal with that and that I should be able to calcualte the average anyway. That is not the case with my Excel (and neither for my coworkers so it is not just my computer:-)). I have a Swedish Excel (and yes I write MEDEL instead of AVERAGE..) version 2003 (part of Microsoft Office Professional Edition 2003). I would be most grateful for any help. This is not just related to calculating AVERAGE but all sorts of quick things you want to do. I know there are ways to gop around it (like search and replace) but that takes time. PLs help! Thanks! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate AVERAGe with #MISSING!
Hi,
You can try this as well SUMIF(range,"=-9.9999E307")/COUNTIF(range,"=-9.9999E307") -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Arne Hegefors" wrote in message ... Hi! In Excel I try to calculate AVERAGE by using the normal function =AVERAGE(cell1:cell2). However if I have a cell that says #MISSING! the average also becomes #MISSING!. I have asked this question before but then I got the answer that Excel is able to deal with that and that I should be able to calcualte the average anyway. That is not the case with my Excel (and neither for my coworkers so it is not just my computer:-)). I have a Swedish Excel (and yes I write MEDEL instead of AVERAGE..) version 2003 (part of Microsoft Office Professional Edition 2003). I would be most grateful for any help. This is not just related to calculating AVERAGE but all sorts of quick things you want to do. I know there are ways to gop around it (like search and replace) but that takes time. PLs help! Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculate average with missing values | Excel Worksheet Functions | |||
How to Calculate Average | Excel Discussion (Misc queries) | |||
CALCULATE WITH AVERAGE ???? | Excel Discussion (Misc queries) | |||
Calculate the number of days between two dates,missing weekends? | Excel Worksheet Functions | |||
If/Then calculate the average | Excel Worksheet Functions |