how to sum cells and ignore the #div/0! 's ?

I an working with the following formula: =SUM(C8:E8)/COUNTIF(C8:E8,">0") if I 
initially have no data entered i get the  #div/0! 's is there a fix for this?
0
Utf
3/15/2010 6:46:01 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

3 Replies
1721 Views

Similar Articles

[PageSpeed] 26

=IF(COUNTIF(C8:E8,">0")=0,"",SUM(C8:E8)/COUNTIF(C8:E8,">0"))
--
David Biddulph

"Bryan" <Bryan@discussions.microsoft.com> wrote in message 
news:62E28129-5C01-44B7-84EC-2851E94569B1@microsoft.com...
> I an working with the following formula: =SUM(C8:E8)/COUNTIF(C8:E8,">0") 
> if I
> initially have no data entered i get the  #div/0! 's is there a fix for 
> this? 


0
David
3/15/2010 7:08:57 PM
Hi,

=IF(COUNT(C8:E8)>0,SUM(C8:E8)/COUNTIF(C8:E8,">0"),"")
-- 
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that 
introduces the fewest assumptions while still sufficiently answering the 
question.


"Bryan" wrote:

> I an working with the following formula: =SUM(C8:E8)/COUNTIF(C8:E8,">0") if I 
> initially have no data entered i get the  #div/0! 's is there a fix for this?
0
Utf
3/15/2010 7:25:01 PM
> =IF(COUNT(C8:E8)>0,SUM(C8:E8)/COUNTIF(C8:E8,">0"),"")

your formula fail if
C8: 0, D8: blank, C8: blank, or all 0s or 0,0, blank

Robust formula:
=IF(COUNTIF(C8:E8,">0"),SUM(C8:E8)/COUNTIF(C8:E8,">0"),"")

"Mike H" wrote:

> Hi,
> 
> =IF(COUNT(C8:E8)>0,SUM(C8:E8)/COUNTIF(C8:E8,">0"),"")
> -- 
> Mike
> 
> When competing hypotheses are otherwise equal, adopt the hypothesis that 
> introduces the fewest assumptions while still sufficiently answering the 
> question.
> 
> 
> "Bryan" wrote:
> 
> > I an working with the following formula: =SUM(C8:E8)/COUNTIF(C8:E8,">0") if I 
> > initially have no data entered i get the  #div/0! 's is there a fix for this?
0
Utf
3/15/2010 7:43:03 PM
Reply:

Similar Artilces:

Edit a group of cells with a macro
I have imported data from another program which combines numeric and alpha characters into a single cell. I need to split the numeric data into one column and the alpha characters into an adjacent column. I copied the imported column into the adjacent column, then went to first column and created a macro which deletes the first 7 characters (the numeric values). When I run the macro on other cells it takes the information from the very first cell and keeps repeating it. What am I doing wrong? Hi! Can you post a few examples of the data you want to separate? Al -- Message posted from h...

help needed to find value and add values in corresponding cells
hello. i need to first search a workbook for a number and return the value i the cell 3 over from it. i can do this using vlookup. but in som workbooks there are mutliple values (the one i look for) with mutipl values accross. eg part no. quantity 1 2 1 -- ghyne ----------------------------------------------------------------------- ghynes's Profile: http://www.officehelp.in/member.php?userid=51 View this thread: http://www.officehelp.in/showthread.php?t=75150 Visit - http://www.officehelp.in | http://www.officehelp.in/archive/index.php | http://www.officehelp.in...

Importing data & email from ACT! 6.0
Can someone tell me how this is done? I gather I should be able to go to Import, Import from another file or program, and then ACT! should be there as an option (but it is not). This is where I get stuck. if you are looking at importing data from outlook to act please find the attached link for more details http://itdomino.act.com/act.nsf/0/c2667caf1c0a2a0c88256c0f00512395?OpenDocum ent&Highlight=2,How,to,import,Outlook,in,ACT! -------------------- ====Content-Class: urn:content-classes:message ====From: <anonymous@discussions.microsoft.com> ====Sender: <anonymous@discussi...

Simple cell prog. with IF...ELSE
Hello XL Gurus! I need to warn you - I'm a perfect newbie! :confused: Well, this is what i would like to do in an Excel sheet: I have made some kind of table, and in some of the columns I need t make some calculations *depending * on some inputs in some of the othe columns. For instance, a price calculation: I need to calculate some averages, to sum some cells...(that ain't problem), but then I need the calculation to add 15% in case that th value one of my cell is (for example) VAT. Let me ilustrate naivly: G2 = (if C2='VAT' then (A1*B1)+((A1*B1)*0.15)else(A1*B1)) ...

Error message opening a file due to too many different cell formats
What can be done to fix this problem? There are 63 sheets in this workbook. There has been a lot of copying and pasting. Now the workbook won't open because there are too many different cell formats. Hi for dealing with too many cell formats have a look at the following KB article http://support.microsoft.com/default.aspx?scid=kb;en-us;213904 Though I'm not sure it will help if you can't open your file anymore -- Regards Frank Kabel Frankfurt, Germany lc wrote: > What can be done to fix this problem? There are 63 sheets > in this workbook. There has been a lot of co...

