Conditional min and max values ​​in Excel 2010

Question

I would like to find Min and Max of Quantity (Column 2) based on Type (coloumn 1), can this be done?

I tried this, but the result was unexpected A similar question

Answer 1

Assuming your data is higher in A2: B13, this works:

=MAX(IF(A2:A13="A",1,0)*(B2:B13)) =MAX(IF(A2:A13="B",1,0)*(B2:B13)) =MAX(IF(A2:A13="C",1,0)*(B2:B13)) 

You need to press ctrl + shft + Enter when you enter the formula into the cell. This finds all lines with A , B or C and multiplies 1 with the value next to it if the letter matches your formula, and 0 if it does not match. Then you take the MAX() values.

<<<Change →>

As @GSerg suggested, you can also do this using these formulas if you press ctrl + shft + Enter while entering them in each cell:

 =MAX(IF(A:A="A",B:B)) =MAX(IF(A:A="B",B:B)) =MAX(IF(A:A="C",B:B)) 

A more elegant way to do it!

Answer 2

Nothing but a pivot table (as @andy holaday suggests) seems like a clear masochism (unless it’s a very good, but apparently very peculiar reason):

Note that for illustration, I doubled the amount of OP data for B , and again for C

Answer 3

This works without ctrl + shift + enter, but your table should be sorted by TYPE column.

Suppose your table is placed in B3: C15, then in A4 put

 =IF(B4=B3;A3;A3+1) 

in E4 - "1", in E5 - "2", in E6 - "3", in F4 put:

 =MAX(INDIRECT("C" & MATCH(E4;$A$1:$A$17;0) & ":C" & MATCH(E4;$A$1:$A$17;1) )) 

and copy it to F5 and F6

in G4 put:

 =MIN(INDIRECT("C" & MATCH(E4;$A$1:$A$17;0) & ":C" & MATCH(E4;$A$1:$A$17;1) )) 

and copy it to G5 and G6

The MATCH function doesn’t handle rows correctly, so I had to specify TYPE, you can use VLOOKUP to change the numbers in column E for rows

In my table, I used this solution to find rows with maximum values ​​this way:

Answer 4

Rather insightful but easy way to do it.
1. create a new column that combines both type and number and calls it "TypeQty" or whatever; 2. Sort (ascending) the new table, i.e. Type, Qty and TypeQty, together, but sorted in the TypeQty column. 3. Apply a form that checks if the type in the line matches the current line. if not, then check this line because it is the last of the current type.

you will get a "mark" only the maximum lines for each type. See screenshots

Answer 5

I would suggest using =large(if(...=...;...);k) to solve this problem.