Averaging cell's...problems with Div/0

Hi guys.  First time poster here so be gentle with me.  I am looking fo
some assistance averaging a range of 1 to 3 numbers.

Here is what I have so far.

=(D3+F3+H3)/(IF(C3=0,0,1)+IF(E3=0,0,1)+IF(G3=0,0,1))


This works great.  What it does is checks to see if there is a value i
the cell, then counts it and divides by the right number.  I.E if yo
only have two values out of 3 filled in it divides the number by tw
instead of 3.

My problem... if all 3 fields are 0 then I get a divide by 0 error.
Any suggestion on how to fix this?  I don't want my spreadsheet to loo
messy before I start plugging in values.  What kind of formula can 
plug in here to have it display 0 if all 3 values are 0

--
Message posted from http://www.ExcelForum.com

0
6/30/2004 6:07:07 PM
excel 39879 articles. 2 followers. Follow

2 Replies
537 Views

Similar Articles

[PageSpeed] 49

=IF(AND(C3=0,E3=0,G3=0),0,(D3+F3+H3)/(IF(C3=0,0,1)+IF(E3=0,0,1)+IF(G3=0,0,1)
))

Are these cells really zero or are they blank, if blank you could use

=IF(COUNT(C3,E3,G3),SUM(D3,F3,H3)/COUNT(C3,E3,G3),0)
-- 

Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)


"Roy Bunch >" <<Roy.Bunch.18oe1t@excelforum-nospam.com> wrote in message
news:Roy.Bunch.18oe1t@excelforum-nospam.com...
> Hi guys.  First time poster here so be gentle with me.  I am looking for
> some assistance averaging a range of 1 to 3 numbers.
>
> Here is what I have so far.
>
> =(D3+F3+H3)/(IF(C3=0,0,1)+IF(E3=0,0,1)+IF(G3=0,0,1))
>
>
> This works great.  What it does is checks to see if there is a value in
> the cell, then counts it and divides by the right number.  I.E if you
> only have two values out of 3 filled in it divides the number by two
> instead of 3.
>
> My problem... if all 3 fields are 0 then I get a divide by 0 error.
> Any suggestion on how to fix this?  I don't want my spreadsheet to look
> messy before I start plugging in values.  What kind of formula can I
> plug in here to have it display 0 if all 3 values are 0.
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
terre08 (1112)
6/30/2004 6:37:44 PM
On Wed, 30 Jun 2004 13:07:07 -0500, Roy Bunch
<<Roy.Bunch.18oe1t@excelforum-nospam.com>> wrote:

>Hi guys.  First time poster here so be gentle with me.  I am looking for
>some assistance averaging a range of 1 to 3 numbers.
>
>Here is what I have so far.
>
>=(D3+F3+H3)/(IF(C3=0,0,1)+IF(E3=0,0,1)+IF(G3=0,0,1))
>
>
>This works great.  What it does is checks to see if there is a value in
>the cell, then counts it and divides by the right number.  I.E if you
>only have two values out of 3 filled in it divides the number by two
>instead of 3.
>
>My problem... if all 3 fields are 0 then I get a divide by 0 error. 
>Any suggestion on how to fix this?  I don't want my spreadsheet to look
>messy before I start plugging in values.  What kind of formula can I
>plug in here to have it display 0 if all 3 values are 0.
>

=AVERAGE(C3,E3,G3)

This will give a DIV/0 error if all three are not numbers, but does give a 0 if
all three are 0.

You could handle the error by either checking specifically for numeric entries
or using an ISERR function.


--ron
0
ronrosenfeld (3122)
6/30/2004 6:51:10 PM
Reply:

Similar Artilces:

Problem with "Application.Goto Reference" code line
In my Workbook_Open() subroutine, I run the following line of code: Application.Goto Reference:=Sheets("Sheet1").Range("A1"), scroll:=True This code works okay, but whenever I mark my "Sheet1" as hidden, the code no longer works. The error message that I get is Run-Time Error 1004 - Method 'Goto' of Object '_Application' Failed. Is there any way to use that line of code on a hidden sheet?? thanx The sheet needs to be visible for that code to work. "Robert Crandal" <nobody@gmail.com> wrote in message news:...

