Ignoring "NA" for min/max

emutuc

New Member
Joined
Dec 19, 2022
Messages
13
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
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")
 
Upvote 0
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:

Book2
ABCDEFGHIJK
11NA591
Sheet1
Cell Formulas
RangeFormula
K1K1=MIN(A1,C1,E1,G1)
 
Upvote 0
.. and if it is, or could be, an error #N/A then use AGGREGATE

22 12 24.xlsm
ABCDEFGHIJK
11#N/A591
Min
Cell Formulas
RangeFormula
C1C1=NA()
K1K1=AGGREGATE(5,6,A1,C1,E1,G1)
 
Upvote 0
=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
 
Upvote 0
=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!
 
Upvote 0
.. and if it is, or could be, an error #N/A then use AGGREGATE

22 12 24.xlsm
ABCDEFGHIJK
11#N/A591
Min
Cell Formulas
RangeFormula
C1C1=NA()
K1K1=AGGREGATE(5,6,A1,C1,E1,G1)
This worked. Thank you. How about all this plus ignoring 0s?
 
Upvote 0
This worked. Thank you.
You're welcome. Thanks for the confirmation.


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.xlsm
ABCDEFGHIJK
110#N/A505
Min
Cell Formulas
RangeFormula
C1C1=NA()
K1K1=LET(r,INDEX(A1:H1,{1,3,5,8}),AGGREGATE(15,6,r/(r<>0),1))
 
Upvote 0
=MIN(IF(IFERROR(A1:H1,0)>0,IFERROR(A1:H1,0)))
 

Attachments

  • 1672325790872.png
    1672325790872.png
    9.4 KB · Views: 3
Upvote 0
=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.xlsm
ABCDEFGHIJKL
110a#N/A053d053
Min
Cell Formulas
RangeFormula
C1C1=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.
 
Upvote 0
Solution

Forum statistics

Threads
1,213,510
Messages
6,114,044
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top