AVERAGE with double quotes ("")

Hi everyone. I'm looking for a way to make this work:

=AVERAGE("","",5,"","")

That function returns a #VALUE error, presumably because Excel treats the double quotes as strings and it doesn't know how to handle them. But I don't want to replace them with zeros, either, because then my average becomes 1.25 instead of 5.

Basically what I'm doing is I have an AVERAGE function that has formulas (VLOOKUPS) that check other sheets for values that may or may not find anything. If it doesn't find anything, then it returns the double quotes for the value to average. If it does, then it returns the value to be averaged. I've evaluated the formula, and the very last step looks exactly like what I've shown above with the double quotes, but it chokes at the end.

Unfortunately, I don't think using an array here is going to work either.

Any help would be appreciated.

You might need to use the AVERAGEIF finction.

Will you please attach a sample Excel workbook?

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

This should return 5

Formula:

Please Login or Register  to view this content.

I appreciate the quick responses! I've tried to attach a file. Not sure if it worked or not...

Anyway, the problem I'm having is that I'm pulling from multiple sheets in the workbook, so using an AVERAGEIF would be tricky, as would ranges of any sort (though correct me if you see a better way). The full formula looks like this:

=AVERAGE(IF(ISERROR(VLOOKUP($A2,'2013ASP'!$A:$F,COLUMN(),FALSE)),"",VLOOKUP($A2,'2013ASP'!$A:$F,COLUMN(),FALSE)),IF(ISERROR(VLOOKUP($A2,'2014ASP'!$A:$F,COLUMN(),FALSE)),"",VLOOKUP($A2,'2014ASP'!$A:$F,COLUMN(),FALSE)),IF(ISERROR(VLOOKUP($A2,'2015ASP'!$A:$F,COLUMN(),FALSE)),"",VLOOKUP($A2,'2015ASP'!$A:$F,COLUMN(),FALSE)),IF(ISERROR(VLOOKUP($A2,'2016ASP'!$A:$F,COLUMN(),FALSE)),"",VLOOKUP($A2,'2016ASP'!$A:$F,COLUMN(),FALSE)),IF(ISERROR(VLOOKUP($A2,'2017ASP'!$A:$F,COLUMN(),FALSE)),"",VLOOKUP($A2,'2017ASP'!$A:$F,COLUMN(),FALSE)))

Thanks again for the help.

Originally Posted by AlKey

This should return 5

Formula:

Please Login or Register  to view this content.

I did try this within my formula, crossing my fingers along the way, but no luck. It looked like this:

=AVERAGE({IF(ISERROR(VLOOKUP($A2,'2013ASP'!$A:$F,COLUMN(),FALSE)),"",VLOOKUP($A2,'2013ASP'!$A:$F,COLUMN(),FALSE)),IF(ISERROR(VLOOKUP($A2,'2014ASP'!$A:$F,COLUMN(),FALSE)),"",VLOOKUP($A2,'2014ASP'!$A:$F,COLUMN(),FALSE)),IF(ISERROR(VLOOKUP($A2,'2015ASP'!$A:$F,COLUMN(),FALSE)),"",VLOOKUP($A2,'2015ASP'!$A:$F,COLUMN(),FALSE)),IF(ISERROR(VLOOKUP($A2,'2016ASP'!$A:$F,COLUMN(),FALSE)),"",VLOOKUP($A2,'2016ASP'!$A:$F,COLUMN(),FALSE)),IF(ISERROR(VLOOKUP($A2,'2017ASP'!$A:$F,COLUMN(),FALSE)),"",VLOOKUP($A2,'2017ASP'!$A:$F,COLUMN(),FALSE))})

Originally Posted by JohnTopley

Different approach:

in G2

=IFERROR(SUMPRODUCT(SUMIF(INDIRECT("'"&Sheets&"'!A:A"),$A2,INDIRECT("'"&Sheets&"'!B:B")))/SUMPRODUCT(COUNTIFS(INDIRECT("'"&Sheets&"'!A:A"),$A2,INDIRECT("'"&Sheets&"'!B:B"),"<>")),0)

Copy down

Replace B:B with C:C, D:D as you copy across

Named range called Sheets

Awesome, thanks JohnTopley. That looks like it will work - much appreciated!