Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 6
Default embedding "ISERROR" function into an "IF" statement

I'm trying to add up a column of numbers and some of the cells have
#N/A or #DIV/0! which causes my summation to come up as an error as
well.

I'd like to keep my #N/A's in the cells and use the ISERROR function
and thought it should be embedded into an IF statement. Can any help
me do this?

Thanks

Mike

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,726
Default embedding "ISERROR" function into an "IF" statement

=SUM(IF(NOT(ISERROR(C3:C31)),C3:C31))

as an array formula, commit it with Ctrl-Shift-Enter, not just Enter

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


wrote in message
oups.com...
I'm trying to add up a column of numbers and some of the cells have
#N/A or #DIV/0! which causes my summation to come up as an error as
well.

I'd like to keep my #N/A's in the cells and use the ISERROR function
and thought it should be embedded into an IF statement. Can any help
me do this?

Thanks

Mike



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 35,218
Default embedding "ISERROR" function into an "IF" statement

Alternative to Bob's function:

=sum(if(isnumber(C3:C31),C3:C31))

Still array entered.

It doesn't use =iserror(), but I think it's easier to understand <bg.

wrote:

I'm trying to add up a column of numbers and some of the cells have
#N/A or #DIV/0! which causes my summation to come up as an error as
well.

I'd like to keep my #N/A's in the cells and use the ISERROR function
and thought it should be embedded into an IF statement. Can any help
me do this?

Thanks

Mike


--

Dave Peterson
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,726
Default embedding "ISERROR" function into an "IF" statement

Hardly intuitive.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Harlan Grove" wrote in message
ups.com...
wrote...
I'm trying to add up a column of numbers and some of the cells have
#N/A or #DIV/0! which causes my summation to come up as an error as
well.

I'd like to keep my #N/A's in the cells and use the ISERROR function
and thought it should be embedded into an IF statement. Can any help
me do this?


Don't bother with ISERROR (or ISNUMBER) or IF. Just use

=SUMIF(YourRangeHere,"<0")+SUMIF(YourRangeHere,"0 ")





  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 733
Default embedding "ISERROR" function into an "IF" statement

Bob Phillips wrote...
Hardly intuitive.

....
"Harlan Grove" wrote in message

....
=SUMIF(YourRangeHere,"<0")+SUMIF(YourRangeHere," 0")


Depends on one's understanding of how SUMIF works. SUMIF with <, <=, ,
= numeric comparisons only sums numbers and excludes error values. In a similar way, COUNTIF can count the instances of particular error values, e.g., =COUNTIF(A:A,"#N/A"). Once one learns how COUNTIF and SUMIF works, maybe then it becomes intuitive.


  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,726
Default embedding "ISERROR" function into an "IF" statement

intuitive, obtained through intuition rather than from reasoning or
observation


"Harlan Grove" wrote in message
ups.com...
Bob Phillips wrote...
Hardly intuitive.

...
"Harlan Grove" wrote in message

...
=SUMIF(YourRangeHere,"<0")+SUMIF(YourRangeHere, "0")


Depends on one's understanding of how SUMIF works. SUMIF with <, <=, ,
= numeric comparisons only sums numbers and excludes error values. In a
similar way, COUNTIF can count the instances of particular error values,
e.g., =COUNTIF(A:A,"#N/A"). Once one learns how COUNTIF and SUMIF works,
maybe then it becomes intuitive.




  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 733
Default embedding "ISERROR" function into an "IF" statement

Bob Phillips wrote...
intuitive, obtained through intuition rather than from reasoning or
observation

....

Intuition is independent of experience and education?

Intuition is preferable to reasoning when it comes to writing
spreadsheet formulas?

  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,726
Default embedding "ISERROR" function into an "IF" statement

Now you are just twisting what has been said. Pray point to the part where I
said that intuition is preferable to reasoning ...

I said, hardly intuitive. The word was carefully chosen, and by the
definition of intuitive, it wasn't, it isn't, it never will be, regardless
of experience or education. That formula is not intuitive, I won't twist
anything just quote you, 'Once one learns how COUNTIF and SUMIF works', that
is not intuitive.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Harlan Grove" wrote in message
.com...
Bob Phillips wrote...
intuitive, obtained through intuition rather than from reasoning or
observation

...

Intuition is independent of experience and education?

Intuition is preferable to reasoning when it comes to writing
spreadsheet formulas?



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Can an "IF()" statement contain a print function MAJones Excel Discussion (Misc queries) 2 July 27th 06 12:25 AM
Combining an If Statement with NETWORKDAYS function Shirley Munro Excel Discussion (Misc queries) 1 June 21st 06 12:42 PM
IF Statement with Average Function results in #Value! Paul Excel Discussion (Misc queries) 5 December 28th 04 09:11 AM
Combining SUM Function with Nested If Statement Somecallmejosh Excel Discussion (Misc queries) 3 December 6th 04 05:25 PM
How to resize a comment box, by embedding code into a function? JJ Excel Worksheet Functions 13 November 16th 04 09:44 PM


All times are GMT +1. The time now is 03:14 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"