|
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. |
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. |
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. |
=SUMIF(A:A,"0")+SUMIF(A:A,"<=0") ynissel Wrote: 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. -- Morrigan ------------------------------------------------------------------------ Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094 View this thread: http://www.excelforum.com/showthread...hreadid=388393 |
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. |
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. |
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. |
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. |
| All times are GMT +1. The time now is 02:43 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com