MAX function doesn't like N/A!

[fidgen]

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

 

[WillR]

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.

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]

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]

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)