Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default REMOVE THE #DIV/0! ERROR FROM FORMULA WHEN FIGURING MULTIPLE AVERA

I am trying to formulate a cell to figure averages from cells that have
averages.
My current formula reads
=AVERAGE(D33,G33,J33,M33,P33,S33,V33,Y33)
I am getting the error due to blank cells that have not been filled in yet.
Any suggestions?
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11,501
Default REMOVE THE #DIV/0! ERROR FROM FORMULA WHEN FIGURING MULTIPLE AVERA

Settting aside that you shouldn't really be taking an average of an average
try this

=SUM(D33,G33,J33,M33,P33,S33,V33,Y33)/COUNT(D33,G33,J33,M33,P33,S33,V33,Y33)

Mike

"Security Dave" wrote:

I am trying to formulate a cell to figure averages from cells that have
averages.
My current formula reads
=AVERAGE(D33,G33,J33,M33,P33,S33,V33,Y33)
I am getting the error due to blank cells that have not been filled in yet.
Any suggestions?

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,651
Default REMOVE THE #DIV/0! ERROR FROM FORMULA WHEN FIGURING MULTIPLE AVERA

=IF(COUNT(D33,G33,J33,M33,P33,S33,V33,Y33),AVERAGE (D33,G33,J33,M33,P33,S33,V33,Y33),"")
--
David Biddulph

"Security Dave" <Security wrote in message
...
I am trying to formulate a cell to figure averages from cells that have
averages.
My current formula reads
=AVERAGE(D33,G33,J33,M33,P33,S33,V33,Y33)
I am getting the error due to blank cells that have not been filled in
yet.
Any suggestions?



  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default REMOVE THE #DIV/0! ERROR FROM FORMULA WHEN FIGURING MULTIPLE AVERA

I'm assuming you mean this happens when *none* of the cells contain data. If
*any* cell contained data it should work (as long as there are no error
values in the range) and AVERAGE would ignore the empty cells and text.

Try this:

=IF(COUNT(D33,G33,J33,M33,P33,S33,V33,Y33),AVERAGE (D33,G33,J33,M33,P33,S33,V33,Y33),"")

--
Biff
Microsoft Excel MVP


"Security Dave" <Security wrote in message
...
I am trying to formulate a cell to figure averages from cells that have
averages.
My current formula reads
=AVERAGE(D33,G33,J33,M33,P33,S33,V33,Y33)
I am getting the error due to blank cells that have not been filled in
yet.
Any suggestions?



  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2
Default REMOVE THE #DIV/0! ERROR FROM FORMULA WHEN FIGURING MULTIPLE A

Thanks very much for your help. It worked like a charm.

One more if you have the time. Same situation, except I am figuring
averages between 2 numbers.

Simple formula of =U20/T20
Any help would be appreciated.

"David Biddulph" wrote:

=IF(COUNT(D33,G33,J33,M33,P33,S33,V33,Y33),AVERAGE (D33,G33,J33,M33,P33,S33,V33,Y33),"")
--
David Biddulph

"Security Dave" <Security wrote in message
...
I am trying to formulate a cell to figure averages from cells that have
averages.
My current formula reads
=AVERAGE(D33,G33,J33,M33,P33,S33,V33,Y33)
I am getting the error due to blank cells that have not been filled in
yet.
Any suggestions?






  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,651
Default REMOVE THE #DIV/0! ERROR FROM FORMULA WHEN FIGURING MULTIPLE A

I'm not sure where the "average" comes into your U20/T20 formula, but you
could try =IF(T20,U20/T20,"") or =IF(T20=0,"",U20/T20)
If you want to distinguish between a blank and a zero in T20 you could do
that, for example =IF(T20="","",IF(T20=0,"infinite ratio",U20/T20))
--
David Biddulph

"Security Dave" wrote in message
...
Thanks very much for your help. It worked like a charm.

One more if you have the time. Same situation, except I am figuring
averages between 2 numbers.

Simple formula of =U20/T20
Any help would be appreciated.

"David Biddulph" wrote:

=IF(COUNT(D33,G33,J33,M33,P33,S33,V33,Y33),AVERAGE (D33,G33,J33,M33,P33,S33,V33,Y33),"")
--
David Biddulph

"Security Dave" <Security wrote in
message
...
I am trying to formulate a cell to figure averages from cells that have
averages.
My current formula reads
=AVERAGE(D33,G33,J33,M33,P33,S33,V33,Y33)
I am getting the error due to blank cells that have not been filled in
yet.
Any suggestions?






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
How can I remove multiple 'errors' in Excel error checking, at the same time??? [email protected] Excel Discussion (Misc queries) 2 May 11th 23 11:42 AM
My formula have error, so I remove errors. Feejo Excel Discussion (Misc queries) 2 January 3rd 08 04:31 PM
Figuring out a formula iwasfloyd Excel Worksheet Functions 1 October 2nd 07 09:52 AM
remove error value when formula exists for empty cells Cyrus Excel Worksheet Functions 2 February 17th 06 05:19 PM
Figuring Out A formula to... ken New Users to Excel 1 October 7th 05 01:44 AM


All times are GMT +1. The time now is 01:02 PM.

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"