• 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.

=AGGREGATE(17,6,....

bwdeming

New Member
I'm stumped and the solution is not coming to me. I want to use the aggregate function to capture quartiles, min, max, median... but only for value that meet certain criteria. Currently selecting ranges manually, but have already caught a few user errors in the process. sample file attached. Thanks.
 

Attachments

  • Chandoo AGGREGATE.xlsx
    60.1 KB · Views: 5
Hi,

Say for MIN Try below formula, entered as array.

=MIN(IF($A$2:$A$2344=$E3,$C$2:$C$2344))

Enter with Ctrl+Shift+Enter.

Similarly repeat for all functions.

Regards,
 
That's half of the solution; I need to capture values for column C if Batch = 107 AND it's group A. As to a pivot, they fall apart with quartiles and median.
 
Thanks. I've tried several variations of the above, but I'm still encountering issues getting the correct values for each calculation. I'll keep at it.
 
@bwdeming

i guess you can do it with powerpiovt or with conventional pivot using helper column. If you are interested it can be tried upon.
 
Thanks. I've tried several variations of the above, but I'm still encountering issues getting the correct values for each calculation. I'll keep at it.

Well the formula I posted in comment #5 seems to work and deliver same output as your manual output. Where you are getting the problem.

Regards,
 
Back
Top