Using MIN with IF resulting in #VALUE error

[Woftae]

In a nutshell, I would like to get the lowest value (number) from a range of specified cells but only if the adjacent cell is above the variable value (30%) - (ignoring cells with zero or null)
I am trying to use the following EXCEL MIN function with a multiple conditional IF statements entered into cell V2.
=MIN(IF(AND(ISNUMBER(X2),X2>Variables!A$6),W2,""),IF(AND(ISNUMBER(AA2),AA2>Variables!A$6),Z2,""),IF(AND(ISNUMBER(AD2),AD2>Variables!A$6),AC2,""),IF(AND(ISNUMBER(AG2),AG2>Variables!A$6),AF2,""))

As I only require to include cells if the adjacent cells value IS greater than the variable value (30%), I do not actually require an action on FALSE, but as IF statements require an action if FALSE, I tried setting the FALSE action to "0" but then the MIN returns "0" which is what I don't want.
But if I set the FASLE action to "" (as above) I get a #VALUE error where the value of the adjacent target cell is below the variable value (30%) or Null or a non-number value such as a dash?? - See screen shots attached
I have also modified and tried the formula in various iterations such as with and without the AND statement etc. to try and provide the correct results without success.

Any assistance on this issue from the collective wise will be greatly appreciated - Thanks

 

[myall_blues]

Can you post a screenshot of the offending data.

 

[myall_blues]

It works for me so you must have done something wrong. Don't forget you need to change both ends of the formula in the IF statement.

Book1
	A	B																			U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG
1
2																							177	1.5		196	0.5494		0	0		161	0.2727
3																					Spill ==>	177	1E+300	1E+300	196	1E+300	1E+300	1E+300	1E+300	1E+300	1E+300
4																					Min only ==>	177
5																					If(Min… ==>	177
6	0.3
Sheet1

Cell Formulas
Range	Formula
V3:AE3	V3	=IF((X2:AG2<>0)*(X2:AG2>A$6)*(X2:AG2<2),W2:AF2,1E+300)
V4	V4	=MIN(IF((X2:AG2<>0)*(X2:AG2>A$6)*(X2:AG2<2),W2:AF2,1E+300))
V5	V5	=IF(MIN(IF((X2:AG2<>0)*(X2:AG2>A$6)*(X2:AG2<2),W2:AF2,1E+300))=1E+300,"-",MIN(IF((X2:AG2<>0)*(X2:AG2>A$6)*(X2:AG2<2),W2:AF2,1E+300)))
Dynamic array formulas.

 

[Woftae]

Formula used below
=IF(MIN(IF((X169:AG169<>0)*(X169:AG169>=Variables!A$6)*(X169:AG169<2),W169:AF169,1E+300))=1E+300,"-",MIN(IF((X169:AG169<>0)*(X169:AG169>=Variables!A$6)*(X169:AG169<1),W169:AF169,1E+300)))

 

[myall_blues]

 

[Woftae]

SH#T!
Thanks, I guess I could not see the trees for the forest!

 

[myall_blues]

Yeah - IFS was a good formula improvement but it doesn't do what I hoped it would when it was first foreshadowed,, which is like:
=IF(Very_long_complex_formula=0, "", &1) where &1 is interpreted by Excel as Very_long_complex_formula.

If you've ever used Unix regular expressions they have that ability.

Glad we got it sorted out.