Formula errors

Y

ynissel

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.
 
B

Bernie Deitrick

Array enter (enter using Ctrl-Shift-Enter)

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

ynissel

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.
 
M

Morrigan

=SUMIF(A:A,">0")+SUMIF(A:A,"<=0")

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.
Click to expand...
 
B

Bernie Deitrick

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
 
G

Gord Dibben

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
 
Y

ynissel

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

Gord Dibben said:
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
Click to expand...
 
Top