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]

Does this work for you?

=MIN(IF((X2:AG2<>0)*(X2:AG2<=0.3),W2:AF2,1E+300))

You haven't said what version of Excel you're using. If you're not using 365 or Office 2021 enter this formula with CTRL+SHIFT+ENTER.

 

[Woftae]

Hi myall_blues
Thanks for the reply, my excel is 365
I will try your suggested formula, but I want to point out that the cells with the 30% are not contiguous etc
Question Whats the "1E+300"?

 

[myall_blues]

As I read it you are looking at the values in X, AA, AD and AG and if those values are greater than 30%, then take the values in W,Z,AC and AF respectively. Is that correct?

I now see an error in my formula as I took less than 30% instead of greater than, and that the 30% value is in Variables!A$6, so it becomes:

=MIN(IF((X2:AG2<>0)*(X2:AG2>Variables!A$6)*(X2:AG2<1),W2:AF2,1E+300))

The 1E+300 is just a very large number (10 to the power of 300) that Excel recognises. Since you are looking for the minimum, then the formula scans X2:AG2 looking for values that are not zero and are also greater than 30% (and less than 100% to set a top bound). Since that expression is inside an IF statement, then if it evaluates TRUE then it takes the values in W2:AF2, and if FALSE it takes the value as 1E+300.

As shown below, the result in row 3 is what happens when this formula is applied without the MIN part, entering it into V3 and allowing the formula to SPILL.
Applying the MIN as shown in row 4 just returns the minimum of the values.

Book1
	A	B																			U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG
1
2																							177	39.20%		196	54.94%		-	-		161	27.27%
3																						177	1E+300	1E+300	196	1E+300	1E+300	1E+300	1E+300	1E+300	1E+300
4																						177
5
6	30%
7
Sheet1

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

 

[Woftae]

Yep that worked! - Thanking you muchly
If all relevant cells are below the 30% the The result I see is ###############, Which is the 1E+300, correct?
Any way to not Show this?

 

[myall_blues]

Great news.

There's a couple of different options to not show the value.

1. You could wrap it in an IF statement:

=IF(MIN(IF((X2:AG2<>0)*(X2:AG2>A$6)*(X2:AG2<1),W2:AF2,1E+300))=1E+300,"",MIN(IF((X2:AG2<>0)*(X2:AG2>A$6)*(X2:AG2<1),W2:AF2,1E+300)))

2. You could conditionally format it to make the text white.

Examples of each shown in V3 and V4 respectively.

Book1
	V
2
3
4	1E+300
5
Sheet1

Cell Formulas
Range	Formula
V3	V3	=IF(MIN(IF((X2:AG2<>0)*(X2:AG2>A$6)*(X2:AG2<1),W2:AF2,1E+300))=1E+300,"",MIN(IF((X2:AG2<>0)*(X2:AG2>A$6)*(X2:AG2<1),W2:AF2,1E+300)))
V4	V4	=MIN(IF((X2:AG2<>0)*(X2:AG2>A$6)*(X2:AG2<1),W2:AF2,1E+300))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
V4	Expression	=V4=1E+300	text	NO

 

[Woftae]

You nailed it, Simply Marvellous!
Many Many Thanks

 

[Woftae]

I have been utilising your formula (again thankyou) and carried out some testing.
But I have discovered I will have some instances where the target data may be above the 100%, even though its 100% or higher it still valid.
With you formula it will calculate to the 1E+300 because it higher than the top bound, I have tried redoing the formula to remove *(X2:AG2<1) from the formula without success
What would be the formula without the (and less than 100% to set a top bound).

 

[myall_blues]

It won’t work without the upper bound, as you have discovered, but if you change the 1 to a 2 that will give you up to 200%, or higher depending how high you need to go.

 

[Woftae]

Yeah tried that, The result is back ###############, even when though its still wrapped in an IF statement
=IF(MIN(IF((X2:AG2<>0)*(X2:AG2>=Variables!A$6)*(X2:AG2<2),W2:AF2,1E+300))=1E+300,"-"...........