fouled file/formula ?

My large Excel 2000 spreadsheet has a new problem in a certain cell
(cell "X") : A trivial formula no longer works unless I reenter it.

FWIW, the cell formula at X is :
      =(1+S24)*R156

Results in the referenced cells are as expected.  The formula result is
shown to be 0, but should be 8.xxx.

Calculation is automatic; F9 is no help.

If I double-click the cell and then press ENTER, the correct result
appears (but sometimes there remains a similar problem in a referring
cell...).  If I enter a constant in a referenced cell, and then UNDO
that entry, the correct result sometimes appears in X.

This is nuts.

Any ideas ?

Thanks,
David

0
drowetx (3)
4/30/2005 6:46:44 AM
excel 39879 articles. 2 followers. Follow

5 Replies
543 Views

Similar Articles

[PageSpeed] 8

Hi David

If this cell is the real problem then, yes, this is nuts. But could it be
that the preceding cells S24 or R156, or one of their precedents, get their
value from a custom function that doesn't work too well ?

Best wishes Harald

<drowetx@yahoo.com> skrev i melding
news:1114843604.836777.131840@l41g2000cwc.googlegroups.com...
> My large Excel 2000 spreadsheet has a new problem in a certain cell
> (cell "X") : A trivial formula no longer works unless I reenter it.
>
> FWIW, the cell formula at X is :
>       =(1+S24)*R156
>
> Results in the referenced cells are as expected.  The formula result is
> shown to be 0, but should be 8.xxx.
>
> Calculation is automatic; F9 is no help.
>
> If I double-click the cell and then press ENTER, the correct result
> appears (but sometimes there remains a similar problem in a referring
> cell...).  If I enter a constant in a referenced cell, and then UNDO
> that entry, the correct result sometimes appears in X.
>
> This is nuts.
>
> Any ideas ?
>
> Thanks,
> David
>


0
innocent (844)
4/30/2005 8:36:17 AM
Hi Harald,

Uhhhhh, indeed there is a custom function called several cells and a
sheet up the road...  I don't recall trouble with it, but it was the
first one I wrote in VBA.  Oops.

S24 contains a constant. R156 references Q156, which references P156,
.... N156, which references a cell on another sheet calculated by the
custom function.  Interestingly, the values in all those cells seem
fine; only in cell X is the problem apparent.

FWIW, the user function invokes Application.Volatile and some present
value methods.

Clearly you are on to the problem.  What might I have done, or not done
? 

Thanks,
David

0
drowetx (3)
4/30/2005 10:28:30 AM
You can do almost anything in a VBA function. So you can do almost anything
wrong there too <g>. Just make sure they recalculate properly and don't deal
with things that aren't explicitly passed to them -no "Range("A1")" stuff
inside VBA functions.

HTH. Best wishes Harald


<drowetx@yahoo.com> skrev i melding
news:1114856910.844634.283770@f14g2000cwb.googlegroups.com...
> Hi Harald,
>
> Uhhhhh, indeed there is a custom function called several cells and a
> sheet up the road...  I don't recall trouble with it, but it was the
> first one I wrote in VBA.  Oops.
>
> S24 contains a constant. R156 references Q156, which references P156,
> ... N156, which references a cell on another sheet calculated by the
> custom function.  Interestingly, the values in all those cells seem
> fine; only in cell X is the problem apparent.
>
> FWIW, the user function invokes Application.Volatile and some present
> value methods.
>
> Clearly you are on to the problem.  What might I have done, or not done
> ?
>
> Thanks,
> David
>


0
innocent (844)
4/30/2005 4:47:53 PM
Hi Harald,

I am satisfied that my function *calculation* is correct, but what must
be done in a user function to ensure correct REcalculation (other than
the application.volatile declaration) ?

And, how can it be that my cell X does not show the right result of its
simple formula applied to the numbers appearing in the referenced
cells, garbage though they may be ?

This smells of a race condition.

Thanks again,
David

0
drowetx (3)
4/30/2005 6:56:52 PM
Hi David

Impossible to say without seeing your function. But this is an illustration
of my previous post. DivideA should be OK at all instances, DivideB is a
pure disaster:

Function DivideA(D1 As Double, D2 As Double) As Double
If D2 = 0 Then
    DivideA = 0
Else
    DivideA = D1 / D2
End If
End Function

Function DivideB(D1 As Double) As Double
If Range("A2").Value = 0 Then
    DivideA = 0
