Highligh lowest value in a row excluding zeros, errors and text

[MKate]

I have a spreadsheet containing a combination of numbers ($ values), zeros, errors (#N/A) and text.

For each row I want to highlight in green the lowest dollar value shown.
I have used the following formula to display the lowest value from the row in another cell:
=MIN(IF(ISNUMBER(C18:T18),IF(C18:T18>0,C18:T18)))

But this formula requires ctrl + shift + enter to work. When I try to put this into conditional formatting formula to get the cell to highlight green, it highlights multiple cells in the row green.

Please help! It is driving me crazy.

Thanks!
M

 

[DRSteele]

I think maybe your absolute/relative cell references are the trouble here.

I successfully created two rules.
1) In V18 I have a your CSE formula, =MIN(IF(ISNUMBER(C18:T18),IF(C18:T18>0,C18:T18))). The CndFrmt rule for cells C18:T18 is =C18:T18=$V18
2) The CndFrmt rule for cells C18:T18 is =C18:T18=MIN(IF(ISNUMBER(C18:T18),IF(C18:T18>0,C18:T18))) The CondFrmt engine seems to have no trouble with the array formula.

Book1
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
18	2	0	l	#DIV/0!		5	x	y	6	2	2	3	m	#NAME?	#N/A	5	0	6		2
Sheet51

Cell Formulas
Range	Formula
V18	{=MIN(IF(ISNUMBER(C18:T18),IF(C18:T18>0,C18:T18)))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[MKate]

Thank you!! it works now! lifesaver.
Just out of interest... can you explain why the $ needs to be with column V only and doesn't work when you have
=$C$18:$T$18=$V$18
(which is what the CndFrmt wants to do automatically when you select the cells)

 

[DRSteele]

You're welcome. Lifesaver! now I'm blushing....

The CondFrmt engine will create a virtual array of logical tests where range C18:T18 is and it will compare C18 to V18, D18 to W18, E18 to X18...etc., unless you lock the column with $. This behaviour is intentional: perhaps you want the column to slide like that within the CndFrmt test, but in your case you don't.

You might find this video interesting; it clears up some of the baffling things that vex us all about CondFrmt. https://www.youtube.com/watch?v=GRfe4bHsjhI