using AVERAGE on cells that may contain the #N/A error...???

C

Canadian_Newb

New Member
Joined
Jun 30, 2011
Messages
31
I have a quick question (and you all here never fail me, so thank-you for that!):
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
I’m trying to run an AVERAGE formula on an array of cells that themselves run a VLOOKUP function.
<o:p></o:p>
Now, what is happening is the VLOOKUP array cells have the N/A error in them (until data is input in another sheet). I understand that the AVERAGE function will not work with N/A errors but is there any workaround for this?
<o:p></o:p>
Because when I input data in cells that transfer a number to the VLOOKUP cells, the AVERAGE cell remains N/A…and will not pull an average of the numbers inserted.
<o:p></o:p>
Is there a workaround for this?
<o:p></o:p>
My formulas (for example):
<o:p></o:p>
Cell: C5
=AVERAGE(D5:N5)
<o:p></o:p>
Cell: D5
=VLOOKUP("USERNAME",'SCORE SHEET'!B7:K18,10,FALSE)
<o:p></o:p>
Is there another formula that I can use to accomplish acquiring the average from an array of cells that may contain N/A errors?
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Sort by date Sort by votes
Try

=AVERAGE(IF(ISNUMBER(D5:N5),D5:N5))

confirmed with CTRL + Shift + Enter, not just Enter.
 
Upvote 0
Canadian_Newb said:
I have a quick question (and you all here never fail me, so thank-you for that!):
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
I’m trying to run an AVERAGE formula on an array of cells that themselves run a VLOOKUP function.
<o:p></o:p>
Now, what is happening is the VLOOKUP array cells have the N/A error in them (until data is input in another sheet). I understand that the AVERAGE function will not work with N/A errors but is there any workaround for this?
<o:p></o:p>
Because when I input data in cells that transfer a number to the VLOOKUP cells, the AVERAGE cell remains N/A…and will not pull an average of the numbers inserted.
<o:p></o:p>
Is there a workaround for this?
<o:p></o:p>
My formulas (for example):
<o:p></o:p>
Cell: C5
=AVERAGE(D5:N5)
<o:p></o:p>
Cell: D5
=VLOOKUP("USERNAME",'SCORE SHEET'!B7:K18,10,FALSE)
<o:p></o:p>
Is there another formula that I can use to accomplish acquiring the average from an array of cells that may contain N/A errors?
Click to expand...
One way...

Book1
CDEFGHIJKLMN
4Average___________
562.755587#N/A4772#N/A885158#N/A44
Sheet1

This array formula** entered in C5:

=IF(COUNT(D5:N5),AVERAGE(IF(ISNUMBER(D5:N5),D5:N5)),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
 
Upvote 0
Not tested, but could you modify your vlookup to display a "0" in the event of an N/A result and then use AverageIf to exclude "0" cells? Or is a 0 a valid result in some cases?
 
Upvote 0
T. Valko said:
One way...

Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Verdana,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=0><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 63px"><COL style="WIDTH: 35px"><COL style="WIDTH: 35px"><COL style="WIDTH: 35px"><COL style="WIDTH: 35px"><COL style="WIDTH: 35px"><COL style="WIDTH: 35px"><COL style="WIDTH: 35px"><COL style="WIDTH: 35px"><COL style="WIDTH: 35px"><COL style="WIDTH: 35px"><COL style="WIDTH: 35px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD><TD>K</TD><TD>L</TD><TD>M</TD><TD>N</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">Average</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">62.75</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">55</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">87</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">#N/A</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">47</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">72</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">#N/A</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">88</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">51</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">58</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">#N/A</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">44</TD></TR></TBODY></TABLE>


This array formula** entered in C5:

=IF(COUNT(D5:N5),AVERAGE(IF(ISNUMBER(D5:N5),D5:N5)),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
Click to expand...
The array formula will work in ANY version of Excel.

If you're using Excel 2007 or later, then:

=IFERROR(AVERAGEIF(D5:N5,"<1E100"),"")
 
Upvote 0
I tried V.G and T. Valko’s suggestions and they both seem to work fine.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p> </o:p>
Snag though – when I extend the array to AO5, I get a 0 as the average.
<o:p> </o:p>
So - =AVERAGE(IF(ISNUMBER(D5:N5),D5:N5)) gives me a result (did not know about the CRTL + SHIFT + ENTER stuff, thanks so much for that!) but =AVERAGE(IF(ISNUMBER(D5:AO5),D5:AO5)) gives me a 0<o:p></o:p>
<o:p> </o:p>
Same with: =IF(COUNT(D5:N5),AVERAGE(IF(ISNUMBER(D5:N5),D5:N5)),""). Gives me the average, but when I extend to =IF(COUNT(D5:AO5),AVERAGE(IF(ISNUMBER(D5:AO5),D5:AO5)),"") I get the 0.<o:p></o:p>
<o:p> </o:p>
Is it because of the limit of 30 cells for an average?<o:p></o:p>
<o:p> </o:p>
Thanks so much!

oh and I'm using Excel 2003.
 
Upvote 0
When you specify a range it can be as large as you like - the limit of 30 only applies to separate arguments, e.g. if you used

=AVERAGE(A2,B2,C2....

listing cells separately then you are limited to 30.

In this case the formula should work, even with the larger range - are you sure you used CTL+SHIFT+ENTER.....you have to do that again when you change the formula......you'll get brackets like { and } around the formula if you've done it correctly

You can also use a "non-array" formula for Excel 2003, i.e.

=SUMIF(D5:AO5,"<>N/A")/MAX(1,COUNT(D5:AO5))
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Possible Use of CountIF and AVERAGE Functions
Replies
1
Views
178
Denny57
D
D
  • Question Question
VBA Code to Apply an AVERAGE of a Range on Cells as New Records are added Via a User Form
Replies
2
Views
88
Denny57
D
B
  • Solved
VLOOKUP issue with wildcard parameter for partial matches in number/letter combinations
2
Replies
10
Views
908
BrooksProctor
B
msword
  • Solved
=AVERAGE: How to exclude cell with errors completely?
Replies
4
Views
583
etaf
etaf
R
  • Solved
Remove unwanted #N/A errors in this LAMBDA/REDUCE formula
2
Replies
11
Views
649
Rnkhch
R

Forum statistics

Threads
1,224,537
Messages
6,179,408
Members
452,912
Latest member
alicemil

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top