Else
    DivideA = D1 / Range("A2").Value
End If
End Function

Note also that there are actions that doesn't start a recalculation. Like
coloring cells, deleting rows,  ...

HTH. Best wishes Harald

<drowetx@yahoo.com> skrev i melding
news:1114887412.448793.200510@f14g2000cwb.googlegroups.com...
> Hi Harald,
>
> I am satisfied that my function *calculation* is correct, but what must
> be done in a user function to ensure correct REcalculation (other than
> the application.volatile declaration) ?
>
> And, how can it be that my cell X does not show the right result of its
> simple formula applied to the numbers appearing in the referenced
> cells, garbage though they may be ?
>
> This smells of a race condition.
>
> Thanks again,
> David
>


0
innocent (844)
5/1/2005 8:00:42 PM
Reply:

Similar Artilces:

How do I get Excel to open a previously saved file?
With Excel not opened, all of sudden when I try to open an existing Excel file, it only opens a blank workbook. I then have to repeat the action of opening the file from the blank workbook. I am not sure what has happened but I have always just been able to just click on the Excel file I want to open and it would automatically open Excel and the intended file. Please advise hi, check this....... Tools>options>general Tab... Make sure that Ignore other applications is unchecked. that's all i can think of Regards FSt1 "Jeff S" wrote: > With Excel not opened, all of ...

SD card
For Win CE 5.0/6.0 I'm supposed to implement a feature as follows:- Store a "configuration file - like *.ini " in SD card and when system starts up, need to read that ini file and do several things like set a flag to turn some LED on/off, assign network adpater with IP settings, provide a URL to jump to etc., which our customer will cuztomize at their discretion based on how they want system to startup by default. How to go about doing this ?. Any tips will be really helpful regards, GS-ICN On Feb 1, 10:40=A0pm, "GS" <madan...@gmail.com> w...

speed of formulas vs functions
Hi everybody, I'm just wondering if a function like =sum(a5:a15) is evaluated at the same speed as a formula like =a5+a6+a7+a8+a9+a10+a11+a12+a13+a14+a15 I understand that if there is a performance difference it will not be noticed with only one function or formula, but what happens when in a workbook you have thousands of formulas or functions? Any help is greatly appreciated. greets, Sybolt (at the office) HI Sybolt, It is not difficult to test your example. I did 4,193,408 calculations of both formulas and manually clocked them (of course I could have done that programmaticall...

Running two files simultaneously....
Can you run two MS Money files at the same time? I have one for personal use and one for business use, but am always having to close out of one and open the other. I'd love to be able to have a window for each open at the same time in order to view information.....MS Money 2001 Simultaneously? No. "william" <turnerinsurance@epride.net> wrote in message news:4c5a01c490f1$37b36bc0$a501280a@phx.gbl... > Can you run two MS Money files at the same time? I have > one for personal use and one for business use, but am > always having to close out of one and open t...

creating a file of particular extension
dear friends, i am using ms access 2003, vb6. i want to create a file of extension ".gms" . how can i do this? also after creating it, i want to run it with default viewer application( default opening application for that particular type extension). how can i do this? thanq There's nothing magic about file extensions: you can use whatever you want. To associate your extension with a specific application, see what Randy Birch has at http://vbnet.mvps.org/code/reg/association.htm -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "r...

Using the Text Formula
I have a range of cells that I want to apply the =LOWER function to but each time that I go to make the text lowercase the text disappears from the cell. I was wondering if someone could tell me what I was possibly doing wrong? capnhud, with the text you want to change in column A, in B1 put =LOWER(A1) and copy down -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "capnhud" <capnhud@discussions.microsoft.com> wrote i...

Displaying values vs formulas
I'm having another brain-dead moment and I hope someone can resuscitate! I'm putting formulas in various cells on a worksheet and some of them are displaying the results and some are just showing in the cell as the formula. How can I get them all to display results. I swear that all the cells are formatted the same but the displays are different. What am I doing wrong? Thanks for any help. Gary Vocks The cells displaying the formula itself and not the results are probably formatted as Text. A quick way to find out if this is so, is to select a cell that displays the formula, the...

