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?

 

[inactiveuserps07]

@BWMagee, there are issues with the formula at a glance. But before jumping into the formula, I'm seeing a potential problem from the outset with what you're trying to do (hard to tell without seeing any data).

Here's the issue I see ...

It seems entirely possible — likely even — that if we were to find your five highest values in Column H and then check all other conditions you listed against only those five rows, some or all of those highest Column-H values would not correspond with a Column-L value equal to PvO!C98 and/or would not correspond with a Column-E value equal to PvO!B98 and/or would not have a Column-R value greater than or equal to 55. In other words, if your rows that hold the five highest Column-H values fail any one of the other three tests, they will be averaged in as a zero.

If it were a given that the five highest Column-H values would pass every other one of the three criteria — then you wouldn't need the three other criteria. So it's assumed that some if not all of those Column-H top five will be zero values. But then to divide by five a sum that has had zeros added in seems like it's not likely what you're trying to do.

It also seems highly likely that, with over 6000 rows, the 5 LARGEst numbers in that column may be the same number ... which would then flag TRUE for every​ row that had that value in H. In other words, if the highest value in Column H is 500, and there are 130 rows in Column H that have a value of 500, ALL 130 rows will match "one of the five LARGEst numbers in the column; and so all 130 rows will flag as TRUE for that condition — which also doesn't seem like it'd be what you wanted.

Erik

 

[BWMagee]

@BWMagee, there are issues with the formula at a glance. But before jumping into the formula, I'm seeing a potential problem from the outset with what you're trying to do (hard to tell without seeing any data).

Here's the issue I see ...

It seems entirely possible — likely even — that if we were to find your five highest values in Column H and then check all other conditions you listed against only those five rows, some or all of those highest Column-H values would not correspond with a Column-L value equal to PvO!C98 and/or would not correspond with a Column-E value equal to PvO!B98 and/or would not have a Column-R value greater than or equal to 55. In other words, if your rows that hold the five highest Column-H values fail any one of the other three tests, they will be averaged in as a zero.

I appreciate the response, but the issues you see are non-issues as there are definitely more than 5 values in each of those rows that meet all the criteria.

If it were a given that the five highest Column-H values would pass every other one of the three criteria — then you wouldn't need the three other criteria.

Incorrect. They must meet the other criteria, otherwise they will return the wrong value from Column Q.

It also seems highly likely that, with over 6000 rows, the 5 LARGEst numbers in that column may be the same number ... which would then flag TRUE for every​ row that had that value in H.

This is exactly why I need the three other criteria. If I didn't have them, the 5 largest numbers would be the same number. But the three criteria will ensure that the 5 largest numbers are all different.

At this stage the value returned "79" is the average of ALL the values in Column Q that meet the three criteria a, b and c. That is a count of 7. So its as though its performing the AVERAGE formula with criteria a, b, and c, but not performing the LARGE formula. So something about the LARGE formula in the place I've put it (first in formula), and/or style I've used (IF(LARGE... ) must be incorrect.

 

[inactiveuserps07]

What you explained just above sounds different than what you explained in the original post.

Originally, you said you want to include a separate criteria that the H value falls within the range of the highest 5 values in that column (i.e., data from all rows that have an H value greater than or equal to the fifth highest value in column H).

Here, it sounds like you may be saying that you want only the top five results AFTER the other three criteria have been filtered down to a subset (e.g., in your example run, you want only 5 of the 7 results that meet criteria a, b and C: those 5 results having the highest H values among that subset of 7).

Is the latter understanding correct?

It'd be much easier to help with a solution if you could make some sample data available along with the anticipated result (e.g., 20 sample rows of data from 2018F! columns E, H, L and R; and PvO!B98:C98). Is that possible for you to provide here?

 

[Aladin Akyurek]

1) E2:E6 houses:

a
a
a
a
a

2) H2:H6 houses:

100
200
250
150
150

3) L2:L6 houses:

JAD
JAD
JAD
JAD
JAD

4) R2:R6 houses:

60
55
70
65
40

5) Q2:Q6 houses:

4
5
8
4
6

What is the expected result from Q2:Q6 if

E2:E6 = a

H2:H6 --> 3 largest numbers

L2:L6 = JAD

R2:R6 >= 55

 

[BWMagee]

What is the expected result from Q2:Q6 if

E2:E6 = a

H2:H6 --> 3 largest numbers

L2:L6 = JAD

R2:R6 >= 55

Expected result would be 5.66 (5+8+4=17/3). The only rows in Q that meet all the criteria are the middle 3.

 

[BWMagee]

What you explained just above sounds different than what you explained in the original post.

Originally, you said you want to include a separate criteria that the H value falls within the range of the highest 5 values in that column (i.e., data from all rows that have an H value greater than or equal to the fifth highest value in column H).

