Help with IF MIN and MAX with #N/A

[rdriver1]

So I need to find the min and max for a column where #N/A will be present. So far i have tried
=MIN(IF(A:A="",0,A:A))
=MIN(IF(NOT(ISNA(A:A)),A:A))
=MIN(IF(ISNUMBER(A:A),A:A))

My outputs keep coming back as #N/A

I need to use the column not specific cell ranges because the data is being brought in from text files of various sizes before filtering

Example:

5886	90.1	116.3	0.7
5887	76.3	112	0.7
5888	64.4	111.3	0.7
5889	76.3	#N/A	0.8			X	Y	Z
5890	81	#N/A	0.8		Min	#N/A	#N/A	#N/A
5891	90.3	116.3	0.8		Max	#N/A	#N/A	#N/A
5892	82.8	#N/A	0.8
5893	89.7	106.9	0.8
5894	86.3	#N/A	0.8
5895	85.5	126.1	0.8

<tbody>
</tbody>

 

[Jon von der Heyden]

=MIN(IF(ISNUMBER(A:A),A:A))

should work...

Are you confirming it with CONTRTOL+SHIFT+ENTER (it should be an array formula)?

 

[rdriver1]

"ctrl shift enter" was the ticket thanks!!

 

[XOR LX]

I strongly recommend that you don't use entire column references within an array formula. Unlike COUNTIF, SUMIF, COUNTIFS or SUMIFS, for example, array formulas calculate over all cells passed to them, whether technically beyond the last-used cells in those ranges or not.

So if, for example, you only have data extending as far as row 1000, then, by referencing an entire column's worth of rows, you are effectively forcing Excel to calculate more than one million rows than are actually necessary, resulting in an astonishingly resource-heavy formula. And that's just for one instance of that formula.

I know you think you're "hedging your bets" by setting such an upper reference, but you're actually doing far more harm than good. Either choose a suitably low, though sufficient, upper bound for the end row being referenced or, even better, makes your ranges dynamic, such that they automatically adjust as your data expands/contracts.

Regards

 

[FormR]

Hi, if you have XL2010+ you could try the normally entered:

Max: =AGGREGATE(4,6,A:A)
Min: =AGGREGATE(5,6,A:A)

 

[Jon von der Heyden]

Hi, if you have XL2010+ you could try the normally entered:

Max: =AGGREGATE(4,6,A:A)
Min: =AGGREGATE(5,6,A:A)

Darnit, I keep forgetting about AGGREGATE!