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
491 Views

Similar Articles

[PageSpeed] 27

=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:

How to make a cell equal another cells colour?
I have conditional formatting on a set of values to highlight high/low values using a 2-colour scale. Is there a way I can get another set of cells to mirror the colour alone without changing their values? Many thanks in advance Sam.D Have them use the same conditional format rules, simply references the cells they are "mirroring". -- Best Regards, Luke M "Sam.D" <SamD@discussions.microsoft.com> wrote in message news:B6BAC7A4-17F8-4A53-BBF3-9BCED618642F@microsoft.com... >I have conditional formatting on a set of values to highlight high...

Problem with Spam filter in OL2003
I'm having a problem with Spam filtering in OL2003. Under Junk email Options I have the level set to High and within International I have a number of TLDs set. All appears to be set correctly but when I receive email it all goes into my Inbox. I do have a Junk Email folder but it has a red crossed circle on it. Maybe this is normal but I'm not sure. Is there some other setting that I am unaware of which is needed to activate the spam blocking? Thanks Bob <bob1030@hotmail.com> wrote: > I'm having a problem with Spam filtering in OL2003. Under Junk email &g...

deleting a file in VC++ 6.0
Can someone show me how to delete a known file within a VC++ 6.0 MFC app. Thanks An MFC way to do it would be CFile::Remove() http://msdn2.microsoft.com/en-us/library/a9skzxhx(VS.80).aspx Tom "Ed" <eddie@hvc.rr.com> wrote in message news:gb%0i.2$R97.0@trndny03... > Can someone show me how to delete a known file within a VC++ 6.0 MFC app. > Thanks > > The same way you delete it in a Win32 app, a console app, a VS2005 app, etc: ::DeleteFile if(!::DeleteFile(pathnamehere)) { /* failed */ DWORD err = ::GetLastError(); ...

CondForm not applying to zero, "0", when it should.
I've just searched through all my posts in this ng. I'm almost 99% positive that I've run into this "zero" problem before. I've set up conditional formatting in a column so that if the cell value is "less than or equal to" and then put "0" in the box, that certain formatting will be applied to those cells. But what I've discovered is that in testing the conditional formatting, some obey this formatting with a zero and some don't. I seem to remember something about zeros being able to be positive or negative and that we had to do somethi...

Excel does not return to previous cell
Hi all, I am running into an problem with my users. We have a Excel 2000 spreadsheet. I know that typically, the sheet/cell that is selected when a user closes the spreadsheet will be the same when a user opens the spreadsheet the next time. However, the problem I am seeing is that the proves true with some users, but not with all users. Some users open the spreadsheet and instead of going back to, say cell A972, it opens to, say cell B4. I would welcome any input on this issue. Thanks, Jeff Averhoff <<<"I know that typically, the sheet/cell that is selected when a user...

C++ Interfaces (was "Re: Get Allocated Block Size") [0]
"Chris Cranford" <chris.cranford@tkdsoftware.com> wrote in message news:wcNNTP.01c34336.24d80e20.1000.1057436717@tkdsoftware.com... > Well, I opted to use a different approach by having two variables in the > dialog: > > DWORD m_dwObjectType; > LPVOID m_lpObjectData; > > This way the data is set the LPVOID and the object type is set using a set > of constant variables that identify object types. Then, the dialog can > recast the LPVOID data to the approach object type structure based on the > object type DWORD. Proved to make more sense.......

Formula problem
I have "inherited" a spreadsheet from someone else and am having problem creating new formulas (there currently are no formulas in th spreadsheet). I don't know if there is an option setting causing th problem or if it is something else. When I enter a formula, such as =IF(A1=1,"TRUE","FALSE"), it shows th formula in the cell instead of the result. I have tried a couple o things to figure out what's going on without much success. If I enter the exact same formula in a new workbook, it behaves as expect it to. If I enter the exact same formula in a ...