Here, it sounds like you may be saying that you want only the top five results AFTER the other three criteria have been filtered down to a subset (e.g., in your example run, you want only 5 of the 7 results that meet criteria a, b and C: those 5 results having the highest H values among that subset of 7).

Is the latter understanding correct?

It'd be much easier to help with a solution if you could make some sample data available along with the anticipated result (e.g., 20 sample rows of data from 2018F! columns E, H, L and R; and PvO!B98:C98). Is that possible for you to provide here?

Yes, the latter understanding is correct. All criteria (a,b,c,d) must be met, so it is the five highest from Column H where all criteria are met. Will put some sample data up in a min.

 

[BWMagee]

[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD][/TD]
[TD]E[/TD]
[TD]H[/TD]
[TD]L[/TD]
[TD]Q[/TD]
[TD]R[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Henry[/TD]
[TD]21[/TD]
[TD]BB[/TD]
[TD]10[/TD]
[TD]90[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Bill[/TD]
[TD]31[/TD]
[TD]AA[/TD]
[TD]24[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]24[/TD]
[TD]Bill[/TD]
[TD]25[/TD]
[TD]AA[/TD]
[TD]39[/TD]
[TD]62[/TD]
[/TR]
[TR]
[TD]154[/TD]
[TD]Bill[/TD]
[TD]26[/TD]
[TD]AA[/TD]
[TD]102[/TD]
[TD]80[/TD]
[/TR]
[TR]
[TD]876[/TD]
[TD]Bill[/TD]
[TD]27[/TD]
[TD]AA[/TD]
[TD]63[/TD]
[TD]77[/TD]
[/TR]
[TR]
[TD]1045[/TD]
[TD]Bill[/TD]
[TD]28[/TD]
[TD]AA[/TD]
[TD]47[/TD]
[TD]64[/TD]
[/TR]
[TR]
[TD]2098[/TD]
[TD]Bill[/TD]
[TD]29[/TD]
[TD]AA[/TD]
[TD]36[/TD]
[TD]56[/TD]
[/TR]
[TR]
[TD]4598[/TD]
[TD]Bill[/TD]
[TD]30[/TD]
[TD]AA[/TD]
[TD]83[/TD]
[TD]90[/TD]
[/TR]
[TR]
[TD]5000[/TD]
[TD]Charlie[/TD]
[TD]32[/TD]
[TD]AA[/TD]
[TD]45[/TD]
[TD]65[/TD]
[/TR]
</tbody>[/TABLE]

Coloured in gold are the cells that meet all criteria.
Coloured in red are the cells that meet only some criteria.
Coloured in black are the cells that don't meet any criteria.

 

[Aladin Akyurek]

1) E2:E6 houses:

a
a
a
a
a

2) H2:H6 houses:

100
200
250
150
150

3) L2:L6 houses:

JAD
JAD
JAD
JAD
JAD

4) R2:R6 houses:

60
55
70
65
40

5) Q2:Q6 houses:

4
5
8
4
6

What is the expected result from Q2:Q6 if

E2:E6 = a

H2:H6 --> 3 largest numbers

L2:L6 = JAD

R2:R6 >= 55

Expected result would be 5.66 (5+8+4=17/3). The only rows in Q that meet all the criteria are the middle 3.

Control+shift+enter, not just enter:

=AVERAGE(IF($E$2:$E$6="a",IF(ISNUMBER(MATCH($H$2:$H$6,LARGE($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))))))

I trust you can adapt this to your real data.

 

[BWMagee]

Control+shift+enter, not just enter:

=AVERAGE(IF($E$2:$E$6="a",IF(ISNUMBER(MATCH($H$2:$H$6,LARGE($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))))))

I trust you can adapt this to your real data.

OK I have double and triple checked, but this only returns the highest column H (not the five highest) when it meets the other criteria. If it doesn't meet the other criteria, it returns DIV/0!. So I wonder it is not reading {1,2,3,4,5}, only {1}. Also, when I enter only {1} it is the same result as entering {1,2,3,4,5} and the same as entering {2} or {3}. The numbers entered in there don't seem to change anything. It gives me Q when it meets criteria a,b, and c. But only when d (column H) is highest, not the five highest.

This is how I adapted it:

[FONT=&quot]=AVERAGE(IF('2018F'!$E$2:$E$6755=PvO!$B98,IF(ISNUMBER(MATCH('2018F'!$H$2:$H$6755,LARGE('2018F'!$H$2:$H$6755,{1,2,3,4,5}),0)),IF('2018F'!$L$2:$L$6755=PvO!$C98,IF(ISNUMBER('2018F'!$R$2:$R$6755),IF('2018F'!$R$2:$R$6755>=55,'2018F'!$Q$2:$Q$6755))))))[/FONT]