Thanks - this will save me a lot of time !!

"Gord Dibben" wrote:

If you wish to alter all your existing formulas run this macro on the cells.

Sub NATrapAdd()
Dim myStr As String
Dim cel As Range
For Each cel In Selection
If cel.HasFormula = True Then
If Not cel.Formula Like "=IF(ISNA*" Then
myStr = Right(cel.Formula, Len(cel.Formula) - 1)
cel.Value = "=IF(ISNA(" & myStr & "),""""," & myStr & ")"
End If
End If
Next
End Sub


Gord Dibben Excel MVP

On Tue, 19 Jul 2005 10:21:03 -0700, "ynissel"
wrote:

thants what I though - a pain in the rear but it will work.
Thanks

"Bernie Deitrick" wrote:

Yosef,

It would be easier of set up a new table in a new range to reference, using

=IF(ISERROR(H3),"",H3)

or modify your existing formulas in H3:L3

=IF(ISERROR(Old Formula),"",Old Formula)

HTH,
Bernie
MS Excel MVP


"ynissel" wrote in message
...
Thanks !
My actual formula is a little more complicated.
=MROUND(IF(COUNT(H3:L3)4,AVERAGE(LARGE(H3:L3,{1,2 ,3,4})),IF(COUNT(H3:L3)3,AVERAGE(LARGE(H3:L3,{1,2 ,3})),AVERAGE(H3:L3))),0.125)

Is there any way to just ignore the error or do I have to put the if
statement into each average.

"Bernie Deitrick" wrote:

Array enter (enter using Ctrl-Shift-Enter)

=SUM(IF(ISERROR(A1:A3),0,A1:A3))

--
HTH,
Bernie
MS Excel MVP


"ynissel" wrote in message
...
I am summing up bunch of lookup tables. My problem is that some of the
lookups will result in an N/A. Is there a way in my sum formula to ignore
the N/A s ?
Thanks,
Yosef

i.e.
=sum(a1:a3) and sometime a2 will be #n/A.