Multiple conditional formatting MINIFS

[duteberta]

I want to Conditional format ONLY THE ONE CELL that contains the lowest value, but only in there is a "1" in the SEL column. For some reason I'm not able to get the correct syntax for the Conditional Formatting formula.

In this example I'm working within a table named "data". I want the cell in column "WT" to be highlighted if it is the lowest value in that column, but only if there is a "1" in the SEL column.

Can you help me?

example.xlsx
	A	B	C
1	DATE	SEL	WT
2	10/1/24	1	335.0
3	10/2/24	1	334.2
4	10/3/24	1	334.1
5	10/4/24	1	335.1
6	10/5/24	1	333.9
7	10/6/24		333.7
8	10/7/24		334.2
9	10/8/24		332.8
10	10/9/24		333.0
11	10/10/24		332.8
12	10/11/24		333.4
13	10/12/24		333.0
14	10/13/24		332.8
15	10/14/24		332.5
16	10/15/24		332.6
Sheet1

 

[Cubist]

Edit: Need some clarification.
Do you want to look at the min of the subset of WT only where SEL = 1?
or
Do you want to look at the min of the entire column WT but highlight only if there's a 1 in SEL?

 

[duteberta]

Edit: Need some clarification.
Do you want to look at the min of the subset of WT only where SEL = 1?
or
Do you want to look at the min of the entire column WT but highlight only if there's a 1 in SEL?

Thank you- the first one.

Look only where there is a "1" in the SEL column and pick the lowest value in that subset. Thank you very much

 

[Cubist]

Try this.

Book1
	A	B	C
1	DATE	SEL	WT
2	10/1/24	1	335
3	10/2/24	1	334.2
4	10/3/24	1	334.1
5	10/4/24	1	335.1
6	10/5/24	1	333.9
7	10/6/24		333.7
8	10/7/24		334.2
9	10/8/24		333.9
10	10/9/24		333
11	10/10/24		332.8
12	10/11/24		333.4
13	10/12/24		333
14	10/13/24		332.8
15	10/14/24		332.5
16	10/15/24		332.6
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2:C16	Expression	=(C2=MINIFS($C$2:$C$16,$B$2:$B$16,"1"))*(B2=1)	text	NO

 

[KillerOfGiants]

Create Formula in Name Manager, I called it MyMin: =MIN(FILTER(Test[WT],Test[Sel]=1,""))

Highlight the current column from row 2 to end for WT and use the following formula for criteria: =AND($B2=1,$C2=MyMin)
where column B is SEL and column C is WT.
The conditional formatting will carry down as new rows are added to the table.

 

[Cubist]

Create Formula in Name Manager, I called it MyMin: =MIN(FILTER(Test[WT],Test[Sel]=1,""))

Highlight the current column from row 2 to end for WT and use the following formula for criteria: =AND($B2=1,$C2=MyMin)
where column B is SEL and column C is WT.
The conditional formatting will carry down as new rows are added to the table.

It's not necessary to create a name range. The conditional format will pick up the new rows automatically.

 

[KillerOfGiants]

The named range is used to reference the table (Test[WT] and Test[Sel]). Those table references do not work in the conditional formatting formula bar.

 

[Cubist]

The named range is used to reference the table (Test[WT] and Test[Sel]). Those table references do not work in the conditional formatting formula bar.

I'm aware of that but what is the advantage of referencing table object vs. R/C style here? Genuine question.

 

[KillerOfGiants]

Looking at your solution: =(C2=MINIFS($C$2:$C$16,$B$2:$B$16,"1"))*(B2=1)

The advantage is readability IMHO. It could be just aesthetics but to each their own.

 

[duteberta]

Thank you all- you have helped me in my creation of my personal weight loss calculator, I really appreciate it!