Example data set

Right, above is a link to an image of a sub-segment of my data set. It is oriented in sets of 3 columns, with the first being a concentration, the second a qualifier, and the last an MDL - and continues for up to 95 samples (so a total of 285 columns making manual entry impractical) . How can i calculate the max or min of the concentration values for those that have a qualifier of "u" or vice versa have no qualifier?

I can't figure out anything, and unfortunately i don't have the time to re-orient the data. Anybody have an idea?

Perhaps something like this will do,

The 8 formulas in C7:J7 are,

``````=AGGREGATE(15, 6, \$A2:\$AY2/((\$A\$1:\$AY\$1=C\$6)*(\$B2:\$Z2="U")), 1)
=AGGREGATE(15, 6, \$C2:\$BA2/((\$C\$1:\$BA\$1=D\$6)*(\$B2:\$AZ2="U")), 1)
=AGGREGATE(14, 6, \$A2:\$AY2/((\$A\$1:\$AY\$1=E\$6)*(\$B2:\$Z2="U")), 1)
=AGGREGATE(14, 6, \$C2:\$BA2/((\$C\$1:\$BA\$1=F\$6)*(\$B2:\$AZ2="U")), 1)
=AGGREGATE(15, 6, \$A2:\$AY2/((\$A\$1:\$AY\$1=G\$6)*(\$B2:\$Z2<>"U")), 1)
=AGGREGATE(15, 6, \$C2:\$BA2/((\$C\$1:\$BA\$1=H\$6)*(\$B2:\$AZ2<>"U")), 1)
=AGGREGATE(14, 6, \$A2:\$AY2/((\$A\$1:\$AY\$1=I\$6)*(\$B2:\$Z2<>"U")), 1)
=AGGREGATE(14, 6, \$C2:\$BA2/((\$C\$1:\$BA\$1=J\$6)*(\$B2:\$AZ2<>"U")), 1)
``````

Those cover both minimum and maximum values when either including or excluding the qualifier.

``````=AGGREGATE(15, 6, \$A2:\$AY2/(SIGN(LEN(\$A2:\$AY2))*(\$A\$1:\$AY\$1=C\$6)*(\$B2:\$Z2="U")), 1)