It makes no sense to use VLOOKUP for that. Instead, use =AVERAGEIF(B2:B55,F2,C2:C55) Rai_Sahab VLOOKUP is not a suitable function for calculating an average. The exercise makes no sense.
Dave_Moyse Let's say the numbers are in A2:A21. As an ordinary formula: =AVERAGE(SMALL(A2:A21,{1,2,3,4,5,6,7,8})) or =AVERAGEIF(A2:A21,"<="&SMALL(A2:A21,8)) As an array formula confirmed with Ctrl+Shift+Enter: =AVERAGE(SMALL(A2:A21,ROW(1:8)))...
New functions supported are : ACCRINT, ACCRINTM, AMORDEGRC, AMORLINC, AVERAGEIF, AVERAGEIFS, BAHTTEXT, BESSELI, BESSELJ, BESSELK, BESSELY, BIN2DEC, BIN2HEX, BIN2OCT, CORREL, COMPLEX, CONVERT, COUNTIFS, COUPDAYBS, COUPDAYS, COUPDAYSNC, COUPNCD, COUPNUM, COUPPCD, CUBEKPIMEMBER, CUBEMEMBER, ...
=AVERAGE(IF(IF(ISNUMBER(A1:A9),B1:B9,0)=0,"",IF(ISNUMBER(A1:A9),B1:B9,0))) Please note that you need to pressCtrl+Shift+Enterto enter this formula not onlyEnterbecause it's an array formula. Hope that helps Hi, Do you want to calculate the average for on...
Let's say the numbers are in A2:A21. As an ordinary formula: =AVERAGE(SMALL(A2:A21,{1,2,3,4,5,6,7,8})) or =AVERAGEIF(A2:A21,"<="&SMALL(A2:A21,8)) As an array formula confirmed with Ctrl+Shift+Enter: =AVERAGE(SMALL(A2:A21,ROW(1:8)))...