Replacing #DIV/0! by 0

Hello,

I'm trying to replace error symbols with a 0 in a formula without success !

Please could someone tell me where I'm going wrong in the formula below :

IF(ISNA(SUM(M3:O3)/SUMPRODUCT((M3:O3>0)*1))),0,((SUM(M3:O3)/SUMPRODUCT((M3:O
3>0)*1))))

Thanks very much for your help,

James


0
5/11/2004 12:31:41 PM
excel 39879 articles. 2 followers. Follow

2 Replies
511 Views

Similar Articles

[PageSpeed] 20

One way:

    =IF(COUNTIF(M3:O3,">0")=0,0,SUM(M3:O3)/COUNTIF(M3:O3,">0"))

In article <ekABsP1NEHA.128@TK2MSFTNGP12.phx.gbl>,
 "James" <james.parker@genesys.com> wrote:

> I'm trying to replace error symbols with a 0 in a formula without success !
> 
> Please could someone tell me where I'm going wrong in the formula below :
> 
> IF(ISNA(SUM(M3:O3)/SUMPRODUCT((M3:O3>0)*1))),0,((SUM(M3:O3)/SUMPRODUCT((M3:O
> 3>0)*1))))
0
jemcgimpsey (6723)
5/11/2004 1:08:06 PM
works great.  Thanks very much.

James

"JE McGimpsey" <jemcgimpsey@mvps.org> wrote in message
news:jemcgimpsey-87D878.07080611052004@msnews.microsoft.com...
> One way:
>
>     =IF(COUNTIF(M3:O3,">0")=0,0,SUM(M3:O3)/COUNTIF(M3:O3,">0"))
>
> In article <ekABsP1NEHA.128@TK2MSFTNGP12.phx.gbl>,
>  "James" <james.parker@genesys.com> wrote:
>
> > I'm trying to replace error symbols with a 0 in a formula without
success !
> >
> > Please could someone tell me where I'm going wrong in the formula below
:
> >
> >
IF(ISNA(SUM(M3:O3)/SUMPRODUCT((M3:O3>0)*1))),0,((SUM(M3:O3)/SUMPRODUCT((M3:O
> > 3>0)*1))))


0
5/12/2004 8:05:13 AM
Reply:

Similar Artilces:

Replace word in sentence function...
I have the following function which replaces a word in a sentence with "....." =REPLACE(I2,SEARCH(E2,I2),LEN(E2),".....") Where the word is E2 and the sentence is I2. At the moment, if the word is "work" and the sentence is "I worked at home" I get the sentence "I .....ed at home". Any ideas how I could capture any remaining letters at the end of the word and get the result "I ..... at home"? I'd greatly appreciate your help. Thanks in advance, Chris can you upload example, would be easier to view -- Message posted from h...

find and replace hyperlink?
Hi, I want to globally find and replace the contents of a hyperlink with a new hyperlink. For example, I want this Jul.xls#'Title Page'!A1 to be replaced with Aug.xls#'Title Page'!A1 I can't seem to find a way do it. Is there a way? Thank you, Mike Take a look at David McRitchie's site: http://www.mvps.org/dmcritchie/excel/buildtoc.htm look for: Fix Hyperlinks (#FixHyperlinks) If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm me wrote: > > Hi, I want to g...

Service section of CRM 3.0
Hello. My question involves working with the Service section of CRM. I am fairly new to the whole CRM system but up to now, going through Sales and Marketing I haven't been to mind-boggled. I am trying to create a new case and link it to a contract through the options that are available by default in the New Case form view. For some reason, when I go to look-up the contract for a particular case I don't return any results. Why??? Basically I'm asking is there any information out there that walks you through the Service section of CRM, else can someone give me a quick ...

Want to learn about CRM 4.0?
If you want to learn and and get the latest information for CRM 4.0, please visit the Crowe CRM 4.0 blog. We have a great CRM team and we are going to post information related to CRM 4.0 weekly. We already posted information related to Multiple User Lookups on a Form, Duplicate Detection, Data Import Tool, etc... To visit the Crowe CRM blog, please go to the following link: http://www.crowecrm.com/crowe/microsoft/crmblog.cfm Darren Liu, CRM MVP Crowe http://www.crowecrm.com http://www.crowecrm.com/crowe/microsoft/crmblog.cfm Hi Darren, I have downloaded the "what is new in CRM 4.0&...

Replacing Powerpoint 97 SR-2 on M/S Office 97 Pro Edit on Win XP
I dread asking this in case someone descends on me like a ton of bricks and says the answer is in the posts already! - but i I have never used the PP 97 which came with the MS Office 97 Pro Edit I have installed on my computer which uses Win XP with Ser Pack 3. I am about to embark on using PowerPoint for the FIRST time and wonder whether I should, before anything else, acquire a more up to date version first - and one that will work problem free with my XP - which version should I go for and will it, on loading, overwrite and replace the Powpoint I already have and still operat...

