• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Conditional Large Formula [SOLVED]

Robs89wag

New Member
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

New Member
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

New Member
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

New Member
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)}
 
Top