RMS 2.0 Integration Approach
Hi everyone, I have Commerce Server 2009 in place and would like to integrate it with RMS2.0. What are some ways in general that an external system can integrate with RMS2.0? Regards. ...

CRM 4.0 Report Deployment
Hi When I deploy a custom report (through Web UI) and run it, no data is displayed. The report header, footer, etc, display, but there is no data. To simplify the problem I created a very simple report with no parameters, which selects all from filteredsystemuser, and the problem was the same. This is in a test VPC, the whole environment is contained in the VPC. I am developing/publishing/viewing as the CRM administrator. The steps I took were: - Created a new report in Business Intelligence Development Studio using Wizard (installed in VPC) - Created a new DataSet called dsTest with a new D...

ARM compiler in Microsoft Platform Builder 5.0
Hello, I have a Freescale iMX31 (ARM11 Family). I know that ARM11 Famility corrisponds to core ARMv6 but on Microsoft Documentation I read that the ARM compiler support 4 and 5 (ARMv4 and ARMv5). Followint the link : http://msdn.microsoft.com/en-us/library/ms925478.aspx So I'm using a ARM11 processor with instruction set of ARM9 (ARMv4 - ARMv5). In this manner, there isn't and optiomization on compiled code. Some comments ? Thanks, Paolo On Nov 20, 2:18=A0pm, paolo patierno <paolopatie...@discussions.microsoft.com> wrote: > Hello, > I have a Freescale...

if isblank problem
I have written following formula on technical sheet of my workbook =if(isblank(q31:q36),0,sum(q31:q36)/4) which gives me correct answer true version ie 0 but when try to enter on summary sheet as =if(isblank(technical!q31:q36),0,sum(technical!q31:q36)/4) i get false version any ideas why? Thanks Tina Hi try =if(countblank(technical!q31:q36)=6,0,sum(technical!q31:q36)/4) -- Regards Frank Kabel Frankfurt, Germany tina wrote: > I have written following formula on technical sheet of my workbook > =if(isblank(q31:q36),0,sum(q31:q36)/4) > which gives me correct answer true version ...

Internet Mail Service Problem
This is a multi-part message in MIME format. ------=_NextPart_000_000B_01C49F0B.EE172F50 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi Team, For quite sometime my Internet Mail Service for Exchange 5.5 get stopped = of its own. My present environment setup is:-=20 1. I have 5 Exchange administrative console located at 3 three = different building.=20 2. My OS is Windows 2000 Server with SP4 3. Exchange 5.5 with SP4=20 4.Running NAV for Exchange built 2.18=20 After going through application logs, I found following E...

How to see calculation and heading in same cell.
I would like the cell to perform a calculation and then display the answer as will as a heading. In other words the answer and the heading will appear in the same cell. Perhaps you mean something like ="Heading Name: "&A1+B1 ******************* ~Anne Troy www.OfficeArticles.com "Jeracho" <Jeracho@discussions.microsoft.com> wrote in message news:F34BACB9-E2DA-448A-924D-46A475F98F91@microsoft.com... > I would like the cell to perform a calculation and then display the answer as > will as a heading. In other words the answer and the heading will appear in...

New Project, Different Problem: AutoFilter?
I'm creating a database of outside trainers. Each trainer can handle multiple categories of training. It is essential that the training manager be able to create lists for specific categories as the need arises, as well as being able to do more standard sorts such as Name. Rather than making multiple entries for each Trainer (one for each category, which may reach 10+), I'd like to allow multiple entries in the Category column utilizing a key for each category. It seems that AutoFilter would be the ideal method for creating this db. Well, actually Advanced AutoFilter, since...

Access ODBC problem: Excel cannot get float columns
I need some help I'm trying to get Access external data from an Excel workbook, using the MS Query feature. Everything is ok except when I try to fetch some tables that ODBC refuses to get data from. The error message tells that the MS Access ODBC driver doesn't allow some columns to be transmited because of its number of characters. The most strange thing is that I can see the data from MS Query correctly, but I caannot get it back from Excel. After some tries, I thing it occurs only with real typed columns. Can anybody help me? I use Windows XP Home + Office 2000 spanish versio...

