# MaxNErr(TheRange)

MaxNErr is a function within ddGlobal and forms part of the Func_Numeric suite of macros. This macro was originally created as I tend to use controlled errors in my formulas.  As such, since I want to be able to ignore an error and still do some basic mathematical instructions I needed to write a whole set of “NErr” (No Error”) macros.

If you want to calculate the maximum number out of 5 numbers, and one of the cells have an error in it, then the standard Excel function (Large, or Max) will get seriously confused and just return the first error it encounters.

See the below example for how MaxNErr works:

 Scenario 1 Scenario 2 Scenario 3 Value: A 10 10 10 Value: B 20 20 # N/A Value: C 30 # N/A 30 Value: D 40 40 # NAME? Value: E 50 50 50 Max(A:E) 50 # N/A # N/A Large(A:E,1) 50 # N/A # N/A MaxNErr(A:E) 50 50 50

The formula for calculating the maximum value in the five rows is:

`MaxNErr(B2:B6)`

Below is the source code for the macro

```Function MaxNErr(TheRange As Range) As Double
'The function return the maximum number in a range.
'Last updated by Denis Dell
Dim i, j As Integer
With TheRange
For i = 1 To .Rows.Count
For j = 1 To .Columns.Count
If IsNumeric(.Cells(i, j).Value) Then
If .Cells(i, j).Value > MaxNErr Then MaxNErr = .Cells(i, j).Value
End If
Next j
Next i
End With
End Function```

Macro is short, efficient and it works.  I am sure it could be optimised more, but right now it does what it says on the tin.

This entry was posted in Articles, ddGlobal, Puzzles and tagged , . Bookmark the permalink.