Conditional formatting to highlight lowest value amongst 4 cells?

[Jyggalag]

Hi all,

I currently have this setup:

My formulas in columns J-M look at their respective counterparts in columns D-G and once all four numbers have been entered in columns D, E, F and G, the cells in columns J-M will highlight which one has the lowest value. However, I would like to highlight this further and have some conditional formatting that also highlights the cell that returns the value for whichever cell has the lowest value. So in the first case column G contains the lowest value (first row of my IF(AND) formula) and I would like for this cell containing "KOLONNE G" to be highlighted with a red color.

My issue is that I am terribly bad at conditional formatting and I have no idea how to do this. Can somebody please help me? Would be greatly appreciated!

Thank you all

Kind regards,
Jyggalag

 

[SanjayGMusafir]

However, I would like to highlight this further and have some conditional formatting that also highlights the cell that returns the value for whichever cell has the lowest value. So in the first case column G contains the lowest value (first row of my IF(AND) formula) and I would like for this cell containing "KOLONNE G" to be highlighted with a red color.

From Cell J4 to M10 use the formula below to conditional format and update

=J4<>""

Conditional Formatting -> New Rule... -> Classic -> Use a formula to determine which cell to format

Copy paste above formula with equal sign in formula bar

 

[Skovgaard]

Don't know if it's important for you, to have all the formulas in Column J:M, otherwise you could simplify it, by below formula:

/Skovgaard

 

[SanjayGMusafir]

to have all the formulas in Column J:M

Just modify the formula I gave -

=J1<>""

And apply it to column J:M

Try it.

 

[Jyggalag]

Just modify the formula I gave -

=J1<>""

And apply it to column J:M

Try it.

This is amazing, thank you so much Sanjay! Apologies for my late response.

Out of curiosity, is there a conditional formatting that can look at a row (so for example row 4) and highlight the lowest value within these 4 cells from column D-G, row 4? So that would be 0,25 in cell G4 in my case

If this is not possible, then thank you nonetheless!

 

[Peter_SSs]

Like this?
Select D4:G?? and apply the CF formula shown

22 10 10.xlsm
	D	E	F	G
1
2
3
4	0.26	0.59	0.32	0.04
5	0.01	0.17	0.9	0.04
6	0.19	0.48	0.44	0.44
7	0.99	0.75	0.5	0.37
8	0.36	0.35	0.08	0.29
9	0.86	0.08	0.13	0.29
10
11
CF Lowest in row

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D4:G11	Expression	=AND(D4=MIN($D4:$G4),D4<>"")	text	NO

 

[Jyggalag]

Like this?
Select D4:G?? and apply the CF formula shown

22 10 10.xlsm
	D	E	F	G
1
2
3
4	0.26	0.59	0.32	0.04
5	0.01	0.17	0.9	0.04
6	0.19	0.48	0.44	0.44
7	0.99	0.75	0.5	0.37
8	0.36	0.35	0.08	0.29
9	0.86	0.08	0.13	0.29
10
11
CF Lowest in row

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D4:G11	Expression	=AND(D4=MIN($D4:$G4),D4<>"")	text	NO

It worked!

Thank you so much @Peter_SSs, absolutely amazing!

Appreciate all the help all of you!

 

[Jyggalag]

Also final question, and this is truly a matter of luxury, so if it's too complex and you cannot assist, i'm more than satisfied.

However, right now if I enter four numbers, then remove one, I will be left with this:

Hence showing that my response in cell D11 (the blank cell in the bottom left) is the lowest value.

Is there a way in which I can force my formula to ignore blank cells when it makes this calculation? Or simply set blank cells at a value higher than the other cells?

This is the formula I use for the "Lowest value" cell columns (example taken from cell J4):

=IF(AND(D4<E4;D4<F4;D4<G4);"KOLONNE D";"")

Do let me know if anybody has the solution to this Thanks!

 

[Peter_SSs]

Try this set-up. It also allows for multiple equal minimum values in a row (see row 5) and no numbers at all in a row (rows 9 & 11)

Note though that this will return incorrect results if columns are added/deleted to the left of the data. If that is a possibility that you wanted to allow for, some different (longer) formulas would be required.

22 10 10.xlsm
	D	E	F	G	H	I	J	K	L	M
1
2
3
4	0.26	0.59	0.32	0.04						KOLONNE G
5	0.01	0.17	0.01	0.04			KOLONNE D		KOLONNE F
6	0.19	0.48	0.44	0.44			KOLONNE D
7	0.99	0.75	0.5	0.37						KOLONNE G
8	0.36	0.35	0.08	0.29					KOLONNE F
9
10		0.08	0.13	0.29				KOLONNE E
11
CF Lowest in row

Cell Formulas
Range	Formula
J4:M11	J4	=IF(AND(D4<>"",D4=MIN($D4:$G4)),"KOLONNE "&MID("DEFG",COLUMNS($J:J),1),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
J4:M11	Expression	=J4<>""	text	NO
D4:G11	Expression	=AND(D4=MIN($D4:$G4),D4<>"")	text	NO

 

[Jyggalag]

Try this set-up. It also allows for multiple equal minimum values in a row (see row 5) and no numbers at all in a row (rows 9 & 11)

Note though that this will return incorrect results if columns are added/deleted to the left of the data. If that is a possibility that you wanted to allow for, some different (longer) formulas would be required.

22 10 10.xlsm
	D	E	F	G	H	I	J	K	L	M
1
2
3
4	0.26	0.59	0.32	0.04						KOLONNE G
5	0.01	0.17	0.01	0.04			KOLONNE D		KOLONNE F
6	0.19	0.48	0.44	0.44			KOLONNE D
7	0.99	0.75	0.5	0.37						KOLONNE G
8	0.36	0.35	0.08	0.29					KOLONNE F
9
10		0.08	0.13	0.29				KOLONNE E
11
CF Lowest in row

Cell Formulas
Range	Formula
J4:M11	J4	=IF(AND(D4<>"",D4=MIN($D4:$G4)),"KOLONNE "&MID("DEFG",COLUMNS($J:J),1),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
J4:M11	Expression	=J4<>""	text	NO
D4:G11	Expression	=AND(D4=MIN($D4:$G4),D4<>"")	text	NO

This is perfect Peter! Thank you so much !!