remove #N/A from average

[chriswrcg]

I have a spreadsheet that I want to calculate sum and average on. I find my numbers and percentages with VLookup with no issues. However if there is no data to return I get the #N/A error message returned. I do not just want to add an iferror tag to my vlookup because it will at 0 to my average and create a false average. The problem is that for some instances 0 will be the correct number and should be calculated into the average and other times whtn there is simply no data to return I can have the field being populated by "false 0"'s

The simple solution would be to have the vlookup not return the #N/A message at all but leave the field blank when there is nothing to return. I found this formula but I can't make it work. Excel keeps telling me there is something wrong with it. Generally in the quotation marks area.
What can I do to make this work and leave the cell blank when there is nothing to return?

Please Login or Register  to view this content.

[jeffreybrown]

Did you try? =IFERROR(VLOOKUP(A1,myRange,1,FALSE),"")

I know you say you don't want to use IFERROR, but it answers the quote below

The simple solution would be to have the vlookup not return the #N/A message at all but leave the field blank when there is nothing to return.

[Fluff13]

Another option is to use Aggregate
https://exceljet.net/excel-functions...egate-function

[chriswrcg]

I found a solution but at the same time created another problem. The formula that I used was this.

Please Login or Register  to view this content.

The problem was that I could not still average the percentages without an error. The error I was getting was #dev/o. I realized I was getting that since the percentages being returned were not numbers but rather text. I added a +0 to the end of all the vlookups and turned everything back to numbers. now I am getting #value! so I still can't average anything. Here is my workbook. What can I do now?

[AliGW]

Try this:

=IFERROR(VLOOKUP($A8,'Coaching data '!$A:$J,8,0),"")+0,"")

[Fluff13]

Or change your existing formula to
=IFNA(VLOOKUP($A$8,'Coaching data '!$A:$J,8,0)+0,"")

[chriswrcg]

that is not working. there is one to many close parenthesis. And adding an open parenthesis at the beginning does not help.

[AliGW]

that is not working. there is one to many close parenthesis

Sorry - this:

=IFERROR(VLOOKUP($A8,'Coaching data '!$A:$J,8,0)+0,"")

[AliGW]

Have you got it working now? If so, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.