Formula errors

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.

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

Originally Posted by 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.

Array enter (enter using Ctrl-Shift-Enter)

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

--
HTH,
Bernie
MS Excel MVP


"ynissel" <ynissel@discussions.microsoft.com> wrote in message
news:0FE945C0-8C27-44D1-8B14-E264FD55A5F0@microsoft.com...
>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.

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" <ynissel@discussions.microsoft.com> wrote in message
> news:0FE945C0-8C27-44D1-8B14-E264FD55A5F0@microsoft.com...
> >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.
>
>
>

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" <ynissel@discussions.microsoft.com> wrote in message
news:0519EE46-BF87-442D-8185-A1FF3306E6AE@microsoft.com...
> 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" <ynissel@discussions.microsoft.com> wrote in message
>> news:0FE945C0-8C27-44D1-8B14-E264FD55A5F0@microsoft.com...
>> >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.
>>
>>
>>

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" <ynissel@discussions.microsoft.com> wrote in message
> news:0519EE46-BF87-442D-8185-A1FF3306E6AE@microsoft.com...
> > 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" <ynissel@discussions.microsoft.com> wrote in message
> >> news:0FE945C0-8C27-44D1-8B14-E264FD55A5F0@microsoft.com...
> >> >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.
> >>
> >>
> >>
>
>
>

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"
<ynissel@discussions.microsoft.com> 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" <ynissel@discussions.microsoft.com> wrote in message
>> news:0519EE46-BF87-442D-8185-A1FF3306E6AE@microsoft.com...
>> > 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" <ynissel@discussions.microsoft.com> wrote in message
>> >> news:0FE945C0-8C27-44D1-8B14-E264FD55A5F0@microsoft.com...
>> >> >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.
>> >>
>> >>
>> >>
>>
>>
>>

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"
> <ynissel@discussions.microsoft.com> 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" <ynissel@discussions.microsoft.com> wrote in message
> >> news:0519EE46-BF87-442D-8185-A1FF3306E6AE@microsoft.com...
> >> > 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" <ynissel@discussions.microsoft.com> wrote in message
> >> >> news:0FE945C0-8C27-44D1-8B14-E264FD55A5F0@microsoft.com...
> >> >> >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.
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>