Thread Problems
Found this from MSDN->codeguru, can't seem to figure out the problem.... class ThreadClass { protected: HANDLE m_hThread; //Thread handle bool m_bActive; //activity indicator DWORD m_lpId; //Thread ID public: ThreadClass(){} virtual ~ThreadClass(){Kill();} //Thread Management bool CreateNewThread() { m_hThread = CreateThread(NULL, 0, (LPTHREAD_START_ROUTINE)_ThreadFunc, (LPVOID) this, 0, (LPDWORD) &m_lpId); if (m_hThread == NULL) return false; m_bActive = true; return true; }; //Wait for thread to end bool Wait() {return (WAIT_OBJECT_0 == WaitForSingleObject(m_hT...

View Only Admin
I am having problems with the view only admin delegation. I put the appropriate group into the local admin on each exchange box but after a couple of days exchange removes this group. What is causing this and how do fix it so it leaves this security group in there. Thanks for your help -- Justin This can happen due to conflicting policies or rules. Try to enable Auditing for the security log and enable loging for Exchange in system manger and check results. "Justin" wrote: > I am having problems with the view only admin delegation. I put the > appropriate group ...

Internal Mail Problem
Hi all, I want to restrict some users to send & receive mail from internal user only, so I try to remove their smtp address and leave the x.400 address in the properties. This works well in Exchange 5.5 environment. But, how can I do this in Exchange 2003 environment, I cannot removed the smtp address in Exchange 2003, Please help, thanks. On Sun, 7 Aug 2005 00:46:12 +0800, <a> wrote: >Hi all, I want to restrict some users to send & receive mail from internal >user only, so I try to remove their smtp address and leave the x.400 address >in the properties. ...

Moving Average Trendline
The graph I am creating has a lot of noise in it. I want to smooth it out. Right now I am using a moving average trendline that kind of does the trick. When I do this I want to be able to work with the points that it has created to make the trendline. Is there a way to get the points that this line makes up? Or is there a way to filter the points in the graph such as take every tenth data point or would I have to do that with the original set used to make the graph by using a macro or something like that? You can calculate your own moving average. Say your data is in column D, sta...

Problem Printing w/Calendar Assistant
Every time I attempt to print a calendar using the Assistant it prints one page and then stops. I have tried to print multiple pages of a weekly calendar and each time it prints one week and then stops. I love the layout and colors of the calendar but I can not get more than one weekly calendar to print no matter how many weeks I select to be printed. Calendar Printing Assistant is known to be quirky. It works best with Outlook 2007. I cant say i know a fix for your issue. However, if you cant work around for this issue or you need a better printing solution, consider using...

problems with windows live mail
when windows live mail starts to download from my gmail pop account I always a box pop up saying "Internet Security Warning" dialog box "The server you are connected to is using a security certificate that could not be verified. "The certificate's CN name does not match the passed value." "Do you want to continue using this server?" I click yes and it works, connects and downloads messages. Until the email client is close and restarted that is. Then once again on the first access the security certificate message is displayed. How do I get this to w...

GP 9.0 Password Issues
After you upgrade to 9.0, the first time a user logs in, the user is forced to change his password. GP encrypts the password (it encrypts all user passwords other than sa). After this stupid encryption, none of the other programs such as SQL Query Analyzer, Crystal Reports, Access etc can recognize this password. Unlike in 8.0, an user who was able to login to Crystal before can't login now with GP credentials. Has anyone experienced this ? What is the resolution to this if any. Thanks Girish It should have encrypted it in 8.0 as well. AFAIK, it's done that the entire time ...

Outlook attachment problems
Dear Our oversea office sender using Outlook Sending message (HTML) insert a picture sending message to my office, when I have received the attachment auto duplicate one set in the message. I have asked the sender try remove the picture and FORWARD to my e-mail, but the attachment sill in the message and auto duplicate one set attachment. Out overseas office using MS SBS , user setup WG mode for outlook download message. I am not sure which part problems or virus effect. Please advice how to solve out the problems ASAP. JACKIE ...

