Find average using VLOOKUP across values if 1 or more VLOOKUPS = #N/A

[DonovanNeb]

Microsoft Office Professional 2013
Windows 7 Enterprise

I'm trying to average multiple columns of values, based on a VLOOKUP for all columns, but coming across errors when one of thsoe columns values is #N/A.

Here is my formula : =AVERAGE(VLOOKUP($S2,$A:$E,2,FALSE),VLOOKUP($S2,$H:$K,2,FALSE),VLOOKUP($S2,$M:$Q,2,FALSE))

First VLOOKUP($S2,$A:$E,2,FALSE) = 11
Second VLOOKUP($S2,$H:$K,2,FALSE) = 14
Third VLOOKUP($S2,$M:$Q,2,FALSE) = #N/A or doesn't exist

I've tried IFNA, but it replaces the #N/A with "" and that gives me a #VALUE, I'm assuming because "" is a character amongst numbers. I'd like to find an answer within 1 statement or command like above.

Thanks, Donovan

 

[Joe4]

Welcome to the Board!

I've tried IFNA, but it replaces the #N/A with "" and that gives me a #VALUE, I'm assuming because "" is a character amongst numbers.

How are you writing the formula?
Note the IFNA will return whatever value you tell it to in its place. With an average, you would need a numeric value. "" is text, and not numeric, and will cause errors.

How exactly do you want the average to work in your example?
Do you want it to treat the third one as 0 and return an average of 8.33, or do you only want it to average the first two values and return 12.5?

 

[DonovanNeb]

Welcome to the Board!


How are you writing the formula?
Note the IFNA will return whatever value you tell it to in its place. With an average, you would need a numeric value. "" is text, and not numeric, and will cause errors.

How exactly do you want the average to work in your example?
Do you want it to treat the third one as 0 and return an average of 8.33, or do you only want it to average the first two values and return 12.5?

Thanks for following up and the question for clarity..RE: average the first two values and return 12.5

I want it to ignore the #N/A, so do not replace the #N/A with 0, but ignore that Vlookup in the AVERAGE calculation.

 

[Joe4]

Unless someone can think up of a different way, it sounds like you may need to build your average manually, i.e.

=(IFERROR(VLOOKUP($S2,$A:$E,2,0),0)+IFERROR(VLOOKUP($S2,$H:$K,2,0),0)+IFERROR(VLOOKUP($S2,$M:$Q,2,0),0))/(IF(ISNA(VLOOKUP($S2,$A:$E,2,0)),0,1)+IF(ISNA(VLOOKUP($S2,$H:$K,2,0)),0,1)+IF(ISNA(VLOOKUP($S2,$M:$Q,2,0)),0,1))

 

[DonovanNeb]

Unless someone can think up of a different way, it sounds like you may need to build your average manually, i.e.

=(IFERROR(VLOOKUP($S2,$A:$E,2,0),0)+IFERROR(VLOOKUP($S2,$H:$K,2,0),0)+IFERROR(VLOOKUP($S2,$M:$Q,2,0),0))/(IF(ISNA(VLOOKUP($S2,$A:$E,2,0)),0,1)+IF(ISNA(VLOOKUP($S2,$H:$K,2,0)),0,1)+IF(ISNA(VLOOKUP($S2,$M:$Q,2,0)),0,1))

I see what you did there!! I always enjoy straight forward answers, even if it seems like the simple way out, it's just a different way to look at things. This works, thanks. I'll wait to see if others chime in before clicking the check mark for solution. Thanks again.

 

[Joe4]

You are welcome.

 

[Eric W]

There is actually a pretty easy/short way to do it, but it comes with some big caveats:

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1																			12.5
2																			x
3
4	x	11
5
6								x	14
7													y	3
8
Sheet4

Cell Formulas
Range	Formula
S1	S1	=AVERAGEIF(A:M,S2,B:N)

Caveat 1: if you have the S2 value in more than one row in a column, like if A6 has x and B6 has 22, then both the B4 and B6 values would be included in the average.

Caveat 2: if you have the S2 value in any of the other columns (C:G or J:L) with a value to its right, that will be included too.

But depending on how your worksheet is set up, this might work for you.