Conditional Large Formula [SOLVED]

[Robs89wag]

I'm looking return the Nth value from all of columnn A providing it meets a match from column B.

Simular to a sum(if).


Sales Group

10 12

11 16

13 25

14 25

14 17

15 25

17 25

19 30

20 25

22 25


Thanks

Rob

 

[richarta]

Just to clarify, do you mean 'the Nth value in column A of all that match where column B meets the criteria' or 'the Nth value from column A, only if it meets the criteria from column B'.


I'm guessing the former as that makes more sense. If that is right, here is a really messy way of doing it - worked out in about 5 minutes and in multiple columns so it can easily be followed - I'll leave it up to someone else to simplify into one column.


Column C formula: =IF(B2=value,TRUE,FALSE)

Column D formula: =IF(C2=TRUE,COUNTIF(INDIRECT("C2:C"&ROW()),"=true"),0)

Column E formula: =MOD(D2,Nth)

Column F formula: =IF(D2<>0,IF(E2=0,A2,""),"")


Where the 2 in the above formulas is the row.


As I say, 5 minutes work so not pretty.


Tim

 

[Robs89wag]

Hi Tim,


Thanks for your help on this.

I should have been more clear (new at this)


I'm looking return the Nth value (1st largest 2nd largest etc.) from all of columnn A(sales) providing it meets a match (25) from column B (Group).

Simular to a sum(if).


I know there is no large(if) but here is what I think I'm looking for.

=largeIF($a$3:$a$12,$a1,$a$3:$a$12)


25

Sales Group

10 12

11 16

13 25

14 25

14 17

15 25

17 25

19 30

20 25

22 25

 

[Robs89wag]

Case Solved


Thank you all so much.

You've all been a great help and I can not tell you how much I've learned in addition to my formula problem.


{=LARGE(IF(B2:B11=$A$1,A2:A11),1)}

 

[richarta]

Those array formulas are really useful - just getting to know them myself.


Tim