hiding formula error #DIV/0!

Hithere,

In a sheet, for a cell I use the formula:
=(F4/1,19-D4)/(F4/1,19)

If there is no input yet in cells F4 or D4 an error message "#DIV/0!" is 
shown.
I don't want this message visable when there is no input yet in F4 and D4.

The solution I found Googling was to use 
=IF(ISERROR(OriginalFormula),"",OriginalFormula)

Translating that to my formula, I came up with:
=IF(ISERROR((F4/1,19-D4)/(F4/1,19)),"",(F4/1,19-D4)/(F4/1,19))

Inserting this formula however makes Excel complaining that there is an 
error in this formula, specificly pointing to the ,"", part in the middle.
I have no clue what I'm doing wrong.

Any help for this Excel-newbie?

t.i.a. Noot





0
not7414 (3)
7/4/2006 8:42:21 PM
excel 39879 articles. 2 followers. Follow

5 Replies
427 Views

Similar Articles

[PageSpeed] 22

Are you sure that the commas between the 1 and the 19 should be there?

Is your number format set to recognise commas as a decimal point?


-- 
mrice

Research Scientist with many years of spreadsheet development experience
------------------------------------------------------------------------
mrice's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=10931
View this thread: http://www.excelforum.com/showthread.php?threadid=558254

0
7/4/2006 8:52:06 PM
Yep, we're Dutch ;-)

"mrice" <mrice.2afv5n_1152046501.9522@excelforum-nospam.com> wrote in 
message news:mrice.2afv5n_1152046501.9522@excelforum-nospam.com...
>
> Are you sure that the commas between the 1 and the 19 should be there?
>
> Is your number format set to recognise commas as a decimal point?
>
>
> -- 
> mrice
>
> Research Scientist with many years of spreadsheet development experience
> ------------------------------------------------------------------------
> mrice's Profile: 
> http://www.excelforum.com/member.php?action=getinfo&userid=10931
> View this thread: http://www.excelforum.com/showthread.php?threadid=558254
> 


