LARGE IF With Criteria

[WalkAlot]

Hi,

I'm creating a LARGE IF array funtion as follows:

=LARGE(IF(Master!$B:$B=A1,Master!$F:$F),1)

The A1 is a drop down list that is selecting a Category name and then selecting the nth largest number in Col F from 1 - 10. I also want to include an option to look at all rows in Col F so that I'm pulling the 10 largest figures from the whole list rather than category specific.

I've tried and ELSE and Or but this doesn't seem to work, I've tried naming the range 'AllCategories' and added that to the drop down but when that is selected I get an error (#NUM).

Can anyone advise how I can include the option to look at the whole column as well please?

 

[Aladin Akyurek]

Try:

=LARGE(IF(Master!$B$2:$B$500=A1,Master!$F$2:$F$500),ROW(INDIRECT("1:"&MIN(10,COUNTIFS(Master!$B$2:$B$500,A1)))))

which needs control+shift+enter, not just enter.

 

[Roger Govier]

Hi
Unless I'm misunderstanding you, you cannot do that with one formula.
Your array entered formula is working fine for Category.
What you need alongside it in say the next column, is the Large part of the formula without an IF
=LARGE(Master!$F:$F,1)
and copied down the same number of rows.

I would use
=LARGE(Master!$F:$F,ROW(A1))
and copy down as then you don't need to change the formula for each row.

 

[WalkAlot]

Thanks Aladin & Roger,

I've tried Aladins method which returned a #Ref

Since posing this I've added a bit more to the formula as I need to query 3 different columns and then ideally be able to slice and dice the information which is why I was after a way of finding out if a Wildcard option could be entered or something. The formula now reads:

=LARGE(IF(Master!$A:$A=B1,IF(Master!$B:$B=B2,IF(Master!$C:$C=B3,Master!$F:$F))),1)

Col a = Page Type
Col B = Category
Col C = Template

I take it from what Roger has said it won't be possible to acheive what I'm trying to do then as some people will want to view a particlular Page Type but across all Categories or a selected Categor with all Page Types.

I think the only other option I have would be a Pivot Table but the people who'll be using it wouldn't know how to use a Pivot.

Thanks for your time both, much appreciated.

 

[Aladin Akyurek]

Book1
	A	B	C	D	E
1	x
2	2	x			6
3	5	y
4	6	x			2
5	2	x
6
7
8
9
10
11
12
13
14
15
16
Sheet1

A1 = x

E2, control+shift+enter, not just enter:

=LARGE(IF($B$2:$B$16=A1,$A$2:$A$16),ROW(INDIRECT("1:"&MIN(10,COUNTIFS($B$2:$B$16,A1)))))

This shows the first item of the reference the LARFE formula computes.

You can create a defined name, say data, with this formula which you can feed to other functions for processing.

=LARGE(IF(Sheet1!$B$2:$B$16=Sheet1!$A$1,Sheet1!$A$2:$A$16),ROW(INDIRECT("1:"&MIN(3,COUNTIFS(Sheet1!$B$2:$B$16,Sheet1!$A$1)))))

Example...

In E4 enter:

=MEDIAN(data)

You can't alas use data in data validation.


If you intend to data validate a cell using this 'x', a different approach is needed.