A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

#Div/0 error, can't get rid of it



 
 
Thread Tools Display Modes
  #1  
Old December 19th, 2009, 09:13 PM posted to microsoft.public.excel.worksheet.functions
John
external usenet poster
 
Posts: 563
Default #Div/0 error, can't get rid of it

Hi Everyone
I use this array formula and it works well,
=IF(COUNT(J5:J36)1,"",AVERAGE(IF(J5:J360,J5:J36, "")))
But if I only have 0 in that column, I get #Div/0, I tried this version
=IF(ISERROR(COUNT($K$5:$K$36)=0),"",AVERAGE(IF($K$ 5:$K$360,$K$5:$K$36,""))) but
to no avail.
Anyone can help me with this.
Thanking you in advance
John

  #2  
Old December 19th, 2009, 09:37 PM posted to microsoft.public.excel.worksheet.functions
Joe User[_2_]
external usenet poster
 
Posts: 757
Default #Div/0 error, can't get rid of it

"John" wrote:
Subject: #Div/0 error, can't get rid of it


"John" wrote in message
...
Hi Everyone
I use this array formula and it works well,
=IF(COUNT(J5:J36)1,"",AVERAGE(IF(J5:J360,J5:J36, "")))
But if I only have 0 in that column, I get #Div/0, I tried this version
=IF(ISERROR(COUNT($K$5:$K$36)=0),"",AVERAGE(IF($K$ 5:$K$360,$K$5:$K$36,"")))
but to no avail.
Anyone can help me with this.
Thanking you in advance
John


  #3  
Old December 19th, 2009, 09:37 PM posted to microsoft.public.excel.worksheet.functions
Otto Moehrbach[_2_]
external usenet poster
 
Posts: 716
Default #Div/0 error, can't get rid of it

The error is occurring with the average, where you have a division. Move
the ISERROR to the average. HTH Otto

"John" wrote in message
...
Hi Everyone
I use this array formula and it works well,
=IF(COUNT(J5:J36)1,"",AVERAGE(IF(J5:J360,J5:J36, "")))
But if I only have 0 in that column, I get #Div/0, I tried this version
=IF(ISERROR(COUNT($K$5:$K$36)=0),"",AVERAGE(IF($K$ 5:$K$360,$K$5:$K$36,"")))
but to no avail.
Anyone can help me with this.
Thanking you in advance
John


  #4  
Old December 19th, 2009, 09:41 PM posted to microsoft.public.excel.worksheet.functions
Joe User[_2_]
external usenet poster
 
Posts: 757
Default #Div/0 error, can't get rid of it

"John" wrote:
Subject: #Div/0 error, can't get rid of it
=IF(COUNT(J5:J36)1,"",AVERAGE(IF(J5:J360,J5:J36, "")))


Try:

=If(COUNTIF(J5:J36,"0")=0, "", AVERAGE(IF(J5:J360,J5:J36,"")))


----- original message -----

"John" wrote in message
...
Hi Everyone
I use this array formula and it works well,
=IF(COUNT(J5:J36)1,"",AVERAGE(IF(J5:J360,J5:J36, "")))
But if I only have 0 in that column, I get #Div/0, I tried this version
=IF(ISERROR(COUNT($K$5:$K$36)=0),"",AVERAGE(IF($K$ 5:$K$360,$K$5:$K$36,"")))
but to no avail.
Anyone can help me with this.
Thanking you in advance
John


  #5  
Old December 19th, 2009, 09:48 PM posted to microsoft.public.excel.worksheet.functions
Rick Rothstein[_2_]
external usenet poster
 
Posts: 2,013
Default #Div/0 error, can't get rid of it

Assuming you really mean to average only non-zero numbers, I would use the
array-entered formula...

=IF(ISERROR(AVERAGE(IF(J5:J360,J5:J36,""))),"",AV ERAGE(IF(J5:J360,J5:J36,"")))

where you simply test your functional expression for an error and react to
it.

--
Rick (MVP - Excel)


"John" wrote in message
...
Hi Everyone
I use this array formula and it works well,
=IF(COUNT(J5:J36)1,"",AVERAGE(IF(J5:J360,J5:J36, "")))
But if I only have 0 in that column, I get #Div/0, I tried this version
=IF(ISERROR(COUNT($K$5:$K$36)=0),"",AVERAGE(IF($K$ 5:$K$360,$K$5:$K$36,"")))
but to no avail.
Anyone can help me with this.
Thanking you in advance
John


  #6  
Old December 19th, 2009, 10:02 PM posted to microsoft.public.excel.worksheet.functions
Joe User[_2_]
external usenet poster
 
Posts: 757
Default #Div/0 error, can't get rid of it

ERRATA....

