Conditional MIN/MAX

​

Most of Excel users deal with conditional sum (SUMIF/S) and conditional count (COUNTIF/S), but did you know that you can also use conditional min/max?

For example, you have an employee database and you want to figure out the salary ranges per level (min/max).

For illustrative purposes, we will use the following hypothetical database*:

	A	B	C
1	Employee Name	Title	Salary
2	Kyra Martino	Engineer 2	650
3	Leonor Bernardini	Engineer 2	650
4	Doris Steed	Engineer 2	650
5	Tameka Lichty	Technician 2	550
6	Dawn Studstill	Technician 2	550
7	Brittany Cauley	Technician 2	580
8	Brad Lo	Engineer 2	650
9	Glen Laberge	Technician 2	350
10	Vernetta Castiglione	Engineer 2	685
11	Marylouise Brookover	Engineer 2	685
12	Tammi Davilla	Engineer 2	685
13	Oretha Henrich	Technician 2	585
14	Alaina Sattler	Engineer 2	690
15	Mya Ewell	Engineer 2	690
16	Albina Wimbush	Senior Supervisor	1,115
17	Letha Korte	Engineer 2	695
18	Byron Fetty	Technician 2	350
19	Helene Gutierres	Engineer 2	695
20	Keturah Koopman	Technician 2	700
21	Neida Mcclaran	Technician 2	705
22	Kylie Reineke	Technician 2	345
23	Laine Schlagel	Technician 2	345
24	Kathlyn Ramey	Technician 2	600
25	Pandora Sautter	Engineer 2	725
26	Karena Strang	Technician 2	833
27	Melonie Frankel	Technician 2	835
28	Tresa Renfroe	Technician 2	880
29	Mitsuko Midyett	Engineer 2	740
30	Arletta Dauenhauer	Technician 2	880
31	Lael Powe	Technician 2	880
32	Oren Salem	Technician 2	880
33	Meridith Burrill	Technician 2	880
34	Yuko Mikus	Senior Technician	980
35	Jamison Mealey	Technician 2	880
36	Kasie Bartlett	Technician 2	885
37	Jina Fillers	Technician 2	350
38	Vida Bran	Technician 2	725
39	Ronda Rothenberger	Technician 2	750
40	Kory Hambleton	Senior Engineer	790
41	Lachelle Neifert	Technician 2	885
42	Clement Julien	Technician 2	895
43	Agueda Trantham	Senior Engineer	800
44	Janie Veitch	Engineer 1	735
45	Edyth Kriger	Technician 2	800
46	Lashell Kaczor	Engineer 1	745
47	Flora Hooser	Technician 2	800
48	Myra Bera	Technician 2	800
49	Nicolasa Forand	Technician 2	800
50	Eleanora Cantero	Engineer 1	760
51	Violeta Ackerman	Technician 1	905
52	Essie Theriot	Technician 1	930
53	Clementine Casanova	Technician 2	760
54	Bethel Hadsell	Technician 2	350
55	Ambrose Ryce	Technician 2	345
56	Merlin Rossbach	Technician 1	935
57	Jeanne Hasty	Technician 2	755
58	Dione Steinhauser	Technician 2	350
59	Clay Bury	Technician 1	965
60	Tasia Schumann	Technician 2	350
61	Marianela Sharlow	Technician 2	350
62	Yetta Starcher	Technician 2	750
63	Sherly Brownfield	Technician 2	810
64	Awilda Vanzile	Senior Engineer	820
65	Luciana Levert	Senior Engineer	845
66	Fairy Flippin	Senior Engineer	850
67	Nicky Mallard	Senior Engineer	855
68	Tamela Samson	Senior Engineer	870
69	Otelia Wene	Senior Engineer	865
70	Jannie Henze	Senior Engineer	870

* All names and other information in the shown table are imaginary and do not intend to represent or refer to actual individuals. If any of the names happens to be the name of a real person, it is pure coincidence.

Tools used: MIN, MAX, IF, Arrays, VLOOKUP, and Advanced Filter.

Steps:

1. Working on column D in the example, you need to instruct Excel to return the minimum salary per Title. So the argument should say: If the title is X then search through salaries where the title is X and return the lowest salary. To translate into a formula:

MIN(IF(D:D=D2,C:C)

2. However, this formula works only as an Array. (For more information about Arrays, please refer to a previous post on Excelarium named "Working with Arrays:.

To convert the formula into an Array, once you type the formula, press CTRL+SHIFT+ENTER instead of ENTER. You will notice to more parentheses indicating that it has become an Array:

{MIN(IF(D:D=D2,C:C)}

3. Drag the formula to the end of the table on column E.

Note: You may notice a slow down in the processing of operations on the sheet; this is normal since Arrays consume a lot of processing power. So you need to be patient while Array results are being calculated or refreshed.

4. For max. salary, apply the same steps but replace the MIN function with MAX as follows:

MAX(IF(D:D=D2,C:C)

Then press CTRL+SHIFT+ENTER.

5. Now you have 2 lists on columns E and F showing salary ranges per title, however, for presentation purposes they'd better be summarized in one table to arrive at one line per title.

Go to Data --> Sort & Filter --> Advanced

Select Copy to another location

List range: Select the Title column (B1:B70).

Cirteria range: Leave empty

Copy to: Select an empty cell away from the original table

Select Unique records only.

Click OK.

You should have the following table:

G

Title

Engineer 2

Technician 2

Senior Supervisor

Senior Technician

Senior Engineer

Engineer 1

Technician 1

6. Next to the new column, use VLOOKUP to get the minimum salary that was arrived at under column D:

=VLOOKUP(G2,B:D,3,FALSE)

You should get the following result:

	G	H
1	Title	Min
2	Engineer 2	650
3	Technician 2	345
4	Senior Supervisor	1,115
5	Senior Technician	980
6	Senior Engineer	790
7	Engineer 1	735
8	Technician 1	905

You should do the same for max. salary, this time returning the values in the Max. column in the VLOOKUP. The result should look as follows:

	G	H	I
1	Title	Min	Max
2	Engineer 2	650	740
3	Technician 2	345	895
4	Senior Supervisor	1,115	1,115
5	Senior Technician	980	980
6	Senior Engineer	790	870
7	Engineer 1	735	760
8	Technician 1	905	965

Voila! You have done a summary of the salary scale, effectively utilizing conditional MIN/MAX.

If you like this post, please do not hesitate to share!