Test
Testing too... "No Name" <noname@nospam.com> wrote in message news:41dbb275$0$3610$61c65585@un-2park-reader-02.sydney.pipenetworks.com.au... > > test again "bodogbodog" <noname@nospam.com> wrote in message news:41dbb36a$0$3605$61c65585@un-2park-reader-02.sydney.pipenetworks.com.au... > Testing too... > > "No Name" <noname@nospam.com> wrote in message > news:41dbb275$0$3610$61c65585@un-2park-reader-02.sydney.pipenetworks.com.au... >> >> > > test "No Name" <noname@nospam.com> wrote...

CRM 3.0 Reports Not Working in Outlook Client
Recently we had to republish all the reports on the SQL server (2005) in order for them to show up in the web client. They now do and can be run. However, none of our laptop clients are able to run a report. When a user clicks on the report link, they get the following error: Server error '/' application The request failed with HTTP status 401: unauthorized. These same users can run the reports through the web client. I'm at a complete loss and appreciate any and all help. Thanks, Jason Little more background: CRM 3.0 and SQL Server 2005 are on different servers OS: Windows ...

Importing Customizations and Workflows from 3.0
Hi, I've exported some customizations and workflows from CRM 3.0 and want to import to CRM 4.0. I'm trying at the CRM 4.0 interface but it's saying "Either the file could not be uploaded, or this is not a valid Customization file." How should I do that? It isn't possible? tks, Alex Unfortunately you can't export from CRM 3.0 and import into CRM 4.0 Your only option is to have your customisations on a CRM 3.0 server, upgrade the server to CRM 4.0, then export and import the customisations and workflows -- David Jennaway - Microsoft Dynamics CRM MVP Web: h...

Counting coloured cells #3
I have a list of percentages that are conditionally formatted and I want to be able to count how many cells are each different colour. I don't think you can do this with count if as it only looks for numbers or text and not formatting but essentially I want to count the number of cells in a range which are red/gold or green. Use the following UDF Function CountColor(rng As Range, colorRng As Range) For Each cl In rng If cl.Interior.ColorIndex = colorRng.Interior.ColorIndex Then ' use this for background color CountColor = CountColor + 1 End If ...

Multiple Hyperlinks in 1 cell?
I have some spreadsheets that have a column with some text (4 numbers) that link to a webpage, where the URL ends with the four numbers (eg.1234). Sometimes I place an additional 4-number reference to another Webpage in the same cell, so it looks like (eg.1234/5678). I have to get rid of the link since I only know how to set it for one Webpage or the other. Is there a way for users to click 1234 and go to that page, or click 5678 (in the same cell) and go to that selected page? Thanks! Not that I'm aware of. "davidemile" <davidemile@hotmail.com> wrote in message news:...

Cursor auto move from one cell to another
How can I have my cursor automatically move from cell C9 to cell A14 after hitting the enter key in cell C9? (note, there may be data entered or else it may be left blank) Thanks all, mikeburg -- mikeburg ------------------------------------------------------------------------ mikeburg's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24581 View this thread: http://www.excelforum.com/showthread.php?threadid=399546 One way is to use a "named range". Check out this old post: http://tinyurl.com/39vzv -- HTH, RD ===================================...

CRM 3.0 Faxing
Apart from OmniRush, does anyone know of any other Faxing software which is compatible with CRM 3.0 ? Thanks Nicos Papaioannou ...

Stored Procedurs for GP-8.0
Hi All, Does anyone have idea why , after fresh installation and creating company using correct way - have not received any error while installation / or Creating Company the stored procedures name start with "SVC_------------' and "zDP_SVC----------" did not copied in company's DB. if so how to copy these Stored Procedures back to the DB - I m using Great Plains 8.0 - SQL-2000 with complete finance , Purchase, inventory , Sales Order Processing and Payroll . Also can i know the numbers of strored procedues and views that creates by default using each modu...

How can i change cell colour depending on month of date in cell?
I have a column with a range of dates corresponding to when an item was tested and would like to automatically change the colour depending o which month it was tested. Jan-Mar = red, Apr-Jun= green, Jul-Sep blue and Oct-Dec= yellow. No date = no colour. Anyone have an suggestions? Do I need to run a macro or is there a simpler way? Thanks And -- andy7 ----------------------------------------------------------------------- andy75's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3017 View this thread: http://www.excelforum.com/showthread.php?threadid=49860 ...