I wrote:
=If(COUNTIF(J5:J36,"0")=0, "", AVERAGE(IF(J5:J360,J5:J36,"")))


Should be:

If(COUNTIF(J5:J36,"0")=0, "", AVERAGE(IF(J5:J360,J5:J36,"")))


----- original message -----

"Joe User" joeu2004 wrote in message
...
"John" wrote:
Subject: #Div/0 error, can't get rid of it
=IF(COUNT(J5:J36)1,"",AVERAGE(IF(J5:J360,J5:J36, "")))


Try:

=If(COUNTIF(J5:J36,"0")=0, "", AVERAGE(IF(J5:J360,J5:J36,"")))


----- original message -----

"John" wrote in message
...
Hi Everyone
I use this array formula and it works well,
=IF(COUNT(J5:J36)1,"",AVERAGE(IF(J5:J360,J5:J36, "")))
But if I only have 0 in that column, I get #Div/0, I tried this version
=IF(ISERROR(COUNT($K$5:$K$36)=0),"",AVERAGE(IF($K$ 5:$K$360,$K$5:$K$36,"")))
but to no avail.
Anyone can help me with this.
Thanking you in advance
John



  #7  
Old December 19th, 2009, 10:16 PM posted to microsoft.public.excel.worksheet.functions
John
external usenet poster
 
Posts: 563
Default #Div/0 error, can't get rid of it

Thank you Rick
Works like a charm.
Best wishes for the Holiday
Regards
John
"Rick Rothstein" wrote in message
...
Assuming you really mean to average only non-zero numbers, I would use the
array-entered formula...

=IF(ISERROR(AVERAGE(IF(J5:J360,J5:J36,""))),"",AV ERAGE(IF(J5:J360,J5:J36,"")))

where you simply test your functional expression for an error and react to it.

--
Rick (MVP - Excel)


"John" wrote in message
...
Hi Everyone
I use this array formula and it works well,
=IF(COUNT(J5:J36)1,"",AVERAGE(IF(J5:J360,J5:J36, "")))
But if I only have 0 in that column, I get #Div/0, I tried this version
=IF(ISERROR(COUNT($K$5:$K$36)=0),"",AVERAGE(IF($K$ 5:$K$360,$K$5:$K$36,"")))
but to no avail.
Anyone can help me with this.
Thanking you in advance
John



  #8  
Old December 19th, 2009, 10:29 PM posted to microsoft.public.excel.worksheet.functions
John
external usenet poster
 
Posts: 563
Default #Div/0 error, can't get rid of it

Thank you Otto
Best Wishes for the Holiday
Regards
John
"Otto Moehrbach" wrote in message
...
The error is occurring with the average, where you have a division. Move the
ISERROR to the average. HTH Otto

"John" wrote in message
...
Hi Everyone
I use this array formula and it works well,
=IF(COUNT(J5:J36)1,"",AVERAGE(IF(J5:J360,J5:J36, "")))
But if I only have 0 in that column, I get #Div/0, I tried this version
=IF(ISERROR(COUNT($K$5:$K$36)=0),"",AVERAGE(IF($K$ 5:$K$360,$K$5:$K$36,"")))
but to no avail.
Anyone can help me with this.
Thanking you in advance
John



  #9  
Old December 19th, 2009, 10:32 PM posted to microsoft.public.excel.worksheet.functions
John
external usenet poster
 
Posts: 563
Default #Div/0 error, can't get rid of it

Hi Joe
This one works well, thank you
Best wishes for the Holiday
Regards
John
"Joe User" joeu2004 wrote in message
...
ERRATA....

I wrote:
=If(COUNTIF(J5:J36,"0")=0, "", AVERAGE(IF(J5:J360,J5:J36,"")))


Should be:

If(COUNTIF(J5:J36,"0")=0, "", AVERAGE(IF(J5:J360,J5:J36,"")))


----- original message -----

"Joe User" joeu2004 wrote in message
...
"John" wrote:
Subject: #Div/0 error, can't get rid of it
=IF(COUNT(J5:J36)1,"",AVERAGE(IF(J5:J360,J5:J36, "")))


Try:

=If(COUNTIF(J5:J36,"0")=0, "", AVERAGE(IF(J5:J360,J5:J36,"")))


----- original message -----

"John" wrote in message
...
Hi Everyone
I use this array formula and it works well,
=IF(COUNT(J5:J36)1,"",AVERAGE(IF(J5:J360,J5:J36, "")))
But if I only have 0 in that column, I get #Div/0, I tried this version
=IF(ISERROR(COUNT($K$5:$K$36)=0),"",AVERAGE(IF($K$ 5:$K$360,$K$5:$K$36,"")))
but to no avail.
Anyone can help me with this.
Thanking you in advance
John




 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 07:05 AM.


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