Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Averaging with #N/A Cells
i'm trying to average 5 cells. One has an #n/a value. This causes the
average to say #n/a. Is there a way to correct this? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Averaging with #N/A Cells
Use the following array formula:
=AVERAGE(IF(ISNA(A1:A5),FALSE,A1:A5)) Of course, change both occurrences of A1:A5 to your actual range. Since this is an array formula, you must press CTRL SHIFT ENTER rather than just ENTER when you first enter the formula and whenever you edit it later. If you do this properly, Excel will display the formula in the formula bar enclosed in curly braces { }. See www.cpearson.com/Excel/ArrayFormulas.aspx for much more information about array formulas. -- Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2008 Pearson Software Consulting, LLC www.cpearson.com (email on web site) "tylermdsm" wrote in message ... i'm trying to average 5 cells. One has an #n/a value. This causes the average to say #n/a. Is there a way to correct this? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Averaging with #N/A Cells
One way
In B1, array-enter by pressing CTRL+SHIFT+ENTER, instead of just pressing ENTER: =AVERAGE(IF(ISNUMBER(A1:A10),A1:A10)) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "tylermdsm" wrote: i'm trying to average 5 cells. One has an #n/a value. This causes the average to say #n/a. Is there a way to correct this? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Averaging with #N/A Cells
=AVERAGE(IF(ISNA(A1:A5),"",A1:A5)) entered as an *array formula* (Control
Shift Enter). -- David Biddulph "tylermdsm" wrote in message ... i'm trying to average 5 cells. One has an #n/a value. This causes the average to say #n/a. Is there a way to correct this? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Averaging with #N/A Cells
One mo
=SUMIF(A1:A5,"<"&1E100)/COUNT(A1:A5) -- Biff Microsoft Excel MVP "tylermdsm" wrote in message ... i'm trying to average 5 cells. One has an #n/a value. This causes the average to say #n/a. Is there a way to correct this? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
averaging cells | Excel Discussion (Misc queries) | |||
Averaging 14 Cells | Excel Discussion (Misc queries) | |||
Averaging Cells Based On Conditions in Neighboring Cells | Excel Discussion (Misc queries) | |||
Averaging Cells | Excel Discussion (Misc queries) | |||
Averaging cells which contain #DIV/0! | Excel Worksheet Functions |