run an excel file remotely
Hi all i got an excel application that generates reports from underlying pivot tables. the reports are generated using excel 2003. but most of the clients donot have licenses for 2003 and some of the features of 2003 cannot be used when the file is opened from the client system. for example GETPIVOTDATA is not very flexible as it was in 2003 version. So is it possible to run the excel application from the server without being copied to the client system. This also helps in maintaing single version of the excel file rather than multiple versions on each client system. Thanks in adva...

more formula help
Once again working on the fundraiser spreadsheet... Teacher wants ... of total money raised, 40% is profit...of that profit the first $15 goes to the general account, the difference goes in the student account. The only thing I really need to keep track of is the student account, which I figured would be (cell*.4)-15, but the problem I have is ... If the child raised zero dollars, it's showing a -$15 dollar balance, but I would need it to show zero. Any help is appreciated. Vivian K =MAX(yourexpression,0) -- Gary''s Student "Viviank" wrote: > Once again wo...

About Gif files
Hi, i need information regarding to how to emedded gif files in a dll. so that, that dll work as a library of gif files. How can i do that? Thanks in advance regards, koti Read this about creating a resource only dll. http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vccore/html/_core_Create_a_Resource.2d.Only_DLL.asp Then you can import you gif files by bring up the new resource dialog from resource view, and select import. Once you select the gif file the program will ask you what type it is. Type in GIF. And you are set. AliR. "Ram" &l...

How to print 2000+ characters seen in formula bar? #2
Formula bar shows +1024 characters, screen only shows 1024. Printer only prints 1024. How can I print all? Add some alt-enters to force new lines within the cell. Use a smaller font. Widen the column width lengthen the row height. Curious wrote: > > Formula bar shows +1024 characters, screen only shows 1024. Printer only > prints 1024. How can I print all? -- Dave Peterson ec35720@netscape.com Thanks, but none of your suggestions work. I'm using Excel 2003 by the way. "Dave Peterson" wrote: > Add some alt-enters to force new lines within the cell. &g...

Copy column headings AND formula totals to blank spreadsheet
Hi Everyone! I apologize that this was likely asked before but could someone show me how I can copy my 'column headings, column widths and totals formula' to a blank spreadsheet tab? I have set up budget spreadsheet where I input my receipts to keep track of monthly expenditures. Could someone explain how to copy the column headings, the column widths, AND the formulas of each column to a blank spreadsheet tab so each month will contain the 'same headings, same column widths and the same formula for each column'? Thanks so much for any help!!! katy Assuming you alr...

Open file(2) from file(1) and then close file(1)
I need to open a file(2) when I open file(1) and then close file(1) File(1) name can change so I need variables. sequence will be Open File(1) Open File(2)... I do it by the following macro: Public LaunchFile As String 'in this public variable I want to keep file(1) name Private Sub Workbook_Open() Dim lDir, lFile, lFileMtr As String lDir = Sheets("launch").Range("B2") & "\" 'direction of my file to open lFile = Sheets("launch").Range("B3") & ".xls" 'file name to openm lFi...

Backing Up Files
I have just finished a back-up of my files as of June 30th, It took 5 DVD's to complete using the "Back Up Program" on my Vista Home Premium, 32 bit start menu. Does this sound reasonable? If it became necessary to restore my files following a hard disc problem, would all my spreadsheets, letters, programs, registry, desk top, etc. be restored to the condition they are now? If not, what would likely be lost? As you see, I am not very knowledgeable concerning computer details. Thanks for any help you can offer. Gene L. On Wed, 2 Jun 2010 08:03:55 -0400, &qu...

Can this VBA for work on text files be modified for a different job ?
I have been using the code below (which, from memory, came from the M-Soft site before I hashed it about) successfully to open 2 text files, appending the whole of one file to the end of the other, then deleting the source file. Now a new task is to; - update all text files in a specified folder by appending a new record. This is to be a daily routine, and there should end up being about 800 to 1000 text files to be updated. - The information for the record that is appended to each file comes from a single text file each day (source file). - The first column of this 'source file' ho...

recovering pst file from documents and settings\Admin
Several computer techs have been unable to get my system out of a safe mode loop. I have made that HD a slave. I have recovered all the data by my outlook file (pst). The Documents and setting etc directory and access to files are protected by the Administrator sceutiry. I really neet to recover my Outlook mail file. How can I do it? Please giv eme a Christmas present by telling me how. Merry Christmas to ALL Are you the administrator? If so then you need to point your emai profile to use this .pst as your data file ----------------------------------------------- ~~ Message po...