Password / Auto_Open Macro Problem :-(
Hi, I recently made this macro in excel: http://groups-beta.google.com/group/comp.lang.basic.visual.misc/browse_thread/thread/4224d7377faa09d3/e008d26c6bdad365 I then made an Auto_Open macro consisting of the line "call Highlighted_Text()" . My workbook has always had a password. Since opening the workbook with the Auto_Open macro, I am asked for my password as usual. But even though I am entering the correct password, I am told that it is wrong and to check if I have my SHIFT key turned off. I CAN'T ACCESS THE WORKBOOK!!!! I have tried pressing the SHIFT key different ways...

Fuel Consumption Problem
I need to build a worksheet that calculates MPG for a car whenever the tank is filled. The tank is not filled each time, but some times only a few gallons are put in. I need a formula that will do the maths only when the tank is filled. It would need to take into account the partial fillings in order to get the correct MPG. Any help would be greatly appreciated. Cheers, Rusty Surely it is just sum of miles travelled divided by sum of gallons input? -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Rusty" <kenrussell@optus YOU...

Visual Studio Conversion Wizard to asp.net 4.0
When I first opened my website with Visual Studio 2010, the conversion wizard came up and I said no. Now two weeks later how can I get the wizard back? -- Arne Garvander (Happily unemployed professional Geek) ...

Cell Shading Color
I am running Excel 2003 in Win XP Pro SP2 I would like to set up cells so the fill color: = pale blue when the entered value is => 110 = no fill if =< 109 Is this possible? if so, how do I do it? TIA Bill Conditional formatting. Format>Conditional Formatting and set the tests accordingly. -- HTH RP (remove nothere from the email address if mailing direct) "Bill" <smile@here.net> wrote in message news:uxSFnEmHFHA.156@TK2MSFTNGP10.phx.gbl... > I am running Excel 2003 in Win XP Pro SP2 > > I would like to set up cells so the fill color: > >...

Database problems
I am a computr Tech, I installed RMS for a friend and explained that they need to back it up every night, which i discovered has not been done. Now when I try to use store Ops, I get "login failed" If I go to Ops Admin, I can connect to the DB, but when I test the connection, it tells me "login failed" at one point i even get "the database has been marked suspect by recovery" I cannot ever use Operrations manager Help me PLEASE -- Terry Terry, Are you sure you are using the correct SQL SA password? How did you copy/install/place the database onto the com...

Page Preview/Print Problem
When I do a page preview, each cell in the worksheet appears as a separate page, and when I try to change the page layout, I can not get past a "margins do not fit page size" error. I have followed all the help instructions on formatting a worksheet to a single page, but nothing works. Does anyone have any suggestions? What version of Excel and window are you using? "laura@basi-usa.com" wrote: > When I do a page preview, each cell in the worksheet appears as a separate > page, and when I try to change the page layout, I can not get past a "...

Outlook connector, connection problems still annoying...
Hello, (my hope is that someone from MS read this, reported more than one time via feedback) I have one problem with the Outlook connector which is more than annoying. I have added 5 Hotmail accounts, and I have added Outlook 2007 to my Autostart folder. Unfortunately I have sometimes problems with my rouuter/ISP at windows startup, with the result that I get 5 message boxes which ask me for my Hotmail password. That's annoying. Peter It sounds like you need to delay outlook's start. There are utilities that can control the start order and add delays or you can use a batch file ...

19 Digits in a Cell
It has been necessary to put 19 digits in a cell but the last four revert to Zeros (0000) I have attempted various formats of the cell but of no availe, Why? and Help please. :confused: Bri -- bri ------------------------------------------------------------------------ bri's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27704 View this thread: http://www.excelforum.com/showthread.php?threadid=489539 Preformat the cell as text (format|cells|number tab|Text) Or start your entry with an apostrophe: '1234123412341234123 bri wrote: > > It has been...

sync problems #3
I am having two problems synch 1. With MSN Money, will not sync data saying information is needed, but I can log on to site with no poblem and when I go to send a bill have no problem. 2. Sync with MSN Web keeps saying updates are temporarily unavaialbe (been a couple of months since I was able to do so). I own Microsoft Money 2006 (upgraded serveral times from earlier versions. Any suggestions? I have the same problem as do others on this site. I bet you installed the beta of IE 7 as I did. even though I uninstalled it the syncing does not work. John "BillB" <BillB...

MIDL Problem by creating TypeLib from IDL
Hi, I receive the following error message when I call the MIDL compiler to create a Typlib from an IDL File (translated from german): "The application can not be startet, because mspdb60.dll could not be found." The mspdb60.dll exists on the computer. The error number 1003 is shown. How can I solve this problem? Thanks Christian > "The application can not be startet, because mspdb60.dll could not be > found." > The mspdb60.dll exists on the computer. The error number 1003 is shown. Is mspdb60.dll in a location described in you PATH environment variable? It s...

SQL 2008, problem setting Identity Specification
Hi, I'm trying to set an IdentityColumn on a table. But I get the following error when I try that "Saving changes is not permitted. The changes you have made require the following tables to be created and re-created. You have either made changes to a table that can't be re-created or enabled the option Prevent saving changes that require table to be re-created." I have never seen this kind of error in pervious version of SQL. I am the db_owner on this database. What am I doing wrong? Thanks. Such operation requires re-creation of the table and the ...

click on cell takes you to specific sheet
Sheet 1 is a summary. Each value comes from an underlying sheet. Is there a way of clicking on the cell and it will jump me to the specific sheet. With 40 or so sheets it is hard to remember which sheet generates the value on sheet 1. Thank you You can make a hyperlink which lets you jump to a specified cell in a specified worksheet with Insert / Hyperlink.. /Place in This Document . GL, Henk "Ron Tarr" wrote: > Sheet 1 is a summary. Each value comes from an underlying sheet. > > Is there a way of clicking on the cell and it will jump me to the specific > sheet....

XP/Pub97 print problem
Looking for help last night I saw a question concerning using Publisher 97 on XP. Since the answer was yes, I have a different question. I used to print statements with publisher, and had a clip-art at the top. Now, since I upgraded to XP, the clip-art does not print. Late last night I found that if I save the file then reload it, I can print one copy. The second print operation drops the clip-art. As long as I reload the file first, I can print another copy. I feel this is a 'settings' problem, but can't find anything that seems to fit. I really don't know where to loo...

Protect cells from moving
I have a protected worksheet with unprotected cells for users to input data. There are formulae which read the cells to build up a set of codes. Users have been moving thier input cells (Cut & Paste) which of course corrupts the formulae. Protection allows me to prevent inserting and deletion of rows but not cells. How can I prevent users from moving cells. Its fine if they want to copy. So Copy & Paste is fine but not Cut & Paste. Thanks Neil ...

MAPI Problem with Exchange 2000 and ARCServe Agent
My ARCServe BrightStore backup software will no longer perform a bricks-level backup (information store backup works fine). The bricks-level backup uses MAPI functions where the information store backup does not. The ARCServe tech support finally determined the problem is with my Exchange 2000 server and not their software. The only change I made to my network recently is I upgraded my Windows 2000 domain to a Windows 2003 domain using domainprep and forestprep because I wanted to upgrade only my domain controllers to Windows 2003. My Exchange 2000 server is sitting on a Windows 2000 Se...

Large BLOB problem
Hi,I am using SQL Server 2000 as backend, where I store files, so i came to problem when I fill IMAGE (OLE Object) field with files larger tha 50 mb.Table is linked to access 97 application, i use DAO GetChunk and AppendChunk methods.Error (ODBC call failed) emerges when I call Update method of recordset object.I've run some tests with same table structure using access97 db as backend and everything works fine, files of 150 mb are 'uploaded' without problem.When I use SQL Server 2000 as backend , i can't insert more than 50mb into field.What is the problem?Cache between access...

Pasting into visible cells only
I have two worksheets that are set up to custom view with a sort function; i.e. it hides lines. I know how to copy visible lines to a fully open worksheet. If the worksheet I want to copy to is also sorted to have the same number of visible lines is there a way to copy just into those visible lines? I get an error box saying " cannot paste in to multiple selections". Excel will only paste into a contiguous range, so you can't copy visible cells only, and paste into visible cells only. TV Man wrote: > I have two worksheets that are set up to custom view with > a so...

Workflow Rule Failing in 3.0
Hey, So I'm not sure why this "Create" WF rule keeps failing, but I have a picklist in a task that is set by the WF rule, based on entity conditions. If the conditions are met the Picklist is set to "Yes" (the picklist has 3 values, "Yes", "No", and "N/A"). I have some OnLoad code that checks this value, and if the Value is "Yes", a function that captures the Click "Save As Complete" event throws an Alert and prevents the action. Thing is, the Workflow rule doesn't set the field and if I go to the Workflow M...