Search and replace problem
Hi, I have an Excel worksheets where in several cell's a symbol was added to mark the cell's "to review". The symbol used was "(*)". I have tried now to delete all the entries with a "search and replace", but Excel interpretes the asterisk as a DOS-symbol and deletes everythng between brackets. Is there a way to avoid that or to let Excel know that I only want to delete the specific characters. TIA Melissa It's because it is a wildcard, you can replace it by preceding it with a tilde ~ which tell Excel to find an asterisk in find what p...

Intigrating HR with Payroll in GP 8.0 with SP3 (US)
We currenlty handle all benefits from Payroll. I would like to start handling benefits and deductions from HR. I am working on 401K and am set up to view payroll from HR. I have 3 employees with a 401K enrollment in HR - as a test - intigration was not chosen for any of them. Already existing in payroll, we have a deduction with the same exact name as the benefit, already showing the before tax amounts, etc. If I choose to intigrate the "401K" code from the HR module with payroll, upon enrolling an individual, will that overwrite all of my current payroll deduction data? ...

replace a negative result with a zero?
I have my formula entered but need it to return a zero if the result is negative. Any help is greatly appreciated. -- Pam Well, what is the formula that you are using? You could try this: =3DMAX(your_formula,0) to replace your_formula. Hope this helps. Pete On Apr 19, 5:17=A0pm, Pam2277 <Pam2...@discussions.microsoft.com> wrote: > I have my formula entered but need it to return a zero if the result is > negative. =A0Any help is greatly appreciated. > -- > Pam Hi, =if(your formula<0,0,your formula) "Pam2277" wrote: > ...

Selective Replace in Search and Replace
I need to search and replace in a large document. I don't want to replace the entire search term/expression just part of it. In this case I am searching for a paragraph return followed by any letter (not digit) and then want to change that to paragraph/tab but keep the letter. The search looks like this ^p^$ but naturally if replace is ^p^t I lose the first letter of the text string which I don't want How can I construct either search and replace so that ^p Alexander turns into ^p ^tAlexander and not ^p ^tlexander Thanks in a...

Dynamics.EXE process hangs in 8.0
Occassionally one of our IM jobs fails and we end up with multiple copies of Dynamics.exe running in Windows Task manager on the server (IM runs on the server box). We use standard macros to log in. In 6.0 when this happened we could delete the IM log in if necessary from the Activity window and go to the Windows Task Manager and kill the Dynamics.exe process. This doesn't work in 8.0 with Windows 2003 server. If Dynamics.exe hangs, it requires a reboot. Is there a workaround or some programmatic way of killing this process? ...

Replace wildcards with a space
I have a text field with all values containing an asterisk. The asterisk can be located anywhere within the contents. I am trying to replace the asterisk with a space. I will then use TRIM to remove any spaces at the beginning of the text. I have been unable to get an update query to work. When my criteria is like "~[*}", Access says that 0 rows will be updated. When the criteria is [*], I am asked to enter a parameter value. Any assistance will be greatly appreciated. Thanks - bkh Barbara wrote: >I have a text field with all values containing an aste...

