# Ignoring "NA" for min/max



## emutuc (Dec 23, 2022)

Greetings to all on Mr. Excel,

I'm seeking your help on a formula where I want to find the minimum of an array of numbers, from 3 different columns, while ignoring all "NA".  
May I have your suggestions?  

Thank you for your time and willingness


----------



## emutuc (Dec 23, 2022)

I thought this might be the solution, but I get an error:

=min('Tension-Concrete Breakout'!AB2,'Tension-Pull Out-Bond'!, 'Tension-Pull Out-Bond'!O2, 'Tension-Concrete Breakout'!,'Tension-Concrete Breakout'!AB2, <>"NA")


----------



## Scott Huish (Dec 23, 2022)

Your 2nd reference doesn't appear to be complete, however if it is just the text NA and not the error #N/A, then MIN ignores text:

Using simpler references:

Book2ABCDEFGHIJK11NA591Sheet1Cell FormulasRangeFormulaK1K1=MIN(A1,C1,E1,G1)


----------



## Peter_SSs (Dec 23, 2022)

.. and if it is, or could be, an error #N/A then use AGGREGATE

22 12 24.xlsmABCDEFGHIJK11#N/A591MinCell FormulasRangeFormulaC1C1=NA()K1K1=AGGREGATE(5,6,A1,C1,E1,G1)


----------



## wclaw (Dec 23, 2022)

=AGGREGATE(5, 6,  'Tension-Concrete Breakout'!A1, 'Tension-Pull Out-Bond'!A1, 'Tension-Concrete Breakout'!B1, 'Tension-Pull Out-Bond'!B1)

while 5 is min and 6 is ignore error


----------



## emutuc (Dec 28, 2022)

wclaw said:


> =AGGREGATE(5, 6,  'Tension-Concrete Breakout'!A1, 'Tension-Pull Out-Bond'!A1, 'Tension-Concrete Breakout'!B1, 'Tension-Pull Out-Bond'!B1)
> 
> while 5 is min and 6 is ignore error


Thank You!


----------



## emutuc (Dec 28, 2022)

Peter_SSs said:


> .. and if it is, or could be, an error #N/A then use AGGREGATE
> 
> 22 12 24.xlsmABCDEFGHIJK11#N/A591MinCell FormulasRangeFormulaC1C1=NA()K1K1=AGGREGATE(5,6,A1,C1,E1,G1)


This worked. Thank you.  How about all this plus ignoring 0s?


----------



## Peter_SSs (Dec 28, 2022)

emutuc said:


> This worked. Thank you.


You're welcome. Thanks for the confirmation.




emutuc said:


> How about all this plus ignoring 0s?


Here is one way but it would not be applicable if your cells are actually on different sheets as indicated in post #2.
Trying to find the minimum of the yellow cells, ignoring error values and zero.

22 12 24.xlsmABCDEFGHIJK110#N/A505MinCell FormulasRangeFormulaC1C1=NA()K1K1=LET(r,INDEX(A1:H1,{1,3,5,8}),AGGREGATE(15,6,r/(r<>0),1))


----------



## shinigamilight (Dec 29, 2022)

=MIN(IF(IFERROR(A1:H1,0)>0,IFERROR(A1:H1,0)))


----------



## Peter_SSs (Dec 29, 2022)

shinigamilight said:


> =MIN(IF(IFERROR(A1:H1,0)>0,IFERROR(A1:H1,0)))


That ignores negative numbers as well as zero but in any case the single function formula ..
=AGGREGATE(15,6,A1:H1/(A1:H1>0),1)
.. would do the same job as that.

Both of the above formulas would rely on the cells between the yellow ones (which we know nothing about) not containing numbers, hence the structure of my previous suggestion.

22 12 24.xlsmABCDEFGHIJKL110a#N/A053d053MinCell FormulasRangeFormulaC1C1=NA()K1K1=LET(r,INDEX(A1:H1,{1,3,5,8}),AGGREGATE(15,6,r/(r<>0),1))L1L1=MIN(IF(IFERROR(A1:H1,0)>0,IFERROR(A1:H1,0)))Press CTRL+SHIFT+ENTER to enter array formulas.


----------



## emutuc (Dec 23, 2022)

Greetings to all on Mr. Excel,

I'm seeking your help on a formula where I want to find the minimum of an array of numbers, from 3 different columns, while ignoring all "NA".  
May I have your suggestions?  

Thank you for your time and willingness


----------



## emutuc (Jan 5, 2023)

Peter_SSs said:


> That ignores negative numbers as well as zero but in any case the single function formula ..
> =AGGREGATE(15,6,A1:H1/(A1:H1>0),1)
> .. would do the same job as that.
> 
> ...


Thank you, this solves it.  I like this one, not as complicated


----------



## emutuc (Jan 5, 2023)

shinigamilight said:


> =MIN(IF(IFERROR(A1:H1,0)>0,IFERROR(A1:H1,0)))


Many thanks! this worked!!


----------



## emutuc (Jan 5, 2023)

Peter_SSs said:


> You're welcome. Thanks for the confirmation.
> 
> 
> 
> ...


Thank you Peter_SSs


----------



## Peter_SSs (Jan 5, 2023)

Glad we could help. Thanks for the feedback.


----------

