LARGE function issue

[BWMagee]

<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Lucida Grande'; color: #000000}span.s1 {color: #006107}span.s2 {color: #ab30d6}span.s3 {color: #a54a29}span.s4 {color: #0057d6}span.s5 {color: #33af4a}span.s6 {color: #ff9c1b}</style>Hi, I have an issue where my LARGE function isn't working.

I want to SUMPRODUCT Q2:Q6755 / 5
If:
a) L2:L6755 = C98
b) E2:E6755 = B98
c) R2:R6755 >= "55"
d) H2:H6755 = The largest five numbers in that column

Here is my current working:

{=IF(LARGE('2018F'!$H$2:$H$6755,{1,2,3,4,5}),(SUMPRODUCT(IF('2018F'!$L$2:$L$6755=PvO!$C98,IF('2018F'!$E$2:$E$6755=PvO!$B98,IF('2018F'!$R$2:$R$6755>=55,'2018F'!$Q$2:$Q$6755,0))))))/5}

It returns a value of 79, which is correct for SUMPRODUCT Q2:Q6755 / 5, based on criteria a, b and c. But not d. For some reason it seems to be ignoring the LARGE function of criteria d. Where am I going wrong?

 

[Aladin Akyurek]

1) Try the set up first on the sample I provided.

2) Note that the formula matches all values from column H against the 3 highest values from the same column.

3) For the sample I posted, we get 5.66.

4) If the set up does not satisfy, try the following guess:

Control+shift+enter, not just enter:

=AVERAGE(IF($E$2:$E$6="a",IF(ISNUMBER(MATCH($H$2:$H$6,LARGE(IF($E$2:$E$6="a",IF($L$2:$L$6="jad",IF(ISNUMBER($R$2:$R$6),IF($R$2:$R$6>=55,$H$2:$H$6)))),{1,2,3}),0)),IF($L$2:$L$6="JAD",IF(ISNUMBER($R$2:$R$6),IF($R$2:$R$6>=55,$Q$2:$Q$6))))))

 

[BWMagee]

1) Try the set up first on the sample I provided.

2) Note that the formula matches all values from column H against the 3 highest values from the same column.

3) For the sample I posted, we get 5.66.

4) If the set up does not satisfy, try the following guess:

Control+shift+enter, not just enter:

=AVERAGE(IF($E$2:$E$6="a",IF(ISNUMBER(MATCH($H$2:$H$6,LARGE(IF($E$2:$E$6="a",IF($L$2:$L$6="jad",IF(ISNUMBER($R$2:$R$6),IF($R$2:$R$6>=55,$H$2:$H$6)))),{1,2,3}),0)),IF($L$2:$L$6="JAD",IF(ISNUMBER($R$2:$R$6),IF($R$2:$R$6>=55,$Q$2:$Q$6))))))

Thanks for the effort. I tried the first formula on the sample you provided and it worked, I got 5.66. Frustrating then that it doesn't work on my main spreadsheet!

The second formula produced all #DIV/O!'s

 

[Aladin Akyurek]

See the following workbook for both formulas: https://www.dropbox.com/s/nea61rad0swx0c3/BWMagee LARGE function issue.xlsx?dl=0