orphan personal folders file
I've got a personal folders entry on my outlook 2003 folder list that isn't connected to any actual folders. If I click on it I see an error message that says "The set of folders could not be opened" Presumably somewhere in the registry is an entry that leads to a null or non-existant file. Could anyone point me at a remedy? I've searched MS Support but no help there. Thanks Posted here countless times. You have a corrupt profile, probably from migrating a data file incorrectly. It's harmless. If you want to fix it, create a new profile from scratch. --...

archiving e-mail .pst file limit
Hi , is ther a file size limit for the .pst file? I have had problems with users who have a pst file close to 2gig not being able to archive files unless they create a new pst file and then they are able to archive. Also if there is a size limit , can it be reset to a higher size? Thanks 2GB is the file size limit for pst-files created in Outlook 97-2002 and cannot be extended. The pst-files created in Outlook 2003 by default can become 20GB but can be extended to a virtual unlimited size. -- Robert Sparnaaij [MVP-Outlook] www.howto-outlook.com Tips of the month: -Properly back-up ...

File does not display
I have a user that has a password-protected file that does not display after providing the password. The sequence is as follows: After double-clicking on the file the password dialog box is displayed, where I enter the password After clicking OK, another dialog box is displayed with the message "The filename.xls is reserved by user name. Enter the password for write access or open as read only. The OK command button is grayed out, until I provide the password so I provide the password again and click OK. I am then returned to the null menu. Going to Window, all options are g...

How do I open Zipped or rar files
I am trying to download some game files from the internet . That is no problem but after I have downloaded them into my downloads I then copy them into my the game's user files. I manage ok until I have a file with rar in the front of the title. Could you please tell me how to unlock or unzip these files? Thanks a lot And this has something to do with Microsoft Word?? http://lmgtfy.com/?q=rar On Sat, 20 Feb 2010 18:36:01 -0800, Pamboa <Pamboa@discussions.microsoft.com> wrote: >I am trying to download some game files from the internet . That is no >problem...

Adding something to a complicated formula
Hey peeps! I have a formula that turns the cell red if the number of received items is less than the number ordered, and only if the scheduled date they were to come in is past today's date. I would like to add in the formula the ability to recognize in the ordered and recieved column if I needed to order more at a later date, or if I received the total items at different times...like 11+3 in the ordered column, meaning I ordered 11, realized I needed more and ordered 3 more. Can I make a formula to recognize this additional requirement? Here is the original formula I have in the cell...

How do i print an excel file in the form of a booklet?
I have a list of phone numbers and addresse that I need to print in the form of a small booklet that I can carry in my purse. How do I print it? What program and how to set it up? Also I want ohotos on the left and data on the right side pages. I'm not sure excel is the best application for this kind of thing. I'm sure you could do it (after a few hours/days/months of frustration!), but maybe you could find a better application by searching google. Or even put the data into MSWord and use that for all the nice formatting. shireen wrote: > > I have a list of p...

Opening an excel file
I used to be able to double-click on an excel file and it would open automatically. Now, all of a sudden, when a double-click an excel file, only the application opens and I need to then browse and open the file. I find this to be a nuisance. How can I return to double-clicking to open a file? -- M.L. Do you have other excel files open at the time or does this happen when opening the first one? Mel;670895 Wrote: > I used to be able to double-click on an excel file and it would open automatically. Now, all of a sudden, when a double-click an excel file, only t...

Validation errors in xml file
Hi, I am trying to use a validation method against this xml and keep getting 'The 'name' attribute is not declared' and I can't figure out why. Here is my XML: <XmlCommand xmlns="http://intranet/hstServices/Schemas/XmlCommand.xsd" connectionKey="SomeKey" commandTimeout="60" appId="122" appScreenType="879"> <Proc name="SomeProcName"> <Param name="@SomeParam" dataType="Char" dataLength="3" direction="Input">MHR</Param> </Proc> <E...

Locating Outlook Notes Files
I had a computer windows clash. The hard drive has to be reformated in order for my computer tostart the windows agian. Fortunately, I had another hard drive, and I switched my old hard drive to new hard drive, installed Windows in my new hard drive, and manage to start the computer again. Now I could go to F: drive (which used to be my C: drive), and save all my important files to my current C: drive (new hard drive with fresh new Windows installation). This way, I could save a lot of Excel, Access, and Word files. However, I had many important Outlook Notes in my original hard drive, an...