Replace more characters using replace
Hi I have number of characters in a string, that I like to replace with "nothing". I use a code like this: Sub test() Dim a As String a = "Jan+& Per" a = Replace(a, " ", "") a = Replace(a, "&", "") a = Replace(a, "+", "") MsgBox a End Sub As I have about 15 characters, that all should be replaced with nothing, I am looking ofr another way to it. Something like Sub test() Dim a As String a = "Jan+& Per" a = Replace(a, {" ",...

Replacing old Windows XP and Outlook Express with new Windows 7
How do I export the contents and settings out of Outlook Express and import the accounts, settings and emails into Windows Live Mail on another machine? For messages: Copy the *ENTIRE* OE message store folder to flash or CD. (Folders.dbx must be included). Place this on the Desktop or other location on the machine using WLMail. Open WLMail and: File | Import | Messages | Microsoft Outlook Express 6 and point to where you saved it. For Addresses: Open the Address Book in OE and File | Export | Address Book (wab) and save it to the Desktop. Copy to flash or CD. Place this on the...

HELP PLEASE! Meaning of database in POS 2.0?
I just installed the MS POS 2.0 on a brand new HP (w XP Pro) with a sample database. Now I am ready to delete everything and start my own store's DB. However, even though I deleted all 3 copies of the sample databases I previously created (via Tools>Dayabase>Delete) I still see the sample database items and configuration specifics. How can I create a clean DB for my store? or should I change the configuration specifics of the sample database and use that as my primary DB? What is the meaning of Db in MS POS? Is there a non-listed DB somewhere that does not get to be listed ...

2.0 release
I'm currently using 1.2 in my test environment, but there are so many security issues....I was wondering if these security issues will be resolved in 2.0 and if there is a release date for 2.0???? Thanks, Jen Hello Jen Version 2.0 will not appear until next year. I am expecting security to have a lot more features but of course until the product is announced officially its anyones guess which features will make it into the final build. Tell us more about your issues around security so we can offer some assistance. "jen" <jselmeyer@racoindustries.com> wrote in messag...

arabic test in CRM 3.0
i re-build the Account form and change the descrptions to arabic languge its work with me but i need to test it and how i can change the form to Left side? for more information : mr_3s_com@hotmail.com ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.mic...

CRM3.0 Integration Connector with GP
Hello All, Does anyone know if there is an automatic way of producing XSD schemas for CRM 3.0 that is acceptable for the Integration server for CRM 3.0? The documentation says that it has to be in the 1.2 format for the integration to work properly, and has to use the namespace: "http://www.microsoft.com/mbs/crm/schemas/2004". The same documentation directs you to a URL to get the standard XSD schemas for CRM 1.2 but they actually have another namespace in them. It goes on to say that when adding new attributes to the CRM entities, that you add the new attributes to the ...

Do you want to replace the contents of
When my macro runs, it stops after this procdure and asks "Do you want to replace the contents ofthe destination cells" is there something I can add that will automatically tell it yes? Here is the part of the macro that it stops on. Selection.TextToColumns Destination:=Range("C1"), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _ Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _ :="/", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1)) Thanks H...

How do I replace just a few characters?
Hello, I have an excel worksheet with a street name column. I need to rename some names from "Main Street" to "Main St". I can do it for one name easily with find and replace, but how do I make it work for an entire column of different names such as "Easy Street" and "Main Street". If I do find "* Street" and replace with "* ST" I get "* ST" for the output instead of "Easy ST" What am I missing? -- Tomsriv ------------------------------------------------------------------------ Tomsriv's Profile: http:/...

GP 9.0 Table descriptions
Is there any way to find the descriptions for each table in GP? Thanks, Tony Absolutely! Go to Tools >> Resource Descriptions >> Tables. Every table is listed with all the fields. You should also consult the SDK that is on the second GP cd. Additionally, Richard Whaley has published a series of books at Accolades Publishing that does a thorough job of documenting the tables, much better than what MS offers. -- Charles Allen, MVP "Tony" wrote: > Is there any way to find the descriptions for each table in GP? > > Thanks, > Tony > > &g...

How do you publish customizations in v3.0
Just a quick question; do you need to publish customizations in v3.0 like in v1.2?? Only I can't find the publish option in deployment manager anymore. You do how ever have to save twice, once on closing the customized form and then on leaving the customization area. Would that publish the customization?? Thanks in advance Ben Found it under "More Actions" in the customization window. Hi Ben! Ben wrote: > Just a quick question; do you need to publish customizations in v3.0 > like in v1.2?? Until the public v3 beta, please refer questions to the private beta newsg...

VBA to find filename and replace
Hi I just found the answer to "Find and Replace in VBA" and tried to adapt it for what I'm doing, but it's more difficult so I need some help please. I have multiple worksheets in an excel 2007 workbook - a monthly report. Within the worksheets I have many cells that link to last months workbooks - (i.e I compare last month figures to this months figures). I need the macro to do the following: - Create an Input box for the user to "enter the name ofTHE OLD months workbook" - Create an Input box for the user to "enter the name of THE NEW mont...

GP 8.0 and Personal Data Keeper
We just updated our system to service pack 5. Is it possible to apply Personal data keeper sp3 next week or do we have to also update PDK at the same time Thanks ...

Replacing Character
I need to replace/delete the " >" character at the end of a number... when the data was brought into the excel 1234> was the number that cam it happens in about 7000 lines any suggestions on how to replace it with a blank?? thank -- tico31p ----------------------------------------------------------------------- tico31pl's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1040 View this thread: http://www.excelforum.com/showthread.php?threadid=46572 Select the range to fix. Edit|Replace what: " >" (without the quotes with: (leav...

List View Grouping in MFC 9.0
Dear Friends, I want to split List control into groups at run time similiar to Windows VISTA MY Computer view. i.e. 1) I can expand and collapse the items at run time ? 2) Header of groups should be in user defined color etc. Any help is highly appreciated. I am developing the MFC application in Visual Studio 2008 + feature pack 1. You may want to look at some of these controls. The Outlook bar control might work for you, or perhaps a tree control and list control in a splitter window: http://www.codejock.com/downloads/samples/controls.asp#controls_vistataskdialog Tom <Smadhuka...