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

DonovanNeb

New Member
Joined
Jan 4, 2021
Messages
3
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,972
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jan 4, 2021
Messages
3
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,972
Office Version
  1. 365
Platform
  1. Windows
Unless someone can think up of a different way, it sounds like you may need to build your average manually, i.e.

Rich (BB code):
=(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))
 
Solution

DonovanNeb

New Member
Joined
Jan 4, 2021
Messages
3
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows

ADVERTISEMENT

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

Rich (BB code):
=(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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,972
Office Version
  1. 365
Platform
  1. Windows
You are welcome.
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,640
There is actually a pretty easy/short way to do it, but it comes with some big caveats:

Book2
ABCDEFGHIJKLMNOPQRS
112.5
2x
3
4x11
5
6x14
7y3
8
Sheet4
Cell Formulas
RangeFormula
S1S1=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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,206
Messages
5,600,317
Members
414,376
Latest member
NickYOW

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
Top