INDEX-MATCH with cell text driven names
Hi, I've read through a few posts that are close to what I'm trying to do but i can't get it to work. I'm constructing a fantasy F1 spreadsheet while i'm bored off work sick. Sheetnames in workbook DriverSummary, AUS, MAL, CHN, BHR,.....for each round/country of the season. Each sheet race sheet has a column for car number in called AUSarray, MALarray etc. AUSREF is cell A1 on the race sheets My current formula in the diver summary works well and is shown below. I have N() to return 0 when a race hasn't happened yet so that my sum() works. =N(OFFSET(AUSREF;MATCH($A4;AUS...

How to get formulas starting from the referenced cells
Hi all, I've cells on a sheet which are referenced by formulas which are on different sheets (about 20) on the same workbook, and I'm looking for a way to get all these formulas. I've tried: 1) The Trace command, but I see only a table icon every time I've this type of reference 2) The Find command, but there are cell references wrote in different manners (ie B2, $B2, B$2, $B$2), and formulas which refer to results of other formulas that refer to the cells, so it's not so simple to trace all the dependencies Any help is appreciated Thanks Dario I'm not sure I full...

if A1=Null and B1=A1 why is result in B1=0 ??
if A1=Null and B1=A1 why is result in B1=0 ?? because of this I get wrong result for average calculations: average for (6,8,0) <> average for (6,8,null) Use =IF(A1="","",A1) -- HTH RP (remove nothere from the email address if mailing direct) "WGeorg" <WGeorg@discussions.microsoft.com> wrote in message news:09FCCF25-D0DC-4B1E-BFA1-CEF9A9327C46@microsoft.com... > if A1=Null and B1=A1 why is result in B1=0 ?? > because of this I get wrong result for average calculations: average for > (6,8,0) <> average for (6,8,null) Use this ...

how to count number of cells in a color ?
I have a list of data in column, they have conditioanal format to different colors, e.g. blue, red, brown. I want to count the total number of blue. How to do it ? The "CountIf" function does seem to help. Any suggestions ? Thanks. Hi Francisco See http://www.cpearson.com/excel/CFColors.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Francisco" <Francisco@discussions.microsoft.com> wrote in message news:927304A5-05ED-425E-A5F5-AE981BA76DC7@microsoft.com... >I have a list of data in column, they have conditioanal format to different > colors,...

Last Value in a Column when Value <> 0
Hi, can someone provide me a formula that populates a cell with the last value in a column that does not = 0? Below is an example 3 6 7 0 0 0 The goal is to populate a cell with the value of 7. I am currently using the below formula that populates the last value of a column: =INDEX('Retirement Total'!B:B,MATCH(9.99999999999999E+307,'Retirement Total'!B:B)) I do not however know how to change this to not populate the last value when it is zero. Your help is appreciated. Assuming you want the last *numeric* value that <>0. Try something like this: =LOOKUP(1E100,1/'...

how to see locked cells
i�d like to know how can i do to see (easily) whether a cell is locked or not, when the workbook IS NOT protected, because using "format / cells" for each one is a little messy thanx a lot!!!!! --- Message posted from http://www.ExcelForum.com/ You can add a Lock Cell button to the toolbar: Choose Tools>Customize Select the Commands tab Choose the Format category In the list of command buttons, scroll almost to the bottom, to find the Lock Cell button Drag the Lock Cell button to the toolbar Close the Customize window. Select a cell, and lock/unlock the cell by clicki...

Questions on copying from one sheet to the other and coping every other cell.
I need to change a formula on sheet two reading... =OFFSET($B$1,0,ROW()-2) which I drag down the column and it copies text like this =B1 =C1 =D1 etc. I need to make it every other cell in the row instead of every cell. For instance now when I drag it, it will go down the column and copy text from =B1 =D1 =F1 +H1 Etc.... Second, first time ever but the columns in first row in sheet 2 is full, so I also need to continue same formula on the next sheet, sheet 3 but have it continue copying to same column on Sheet 2. How do i make this possible? Thanks! TKL -- KatyLady ---------------...

macro to find date format in a cell and delete that entire row
macro to find date format in a cell and delete that entire row i have dates in a column and date keeps changing, i want that if macro find date in a column , it deletes that entire row help me thank u so much everyone for ue hel -- Message posted from http://www.ExcelForum.com try Sub deletedate() For Each c In Selection If IsDate(c) Then c.EntireRow.Delete Next End Sub -- Don Guillett SalesAid Software donaldb@281.com "vikram >" <<vikram.15ix9t@excelforum-nospam.com> wrote in message news:vikram.15ix9t@excelforum-nospam.com... > macro to find date format in a ...

CRM 3.0 Conveting Campaign Tasks into Tasks
I would like to convert Campaign Tasks into Tasks. The reason for this is that only Tasks are sync'ed with Outlook. Can it be done with the workflow manager. I had a look at it but without any result. Tnx ...

Omit points from plot if cell is formula returns a non-numeric res
How can I prevent cells with a non-numeric function result from being plotted as zero values. The function =IF(G3>0,F5+G3,"") Plots values as it should for G>0 but when G<=0 it plots points on the x-axis instead of omiting the points as it does when the cells are blank. On the spread sheet the cells display blank as they should. Hi, Charts plot all non numeric values as zero. There will however interpolate values if #N/A is used. =IF(G3>0,F5+G3,NA()) Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Dave" <Dave@discuss...

Sum at end of column question
I have a delimted text file that I need to import into excel sort it by product number and total sales by product. I have to do this twice a month so I made a marco that imports the file and then using filter it filters by product number and copies the resuts to another sheet in the workbook. The trouble Im having is that I don't know how to add the entire column for total sales automatically. the total sales will always be in column C but the row number will always be different. I have tried highlighting the C column and clicking on the sum icon in the toolbar but it doesn't g...