0
not7414 (3)
7/4/2006 9:08:21 PM
Assuming 1,19 is 1 19/100 (i.e the comma is used for the decimal symbol)
Why not simply the formula to =1-1,19*D4/F4
This can give an error only when F4 is zero (an empty cell is treated as 
zero)
So =IF(F4<>0, 1-1,19*D4/F4,"")
The last part is a pair (2) double quotes (")
Now, we North Americans are only vaguely aware of other people's 
conventions. Do you normally use ; to separate arguments in a formula?
IF so use =IF(F4<>0; 1-1,19*D4/F4;"")
best wishes - let us know if all goes well
-- 
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"noot" <not@ho.me> wrote in message 
news:44aad2c2$0$8901$e4fe514c@dreader31.news.xs4all.nl...
> Hithere,
>
> In a sheet, for a cell I use the formula:
> =(F4/1,19-D4)/(F4/1,19)
>
> If there is no input yet in cells F4 or D4 an error message "#DIV/0!" is 
> shown.
> I don't want this message visable when there is no input yet in F4 and D4.
>
> The solution I found Googling was to use 
> =IF(ISERROR(OriginalFormula),"",OriginalFormula)
>
> Translating that to my formula, I came up with:
> =IF(ISERROR((F4/1,19-D4)/(F4/1,19)),"",(F4/1,19-D4)/(F4/1,19))
>
> Inserting this formula however makes Excel complaining that there is an 
> error in this formula, specificly pointing to the ,"", part in the middle.
> I have no clue what I'm doing wrong.
>
> Any help for this Excel-newbie?
>
> t.i.a. Noot
>
>
>
>
> 


0
bliengme5824 (3040)
7/4/2006 9:15:24 PM
Wow, I'm very impressed, you were right twice :-)

Working like a charm now.

Thanx!


"Bernard Liengme" <bliengme@stfx.TRUENORTH.ca> wrote in message 
news:%238ED576nGHA.776@TK2MSFTNGP04.phx.gbl...
> Assuming 1,19 is 1 19/100 (i.e the comma is used for the decimal symbol)
> Why not simply the formula to =1-1,19*D4/F4
> This can give an error only when F4 is zero (an empty cell is treated as 
> zero)
> So =IF(F4<>0, 1-1,19*D4/F4,"")
> The last part is a pair (2) double quotes (")
> Now, we North Americans are only vaguely aware of other people's 
> conventions. Do you normally use ; to separate arguments in a formula?
> IF so use =IF(F4<>0; 1-1,19*D4/F4;"")
> best wishes - let us know if all goes well
> -- 
> Bernard V Liengme
> www.stfx.ca/people/bliengme
> remove caps from email
>
> "noot" <not@ho.me> wrote in message 
> news:44aad2c2$0$8901$e4fe514c@dreader31.news.xs4all.nl...
>> Hithere,
>>
>> In a sheet, for a cell I use the formula:
>> =(F4/1,19-D4)/(F4/1,19)
>>
>> If there is no input yet in cells F4 or D4 an error message "#DIV/0!" is 
>> shown.
>> I don't want this message visable when there is no input yet in F4 and 
>> D4.
>>
>> The solution I found Googling was to use 
>> =IF(ISERROR(OriginalFormula),"",OriginalFormula)
>>
>> Translating that to my formula, I came up with:
>> =IF(ISERROR((F4/1,19-D4)/(F4/1,19)),"",(F4/1,19-D4)/(F4/1,19))
>>
>> Inserting this formula however makes Excel complaining that there is an 
>> error in this formula, specificly pointing to the ,"", part in the 
>> middle.
>> I have no clue what I'm doing wrong.
>>
>> Any help for this Excel-newbie?
>>
>> t.i.a. Noot
>>
>>
>>
>>
>>
>
> 


0
not7414 (3)
7/4/2006 9:30:47 PM
That formula would not give #DIV/0 whether there is anything in  D4 and/or
F4 or not. #DIV/0 occurs when the divisor is 0, and it is 1.19 in your case.

Something else must be afoot.

-- 
 HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"noot" <not@ho.me> wrote in message
news:44aad2c2$0$8901$e4fe514c@dreader31.news.xs4all.nl...
> Hithere,
>
> In a sheet, for a cell I use the formula:
> =(F4/1,19-D4)/(F4/1,19)
>
> If there is no input yet in cells F4 or D4 an error message "#DIV/0!" is
> shown.
> I don't want this message visable when there is no input yet in F4 and D4.
>
> The solution I found Googling was to use
> =IF(ISERROR(OriginalFormula),"",OriginalFormula)
>
> Translating that to my formula, I came up with:
> =IF(ISERROR((F4/1,19-D4)/(F4/1,19)),"",(F4/1,19-D4)/(F4/1,19))
>
> Inserting this formula however makes Excel complaining that there is an
> error in this formula, specificly pointing to the ,"", part in the middle.
> I have no clue what I'm doing wrong.
>
> Any help for this Excel-newbie?
>
> t.i.a. Noot
>
>
>
>
>


0
bob.NGs1 (1661)
7/4/2006 10:15:56 PM
Reply:

Similar Artilces:

Too Many IF Statements Nesting Error (Excel Formula Loop w/o VBA)
Hello Excel Problem Gurus, First of all, let me thank you in advance. I find it exemplary that you all can devote time to helping others who are having issues with their work. Hopefully one day I can be at a mentor level, and help others too. Hope you can help! I have an issue where I don't know how to write the formula that I need without going over on the nesting. The current formula that I have is as follows: =IF(OR(B7="",J7="",L7="",M7="",N7="",O7="",P7=""),"No Data",IF(V7="Yes",&qu...

Lost Active Directory for Microsoft CRM 3.0
Hi, We recently had a server failure on our Domain Controller which was unrecoverable and our backups for active directory failed to restore. The CRM database and application are on a seperate server so is fully intact. But we have lost the AD, which means no access. My question is what is the best method if any to recover the CRM system? I was hoping in version 3.0 this is possible as i know 1.2 was a nightmare due to the security descriptors. Am i able to reinstall and attach to existing database and apply CRM customization? Thanks, Pete Pete, You're in luck, this shouldn'...

Office 2003 on WinXP is receiving an error...
This operating is not presently configured to run this application.' This nmessage keeps popping up after I install WInXP Sp2. Right after I install office 2003, and I try to open MS word 2003, the above message appears. Can anyone point me in the right direction? RodneyJ Insert office cd>repair "RodneyJ" <u58446@uwe> wrote in message news:a43130535cd3e@uwe... > This operating is not presently configured to run this application.' This > nmessage keeps popping up after I install WInXP Sp2. Right after I > install > office 2003, and I t...

converting text to a formula
is it possible to convert a text string to a formula? I have built a series of cells using CONCATENATE to produce a text string for a vlookup formula - is there a way to now use this text as a working VLOOKUP formula? Thanks. West. -- West ------------------------------------------------------------------------ West's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=16123 View this thread: http://www.excelforum.com/showthread.php?threadid=277050 West Wrote: > is it possible to convert a text string to a formula? > > I have built a series of cells u...

Showing 0 after decimal with ROUND
Hi, I'm using the ROUND function like this: =ROUND(C29*(92%),1) &- ROUND(C29*(108%),1) Unfortunately, if the numeral after the decimal is a 0, then it doesn't display. How can I force a 0 to display after the decimal? Libby Format the cell as Number with 1 decimal place - it would appear to be formatted as General. Hope this helps. Pete On Dec 4, 1:44=A0pm, Libby <Li...@discussions.microsoft.com> wrote: > Hi, > > I'm using the ROUND function like this: > > =3DROUND(C29*(92%),1) &- ROUND(C29*(108%),1) > > Unfortuna...

lil help with "or" formula
I am trying to return a "false" value if there is text in cells E2:I2.. the only formula i have come up with so far is =OR(ISTEXT(E2),ISTEXT(F2),ISTEXT(G2),ISTEXT(H2),ISTEXT(I2)) but this returns the opposite value of what I want, it tells me "true" if there is text... any help???? Hi try: =IF(SUMPRODUCT(--(ISTEXT(E2:I2)),false,true) -- Regards Frank Kabel Frankfurt, Germany "Murph" <Murph@discussions.microsoft.com> schrieb im Newsbeitrag news:4A8133F9-3E83-4369-A69E-763CD6E9D3F8@microsoft.com... > I am trying to return a "false" value i...

Errors saving to network drive
When my users open an Excel spreadsheet located on a network drive, make their changes, and then try to save the spreadsheet, they get an error message saying "Your changes could not be saved to Whateverfile.xls, but were saved to XXXXXXXX ( 8 digit hex number ). Close the existing document, then open the temporary document and save it under a new name" The users have been explicitly granted full rights, including Delete and Modify, to the shared folder in which the spreadsheet resides. I have disabled their Symantec Antivirus realtime protection for the time being. This pr...

How do I set up a daily average of unit sales formula
More info required. -- HTH RP (remove nothere from the email address if mailing direct) "jim m" <jim m@discussions.microsoft.com> wrote in message news:7E6D4510-97C1-42D4-A402-5590201C6065@microsoft.com... > ...

Special Pasting a work book with many sheets and formulas
I have a workbook with many sheets that all have formulas and links to other data. I want to save the workbook as another name with all the worksheets keeping the values only (no links or formulas). Is there a quick way to do this for everysheet without having to special paste every sheet in the workbook. So can I save everysheets data values at workbook level. See this page for a code example http://www.rondebruin.nl/values.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "lex63" <lex63@discussions.microsoft.com> wrote in message news:ED708...

Need formula to calculate bi-monthly pay dates
I need to create a list or table that returns all pay dates for the 15th and last day of the month for an entire year. The date must adjust forward when the normal pay date falls on a weekend or holiday, e.g. the 15th is Sunday so pay day is the 12th. My attempts have resulted in excessive formulas and no concrete answer. Do you need to account for holidays? This formula works but I don't account for holidays although it'd be easy enough to do so. I'm thinking some "date wizard" (a person that is good at date formulas, that's not me!) can probably ...

cell contents revert to 0 when i click on the next cell
I put a number into a cell click on the next cell and the first cell reverts to 0. If I format to number with 2 decimal places it will be ok but when I try to take out decimal places it goes back to zero, Help please You haven't said what number you are trying to put into the cell, but I suspect that the number is less than 0.5. A quick test shows that if you set the cell to no decimal places then enter a number less than 0.5 it is displayed 'rounded down' so it will show as zero, if it's 0.5 or above it displays as 1. If you need to put numbers less than 0.5 into youe c...

Error loading XML into DataSet
Hello, When I try to load this xml file (see below) into an asp.net dataset using the following code: ds.ReadXml("xmlfilepath\xmlfile.xml", XmlReadMode.Auto) I get the following error: "The same table (description) cannot be the child table in two nested relations. " If I take out the anchor tag in the description it works fine, however, I need the anchor tag in there. Does anyone have any suggestions? <?xml version="1.0" ?> <rss version="2.0"> <channel> <title>Liftoff News</title> <link>http://liftoff.msf...

Accommodating for empty cells in this formula?
I have a formula in cell H21, for example, reads like this: =IF($G21<>"",($H20-$G21),"") is there a way to adjust the formula so that an empty cell in G21 doesn't give the #VALUE! in subsequent cells in column H? Just to give a similar example, this formula =SUMIF(A1:A9,"<>0") adjusts for any and all empty cells in A2 to A9. It no longer matters if any of the cells are empty, the formula correctly gives the correct addition of A1 plust a sum of everything between A2 to A10 without any #VALUE! results. Was hoping to have the formula above als...

Help With Margin Formula
Hello, I need help with a margin forumla (calculated from retail). Say I have a cost of $10.00, and I need the formula to calculate a 40% margin from retail. So the retail should end up at $16.67. Not sure how to get from $10.00 to $16.66, I just know the cost and the margin I need to make. Thanks JR =A1/(100%-40%) -- Kind regards, Niek Otten "JR" <gaspower@aol.com> wrote in message news:eGszf.424$2O6.53@newssvr12.news.prodigy.com... > Hello, > I need help with a margin forumla (calculated from retail). Say I have a > cost of $10.00, and I need the formul...

Run time error 5 : HQClient
When I double click HQ Client I have message box "Run time error 5 : Invalid procedure call or argument" how should I do? I used RMS 2.0 -- TOY2TOY ---------------- 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.microsoft.com/Businesssolutions/Community/NewsGr...

LNK2001 and LNK2019 errors with template class
This is a multi-part message in MIME format. ------=_NextPart_000_00BB_01C695ED.3958DA70 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I have a smart pointer class that uses template functions that works = fine under VC6 but it not linking under VS2005. 1>TestGridToolkitView.obj : error LNK2019: unresolved external symbol = "void __cdecl PrismGCSmartPointer(class CPrismSmartPointer<class = CDialog> &)" = (?PrismGCSmartPointer@@YAXAAV?$CPrismSmartPointer@VCDialog@@@@@Z) = referenced in function "protected: __...

delivery failure error
"The e-mail account does not exist at the organization this message was sent to" then says user unknown, even though other comupters don't have that problem. I get this error for a few clients, yet other clients can email the same address. I have Windows small business server 2000, I am pretty sure everything has updated patches. I know i security updates automatically. My clients are an updated Windows XP SR 1 with Office 2003(outlook) SR1. ...

on Writexml error
(Type your message here) Hi all, I have a XML file as a datasource. I read that file using dataset.readxml.Then in that dataset I make some changes. Now I want to write that changed dataset by overwriting the file that was created initially. I someone please tell me how to do it. -------------------------------- From: ratnesh gupta ----------------------- Posted by a user from .NET 247 (http://www.dotnet247.com/) <Id>sCyOfoucUUyYsxAsNxg9AA==</Id> ...

Excel error 1706
Whenever I try to start excel on my computer it displays an error message and it shuts itself down, any other program from the office pack works properly, except for the one I mentioned. I reinstalled it and it does the exact same thing. Thanks in advance. ...

Error with Bold
What's wrong? With Worksheets("Recent") Range(Cells(1, 1), Cells(1, 2)).Font.FontStyle = "Bold" End with Take a look at the activesheet. I bet that range is bolded. But if you want Recent to be the sheet with the change, add a couple of dots. With Worksheets("Recent") .Range(.Cells(1, 1), .Cells(1, 2)).Font.FontStyle = "Bold" End With Without those dots, the range refers to the activesheet (if the code is in a general module). crapit wrote: > > What's wrong? > With Worksheets("Recent") > Range(Cells(1...

ANN: Optimizing the Performance of Microsoft Dynamics CRM 3.0 white paper
Hi, the White Paper "Optimizing the Performance of Microsoft Dynamics CRM 3=2E0" is now available: http://go.microsoft.com/fwlink/?LinkID=3D80916=20 Regards Nicolas F=FChrs ...

Error loading outlook 2003
Hey Guys need a little help I'm running office 2003 with outlook, I'm using my own exchange server, everytime I load up outlook on one of my workstations it loads up fine until I hit the inbox, once I do that Outlook closes down, I check in the event view and got this error message: "The MOF file created for the outlook service could not be loaded. The error code returned by the MOF Complier is contained in the Record Data. Before the performance counters of this service can be collected by WMI the MOF file will need to be loaded manually." Anyone know a fix inste...

Rule Wizard: "Rules In Error"
I have a blackberry, and I have my outlook forwarding emails to it, but about once a day, I get a message that says "Rules in Error: Error in Rule Execution" and it won't forward any emails after that. I only have one single rule, so it's not a rule conflict. Does anyone know the deal? This is extremely frustrating as sometimes I'll be out of the office during the weekend or at night and won't receive any message for hours on my blackberry, only to find out later that this "Rules in Error" dialog box popped up and it won't forward emails. It see...

Does POS 2.0 support price-embedded barcodes?
Does anyone know if Microsoft Point of Sale 2.0 supports price-embedded barcodes (also known as random weight barcodes)? I have Digi scales that print out 12-digit barcodes with UPC and price info. I would like to implement POS and not RMS because it is much less complex. Thanks for any info. POS 2.0 does not support price-embedded barcodes. "Martin" wrote: > Does anyone know if Microsoft Point of Sale 2.0 supports price-embedded > barcodes (also known as random weight barcodes)? > > I have Digi scales that print out 12-digit barcodes with UPC and price info. &...

Oulook 2003 dial up sync errors
we are using outlook 2003 will all current updates insatlled and are using cashed exchange mode. our operating system is windows 2000 SP4 with all the current updates as well. when the user connects and hits f9 to sync we get the following error in the sync log (i have run detect and repair and removed and recreated the users outlook profile) any suggestions??: 18:57:44 Synchronizer Version 11.0.5604 18:57:44 Could not connect to public folder server. 18:57:44 [8004011D-526-80040115-0] 18:57:44 The Microsoft Exchange Server computer is not available. Either there are networ...