When will CRM 2.0 be released?
I was wondering if anyone has a firm(ish) idea when version 2 will be released. I've heard 2005, but that is a little vague. My company has identified a few gaps that should be resolved with the 2005 expected enhancements. If the release is put off until this time next year, we will go ahead and correct now. However, we would like to hold off wasting time\money is Microsoft will deliver within the 1st two quarters of 2005. Thoughts? The latest word is that CRM 2005 should be release sometime in calendar Q2 of 2005. Matt Parks MVP - Microsoft CRM ---------------------------...

Fax Server SBS2003 and CRM PRO 3.0
Hi I have 2 server 2003, one is a SBS2003 Standard with exchange and Fax service of Microsoft and one is Windows Server 2003 Standard with SQL 2005 and CRM 3.0. I have this problem ..... how i can send the Fax from the SBS2003 in the CRM 3.0? there is a router for the fax at the same of router for enchange? Thanks ...

Interesting problem in Money 2005 and MSN link
Money 2005 Premium. I swore I'd never link to MSN on the web but yesterday somehow got there by trying to set up an MBNA card. I finally figured it wasn't worth it so I removed everything - passport and all data. Here is the interesting thing. Previously, my mny file was 46MB after a shrink. After removing everything, it shrank to 41MB. I didn't really care since I had a backup but I decided to explore further. In putting everything through MSN and then disabling it all, it reversed the sign on 2 transactions (one in 1998, the other in 2003) but nothing else was touched. All 22,...

Coping part of a cell content into a seperate cell
Hi I have two cells, one containing first and middle name and another one with surname. I want to combine the first name and surname into a separate cell, can you advise how I can just copy the first name and miss out the middle name please?? Thanks Caz Hi, I assume that the midle name is separated by a space from the first name and is in column A and the last name in column B =TRIM(LEFT(A2,FIND(" ",a2)-1))&" "&B2 "Caz H" wrote: > Hi > I have two cells, one containing first and middle name and another one with &g...

Very strange problems while running Great Plains on workstations
I notice that a few workstations in an office I support are having problems when they run Great Plains. Excel, Outlook, Word and Dynamics.exe are showing in the Application event log as being Hanging or Faulting. I also see Fault Bucket errors, but when I search online I cannot find any information online. Here is one of the Fault Bucket errors: 3:15:35 pm 28-Sep-06 Application Hang None 1001 N/A Fault bucket 296734104. Also, these workstations are experiencing problems printing PDF files. Has anyone out there seen this behavior and if so, how can these problems be fixed? Thank you, ...

Help With Problem: Nested IF Function, Office2K v Office2003
Hello Group, I'm looking for some help with a nested IF worksheet function. Shown below is a formula that I'm using in a model. =SUM(IF(worksheet!$A$3:$A$54=DATEVALUE("9/1/2005"),IF(worksheet!$B$3:$B$54="<90",worksheet!$C$3:$C$54,0),0)) If short, what I'm trying to do is scan the data in two columns and for any cells which meet the TRUE case in both columns, then sum the third numbers for all cases returning TRUE-TRUE. If either cell returns a FALSE, then enter 0. I created the original formulas and model in Office2000 and have used the model for more tha...

Adding a formula to the same cell (H5) on every tab
I have an inventory spreadsheet with 125 tabs. The tabs are numbered 1 through 125. The are identical except for the data below the column headings. If I wanted to put a formula in H5 on every tab, can it be done other than manually opening every tab and typing it? One additional question: If I add a Summary Tab, how could I show the value of a specific cell on each tab without manually entering it? I show the formula I'm using bring B3 to the summary for every tab: A B 1 Unit Value 2 1 ='1'!B3 3 2 ='2'!B3 4 3 ='3'!B3 5 4 ='4'!B3 6 5 ='5'!B3 7...

Average Attrition Rate
Our company attrition rate calculation = Attrits / Average Headcount. I want to use the same calculation for any set of agents, whether th agents are in classroom trianing or production. The problem is tha the training group needs to back into the number of agents that need t start a training class. For example. If you have a class that starts with 25 and ends with 2 there is a 20% fallout but due to average headcount in the calculatio the attrition rate is 22.1%. HOW CAN I HAVE A FORMULA THAT BACKS INTO THE 25 THAT I WOULD NEED T START IN CLASSROOM TRAINING? I WOULD LIKE TO USE THE 20 ...