MAX function doesn't like N/A!

fidgen

New Member
Joined
Aug 20, 2002
Messages
46
I've got a column in which each cell has a formula linking to a different worksheet.

The cells can either display a positive number (not 0) or N/A

Row 2 of each column has: (in this example for column B)

=MAX(B3:B10000) (10000 to allow it to automatically update as new rows are added)

But because some cells have N/A, MAX also displays #N/A rather than just ignoring them and displaying the largest integer in the column.

Any way round this?


Thanks
Hugh
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

WillR

Well-known Member
Joined
Feb 18, 2002
Messages
1,143
You can get rid of the #N/A values by using the IIF(ISERR formula....

I picked the following code up that you can run per worksheet to add the IF(ISERR string around your formulas.

Code:
Sub ErrorTrapAddDDL()

' Adds =If(IsError(xxx)) around formulas
' results in no error values on speadsheet
  
  Dim cel As Range
  Dim rng As Range
  Dim Check As String

  Const Equ As String = "=IF(ISERROR(_x) ,"""", _x)"

  Check = Left$(Equ, 12) & "*" ' Check for =IF(ISERROR(

  On Error Resume Next

  Set rng = Selection.SpecialCells(xlFormulas, 23)
  If rng Is Nothing Then Exit Sub

  With WorksheetFunction
    For Each cel In rng
      If Not cel.Formula Like Check Then
        cel.Formula = .Substitute(Equ, "_x", Mid$(cel.Formula, 2))
      End If
    Next
  End With
End Sub

Hope this helps

_________________
Will<img src=http://www.smilies.nl/dieren/pengy.gif>
Do NOT feed the Penguin!
This message was edited by WillR on 2002-09-13 04:13
 

sen_edp

Well-known Member
Joined
Mar 13, 2002
Messages
555
Hello Hugh

try this array formula

=max(if(iserror(b3:b10000),"",b3:b10000))

press ctrl-shift-enter when entering

hth

_________________
Best Regards,<font color="black"><font size=+1><font color="blue">A<font color="red">ndrea<font color="blue">S</font color="red"><font size=+1><font size=1><font color="red">using xl2000
This message was edited by sen_edp on 2002-09-13 04:18
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
On 2002-09-13 03:50, fidgen wrote:
I've got a column in which each cell has a formula linking to a different worksheet.

The cells can either display a positive number (not 0) or N/A

Row 2 of each column has: (in this example for column B)

=MAX(B3:B10000) (10000 to allow it to automatically update as new rows are added)

But because some cells have N/A, MAX also displays #N/A rather than just ignoring them and displaying the largest integer in the column.

Any way round this?


Thanks
Hugh

Try to avoid #N/A. You could generate "NoValue" instead of #N/A, for example.

Having said that, you can use the following array formula in case you keep #N/A's:

=MAX(IF(ISNUMBER($B$3:$B$10000),$B$3:$B$10000))

To array-enter a formula, you need to hit control+shift+enter at the same time.

If you switch to "NoValue", you can just use

=MAX($B$3:$B$10000)
 

Watch MrExcel Video

Forum statistics

Threads
1,119,137
Messages
5,576,299
Members
412,716
Latest member
Ardin
Top