excel - AVG of Cells Next to Cells used in Another Formula -

i new asking questions here hope correct. helping dad spreadsheet , i'm having issues figuring out how 1 formula. dont know if can done formula or if has done macros.

this scoring sheet multiple matches. each match there total score , cell next score x count (number of bulleyes). in same row (column k) calculate top 6 total scores , average them:

=average(large((n15,q15,t15,w15,z15,ac15,af15,ai15,al15,ao15,ar15,au15,ax15,ba15,bd15,bg15,bj15),{1,2,3,4,5,6}))

now need take avg of x counts next total scores used in formula above , put solution in column l.

for example, if cells used avg score in row are:

n15,q15,t15,w15,z15,ac15

then cells need used x count avg be:

o15,r15,u15,x15,aa15,ad15

this result put l15

please help. if clarification needed let me know.

screen shot:

please try following formula:

=sumproduct(o15:bm15, --(mod(column(n15:bl15)-column($n15),3)=0), --(n15:bl15+o15:bm15/10^3+column(n15:bl15)/10^6>=    large(n15:bl15+o15:bm15/10^3+column(n15:bl15)/10^6,6))    )/6 

how work?

sumproduct has 3 parameters - first array sum, next 2 parameters return array of 0 , 1 choose interesting elements of first array.

• mod(column(n15:bl15)-column($n15),3)=0)

this part included avoid listing every single cell. if score in every third column of input range, can calculate column number relative first column, , function mod(column,3) returns: {1,0,0,1,0,0...}. every third column of input array included in sum.

• (n15:bl15+o15:bm15/10^3+column(n15:bl15)/10^6>= large(n15:bl15+o15:bm15/10^3+column(n15:bl15)/10^6,6)

this part decide 6 of scores should included in final sum. trickiest part decide ties. approach take:

• if 2 scores same, take 1 higher number of bulleyes
• if still tied, take 1 first columns

this means instead of n15 value calculate:

n15+o15/10^3+column(n15)/10^6 

with sample data evaluates to: 566.017014. first 3 decimal places number of bulleyes, next 3 column number.

you can use same formula calculate average of top 6 scores changing first parameter:

=sumproduct(n15:bl15, --(mod(column(n15:bl15)-column($n15),3)=0), --(n15:bl15+o15:bm15/10^3+column(n15:bl15)/10^6>=    large(n15:bl15+o15:bm15/10^3+column(n15:bl15)/10^6,6))    )/6