none
Excel mod function error. Excel 2007

    Question

  • I have the following equation in Excel 2007: =MOD(AD6+AA27,20). The answer it was giving was wrong (should be zero, showing 20).  I broke it down to find out AD6+AA27 = 80.  So I tested with constants MOD(80,20) which came out zero.  I put the formula in cell next to it and it came out correctly as zero.

    If I delete the contents of the bad cell (the formula) and then click the undo button, the formula comes back as you would expect.  Weird thing is that the formula calculates correctly after the delete/undo sequence and the value of the cell is correctly showing zero.

    Appears to be a bug. ?

       
    Tuesday, October 09, 2012 2:20 PM

All replies

  • On Tue, 9 Oct 2012 14:20:57 +0000, LW4 wrote:
     
    >
    >
    >I have the following equation in Excel 2007: =MOD(AD6+AA27,20). The answer it was giving was wrong (should be zero, showing 20).  I broke it down to find out AD6+AA27 = 80.  So I tested with constants MOD(80,20) which came out zero.  I put the formula in cell next to it and it came out correctly as zero.
    >
    >If I delete the contents of the bad cell (the formula) and then click the undo button, the formula comes back as you would expect.  Weird thing is that the formula calculates correctly after the delete/undo sequence and the value of the cell is correctly showing zero.
    >
    >Appears to be a bug. ? 
     
    The more likely explanation is that AD6 + AA27 are not adding up to "exactly" a multiple of 20.  You should be aware that what you see in a cell may not be exactly the value stored in the cell, due to formatting.
    If you need a certain level of precision, round to that level.  You could use the "precision as displayed" option, but this will irreversibly alter the precision on ALL of your values.
     
    =MOD(round(AD6+AA27,8),20)
     

    Ron
    Tuesday, October 09, 2012 6:19 PM
  • Seems like that would not explain why delete/undo or editing the formula makes it calculate correctly.  Or am I wrong about that?  For example:

    If I put a "1" in front of the 6 like this:

    =MOD(AD16+AA27,20)

    But before hitting [Enter], I delete the one to make the formula exactly like it was.  This test then yields a correct result (zero). 

    More info: I have Calculation Options set to "Automatic".  I also clicked "Calculate Now" and it did not change the incorrect result.

    Thanks


    LW

    Tuesday, October 09, 2012 6:29 PM
  • Well, with only partial information, it is unlikely that I can provide you a fully coherent explanation.  In addition to values displayed being not exactly what they appear due to rounding issues, calculation ordering and iteration can also affect results.

    To go further, I would suggest you post a copy of the misbehaving workbook, sanitized as necessary, on some publicly accessible website, like Skydrive, and post a link here so that I and others can see exactly what you are writing about.


    Ron


    Tuesday, October 09, 2012 6:58 PM
  • Here is the link for the misbehaving workbook if I did it correctly.

    https://skydrive.live.com/redir?resid=EF53FA6589A5843A!108&authkey=!AEnttrfbJkA7ORw

    Go to worksheet PP5 and look at cells AE5 adn AE6 which contain the MOD function formula.


    LW

    Tuesday, October 09, 2012 7:45 PM
  • The issue has to do with the order in which Excel performs calculations.  And one problem with my reproducing it is that Excel does a full recalculation before it saves, so I can't even save the workbook and be able to open it with the error, because it is fixed before the save.

    If I manually trigger a "complete" recalculation (ctrl-alt-F9), the error goes away.

    The algorithm used in recent versions of Excel is pretty complicated, and described in various web articles as well as the MSKB.

    I suppose there could be something peculiar to Excel 2010 not present in 2007 (which is what I have).  But they are supposed to be the same in this regard.  However, once I have merely edited the formula (even without changing it); forced a ctrl-alt-F9 recalculation; or just saved the workbook, I cannot get the error to return.

    In some cases, the last formula entered goes to the end of the list, so might try just adding in the formula again.  Change it to something else; then re-enter it.

    If you have "corrected" the error, saved the workbook and then re-opened the workbook, is there a sequence of operations you can go through that will cause the error to re-appear?


    Ron

    Tuesday, October 09, 2012 8:33 PM
  • Once the error goes away (using one of the methods you listed above), I have not been able to reproduce it. 

    With Excel 2007, if I do a "Save-As", it recalculates and fixes the error.   If I edit some other cells and then save when exiting, the error stays. 


    LW

    Tuesday, October 09, 2012 8:43 PM
  • However, if you edit a cell that is "in the chain" of the cells showing the error, the error also goes away, before you save.  For example, editing (change it from an 8 to a 6 and back again) Instructions!A10 corrects the error in PP5!AE5; and Instructions!A14 will correct the error in PP5!AE6.

    Also, merely editing, without changing, the error cells, or even just PP5!AA27 removes the error.  (i.e. select PP5!AA27; put the cursor in the formula bar; <enter>).

    Again, this points to an issue with Excel's dependency tree; not the MOD function itself.  And it may have to do with the order in which the equations and constants were entered in the workbook.

    But it seems that once the workbook has completely calculated, that the error is gone.


    Ron

    Wednesday, October 10, 2012 2:14 AM
  • Ron wrote:

    Also, merely editing, without changing, the error cells, or even just PP5!AA27 removes the error.  (i.e. select PP5!AA27; put the cursor in the formula bar; <enter>).

    Again, this points to an issue with Excel's dependency tree

    I hope that after reading my (other) response, you will realize that your assertion is incorrect.

    There is no issue with Excel's dependency tree or order of recalculation.

    Instead, you are simply "correcting" some legitimate infinitesimal differences that arise in Excel arithmetic, but that Excel is unable to display due to its limitation of displaying only up to 15 significant digits.

    In effect, by editing the cell, you are truncating the actual value to the 15 significant digits that Excel will format.

    • Edited by joeu2004 Thursday, October 11, 2012 7:07 PM
    Thursday, October 11, 2012 9:24 AM
  • That's at first what I thought the problem was, but I did not find the differences that your more thorough analysis has uncovered, so thought there might be a dependency tree issue.

    However I still do not understand why a value of -4.2632564145606E-14, the value that appears to be in PP5!AE5 and AE6 (the result of the MOD formula), whether or not I have edited that cell, will initially display as 20, and subsequently display as zero.  When I've seen the "rounding problem" issues in the past, the results display had not shown these variations.


    Ron

    Thursday, October 11, 2012 11:21 AM
  • Ron wrote:

    I still do not understand why a value of -4.2632564145606E-14, the value that appears to be in PP5!AE5 and AE6 (the result of the MOD formula), whether or not I have edited that cell, will initially display as 20, and subsequently display as zero.


    I see your point -- now.

    When I first looked into this, I did not see that misbehavior.  I tried saving and reopening the file.  I tried ctrl+shift+alt+F9.  In both cases, PP5!AE5 and PP5!AE6 continued to display their original values, namely 10 and 20 respectively.  Yet those were sufficient steps to reproduce the inconsistency you and LW observed, according to your original response after LW posted a link to the uploaded file.

    However, I eventually got into a state where ctrl+shift+alt+F9 is indeed sufficient to change PP5!AE5 and PP5!AE6.  (Although I am not sure with which instance of the file.  I was pressed for time when the change in behavior occurred.)

    I cannot explain that inconsistency in my observations.  And at the moment, I do not have access to the computer with all my files and tools.

    So for now, I have deleted my original response to LW.  Even though I think it contained valuable information and "good practices" which might avoid the problem in the first place, it contained a number of misstatements, and it might be a misdirection in the final analysis.

    And I wanted to post this follow-up now to retract my previous apparent misstatements about your observations.

    I have my doubts that this is due to a problem with the recalculation engine.  But it is not impossible; and I confess that I cannot explain how the file might have gotten into that state.

    I do note that when I try to download LW's file, Skydrive says it cannot open the file because of unsupported features, namely protected worksheets.  But when I open the local copy of the file on my computer, I do not see any protection.

    I don't know if or how that might be relevant, if the information is even correct in the first place.  And I confess:  worksheet and workbook protection is not a feature that I have a lot of experience with.  So I might easily have overlooked something.

    Perhaps LW can give us more information about the history of the uploaded file.

    PS:  LW reported the problem with Excel 2007.  You mention Excel 2010; it is unclear which one you are using.  I just want to say that there was a change in the MOD implementation in Excel 2010 that does affect the arithmetic anomalies in some cases.  So it is important to use Excel 2007 when trying to duplicate any arithmetic anomalies that I describe.

    Thursday, October 11, 2012 7:42 PM
  • Observations and clarifications:

    Interesting observation that, at least initially,
    ctrl-shift-alt-f9 did not correct the problem on your file.  I had not
    tried that, just ctrl-alt-f9 which did work.  But on a saved file on my
    computer, ctrl-shift-alt-f9 also corrects the displayed result.

    That SAVE'd file was obtained by "downloading" the file from skydrive -- not "opening" it with Excel.

    With regard to my observations about saving:  Save did NOT correct the problem,  but SAVE AS  did.

    With
    regard to the sheet protection, I received the same warning.  When I
    examine the downloaded file, I note that worksheet: "Leave Record" is
    protected.  The other worksheets are not.

    Excel 2007 is the only version I have.

    I,
    too, have no idea how the workbook got into this state.  Nor do I know
    how to return it to that state once it is corrected.  But it is an
    interesting anomaly.

    Another random observation of uncertain
    significance:  In an UNcorrected version of the workbook (one which
    shows a value of 20 in AE6, but contains -4.263E-14 when "F9'ing" the
    formula bar, a formula  =AE6 entered in some other cell on that
    worksheet will both display 20, and also show a value of 20 when F9'ing
    the formula bar.  Correct the display in AE6 by selecting that cell;
    editing the formula bar; and <enter>.  The =AE6 formula remains
    showing 20; and a second =AE6 formula will now show zero!

    Hopefully the following series of pictures will demonstrate what I am seeing.  And Calculation mode IS set to Automatic:

    Initial worksheet

    Add =AE6 to some cell 

    Then "correct" the value in AE6 by editing that cell in the formula bar:

    Enter =AE6 to another cell:

    I have no idea how to go about analyzing this. But ctrl-alt-f9 does seem to correct everything.  Since a forced recalculation seems to correct things, that was what led me to consider a problem in the dependency tree, but I don't know enough about it to be able to do anything more than advance this speculation.


    Ron

    Thursday, October 11, 2012 11:53 PM
  • Thank you for discussion on this problem.  I did post another version with protection off on "Leave Record" if that helps.

    https://skydrive.live.com/redir?resid=EF53FA6589A5843A!108&authkey=!AEnttrfbJkA7ORw

    LW


    LW

    Friday, October 12, 2012 2:08 PM
  • LW wrote:

    I did post another version with protection off on "Leave Record" if that helps.
    https://skydrive.live.com/redir?resid=EF53FA6589A5843A!108&authkey=!AEnttrfbJkA7ORw

     

    First, this is the same URL as the previous one.  And I still get the "protected sheets" error that limits us to opening the file in our local Excel or saving to our local disk first.

     

    But that is a good thing!

     

    Please do not change the original uploaded file.  And please do not delete it.

     

    The recalculation problem Ron is talking about is state-dependent.  And any change to the file, be it on your system or on Skydrive, might impair our ability to duplicate and discuss the recalculation problem.

     

    As of this writing, I have verified that the URL above still references a file that duplicates the recalculation problem.

     

    As for uploading any new version without sheet protection....

     

    First, please be sure that it has a different name and a different URL (link).

     

    Second, we don't need it as far as I am concerned.

     

    I never like opening files in Skydrive (or any "cloud" application, for that matter).  I prefer to download the original file.  I always worry that what I download after opening the file in Skydrive is not the original bits of the uploaded file.

     

    And on the contrary, the protected sheet could be part of the recalculation problem, for all we know.  (We really don't know anything at this point.)


    • Edited by joeu2004 Friday, October 12, 2012 4:32 PM
    Friday, October 12, 2012 4:27 PM
  • I did leave both there and gave a new name to the unprotected file: "TA form bug no prot.xlsx".  I just shared the link of the wrong file after uploading.

    No protection:

    https://skydrive.live.com/redir?resid=EF53FA6589A5843A!113&authkey=!ABrV9bbNKRkxuoc

    Original:

    https://skydrive.live.com/redir?resid=EF53FA6589A5843A!108&authkey=!AEnttrfbJkA7ORw


    LW

    Friday, October 12, 2012 4:52 PM
  • Ron wrote:

    I still do not understand why a value of -4.2632564145606E-14, the value that appears to be in PP5!AE5 and AE6 (the result of the MOD formula), whether or not I have edited that cell, will initially display as 20, and subsequently display as zero.  When I've seen the "rounding problem" issues in the past, the results display had not shown these variations.

     

    I believe I can explain everything now.

     

    The key thing to note is:  there is no big recalculation mystery.

     

    And the other key thing to note is:  this is all avoidable by properly rounding formulas explicitly to the expected (required) precision.

     

    In general, if you expect the result of a calculation with non-integers (and division of integers) to be accurate to a specific precision, you should explicitly round the calculation to that precision.

     

    For example,
    1. PP1!X27 should be =ROUND(SUM(X13:X25),2)
    2. PP1!AA27 should be =ROUND(SUM(X27:Y27),2)
    3. PP2!AE5 should be =ROUND(MOD(ROUND(AD5+AA27,2),Instructions!C12),2)

     

    If you had followed this "good practice", you might not have stumbled over the problems in the first place.

     

    In summary....

     

    -----

    1. LW wrote:  "=MOD(AD6+AA27,20). The answer it was giving was wrong (should be zero, showing 20)".

     

    Explanation:  the value was not 20 to begin with.  Instead, it was infinitesimally less than 20, which is consistent with the definition of MOD.

     

    However, it was so close to 20 that Excel displayed 20 even when formatting with 13 decimal places, due to Excel's limitation of formatting only up to 15 significant digits.

     

    -----

    2. LW wrote:  "Weird thing is that the formula calculates correctly after the delete/undo sequence and the value of the cell is correctly showing zero".

     

    I think the simplest explanation is (Occam's Razor):  the file had been accessed and last saved by Excel 2010 (or later).

     

    When you open a file in Excel (any version), cells are normally not recalculated.  (Except for "volatile" functions and dependent cells and some other anomalous conditions.)

     

    Consequently, when you subsequently opened the file in Excel 2007, you are seeing the value left from Excel 2010.  But when you changed PP5!AE6 (for example) or something PP5!AE6 depends on, then Excel 2007 recalculated it.

     

    In short, there is no defect in the recalculation engine.  It is performing exactly as expected.

     

    The key issue is:  there is a difference in the way that Excel 2007 (and earlier) and Excel 2010 (and later) calculate MOD.  More to the point, the Excel 2007 method has a defect; the Excel 2010 method is correct.  I will explain further in #3 below.

     

    The irony is:  what appears to be 20 is the correct result.  What appears to be zero is the wrong result, in part because it is an infinitesimally small negative number.  MOD should never return a negative number, infinitesimal or otherwise, when both parameters are positive.

     

    -----

    3. Ron wrote:  "I still do not understand why a value of -4.2632564145606E-14".

     

    Of course, that was not the gist of the excerpt, taken out of context.  But the point is:  Ron might have stopped there and wondered why MOD is returning a negative number at all.

     

    Explanation:  it is due to a defect (IMHO) in INT, which propogates into the internal calculation of MOD.

     

    This is compounded by the fact that most of the numbers are not what they seem to be, as Ron conjectured initially.  However, this is not always apparent by formatting with 15 decimal places, due to Excel's limitation of formatting of only 15 significant digits.

     

    MOD(a,b) is defined as a - b*INT(a/b).  Since MOD should return the remainder of a/b, the presumption is:  a >= b*INT(a/b).

     

    The formula in PP5!AE6 is:  =MOD(AD6+AA27,20).

     

    AA27 is exactly 64.  But AD6 is exactly 15.9999999999999,573674358543939888477325439453125, which is 16-12*2^(-48).  Consequently AD6+AA27 is exactly 79.9999999999999,573674358543939888477325439453125, which is 80-3*2^(-46).

     

    Note that AD6 is so close to 16 that Excel displays it as 16, even with 13 decimal places (a total of 15 significant digits).  Likewise, Excel displays AD6+AA27 as 80, even to 13 decimal places.

     

    Consequently (AD6+AA27)/20 is exactly 3.99999999999999,7779553950749686919152736663818359375, which is 4-5*2^(-51).  And this is where the Excel 2007 INT defect arises.

     

    The correct result from INT((AD6+AA27)/20) is 3.  Certainly, INT(x) <= x should always be true for positive x.  And this is borne out by the VBA implementation:

     

    Function vbint(x As Double) As Double
    vbint = Int(x)
    End Function

     

    But Excel INT returns 4(!).  I believe that is because 4-5*2^(-51) is so close to 4 that Excel formats it as 4 due to its limitation of formatting 15 significant digits.

     

    In any case, 20*4 = 80, which is larger than 80-3*2^(-46), the exact value of AD6+AA27.  Consequently, MOD returns -3*2^(-46), the correct result of 80-3*2^(-46) minus 80.

     

    By the way, Excel 2010 INT(4-5*2^(-51)) also incorrectly returns 4.  However, it appears that Excel 2010 MOD does not use that result internally.  Instead, it is probably implemented like one of the following VBA emulations:

     

    Function vbmod(x As Double, y As Double) As Double
    vbmod = x - y * Int(x / y)
    End Function

    Function vbmod64(x As Double, y As Double) As Double
    Dim t As Double
    t = x / y
    t = Int(t)
    t = y * t
    vbmod64 = x - t
    End Function

     

    The difference between vbmod and vbmod64 is the pairwise rounding of the 80-bit arithmetic native to Intel CPUs to 64-bit.  The latter, vbmod64, is how Excel evaluates formulas.  But at this moment, I cannot remember which method Excel uses in the internal MOD implementation.

     

    (I did intuit that some time ago.  I just don't remember.  And I cannot intuit it from this example because for MOD(AD6+AA7,20), vbmod and vbmod64 return the same exact results.)

     

    In any case, Excel 2010 MOD(AD6+AA27,20) returns exactly 19.9999999999999,573674358543939888477325439453125, which is 20-12*2^(-48).  And that is the exact value we see in the uploaded file when we open it initially in Excel 2007.

     

    Since that is less than 20, it is indeed a correct (at least feasible) MOD result.  However, Excel displays 20, even with 13 decimal places, due to its limitation of formatting only 15 significant digits.  So the displayed value is misleading and admittedly confusing.

     

    -----

    I hope this helps.  I can provide more details, if desirable.

     

    And I hope this addresses all questions.  There has been a dizzying list of conjectures and conclusions.  And I am struggling to work around the behavior of this GUI.  In my confusion, I might have overlooked or forgotten something that I intended to comment on.


    • Edited by joeu2004 Saturday, October 13, 2012 2:50 AM
    Friday, October 12, 2012 6:42 PM
  •  

    [NOTE: This is a resubmit of a posting that I deleted. The previous version contained much misinformation]

    Update.... I wrote:

    When you subsequently open a file in Excel (any version), cells are normally not recalculated.  (Except for "volatile" functions and dependent cells and some other anomalous conditions.)
     
    Consequently, when you subsequently opened the file in Excel 2007, you are seeing the value left from Excel 2010.  But when you changed PP5!AE6 (for example) or something PP5!AE6 depends on, then Excel 2007 recalculated it.


    This does not explain why we get a "save changes" prompt when we simply open, then close the file, and why PP5!AE6 is changed when we do save.

     

    Is this indeed a flaw in the recalculation engine?

     

    No.  There is a simple explanation.  The workbook uses TODAY() in many Conditional Formats as well as in some formulas in "Leave Record" worksheet.

     

    When I eliminate all of the uses of TODAY() [1], PP5!AE6 is not changed when we save the now-modified file in Excel 2007.  That is, PP5!AE6 retains the value displayed as 20; it is not changed to a value displayed as zero (or about -4.26E-14 when the cell format is changed).

     

    Subsequently, I can open and close the file without getting a "save changes" prompt.  And PP5!AE6 remains unchanged.

     

    Moreover, I can modify other unrelated cells and save the modified file, then close and reopen the file, and PP5!AE6 remains unchanged.

     

    In summary, everything is copacetic and consistent with the correct behavior of the recalculation engine.

     

    -----

    PS:  Ron, you are correct that save v. "save as" makes a difference.

     

    Simply saving does not alter PP5!AE6.

     

    But for some reason, "save as" does cause PP5!AE6 to be recalculated, which causes Excel 2007 to display zero instead of 20 for the reasons that I explained previously (XL2007 v. XL2010 MOD).

     

    I conjecture that Excel rebuilds the dependency tree for the "new" file, rather than reuse the dependency tree that existed for the "old" file.

     

    That makes some sense, when I think about it.  But it caught me by surprise.  Until now, I did not think there was any semantic difference between save and "save as" other than the name change.

     

    Good to know for future experiments.

     

    -----
    [1] To be specific, this is the procedure I followed for initially modifying the file in Excel 2007.

     

    1. Download the Skydrive file to a local folder.
    2. Open the file in Excel 2007.
    3. Unprotect the "Leave Record" worksheet.  There is no password.
    4. Delete all Conditional Format rules.
    5. Find and replace all TODAY() references with a constant.
    6. Close the file, responding "yes" to the "save changes" prompt.
    7. Open the file in Excel 2007 again.  PP5!AE6 still displays 20.
    8. Put a constant into PP5!AE8, an arbitrary and unreferenced cell.
    9. Close the file, responding "yes" to the "save changes" prompt.
    10. Open the file in Excel 2007 again.  PP5!AE6 still displays 20.

     

    LW, I am not suggesting this procedure as a work-around or fix.  I am simply doing it for diagnostic purposes, to identify the root cause of the questioned behavior.

    • Edited by joeu2004 Saturday, October 13, 2012 2:55 AM
    Saturday, October 13, 2012 12:25 AM
  • Ron wrote:

    Add =AE6 to some cell [...AF6...]

    [....]
    Then "correct" the value in AE6 by editing that cell in the formula bar:

    Enter =AE6 to another cell:

    [....] But ctrl-alt-f9 does seem to correct everything.  Since a forced recalculation seems to correct things, that was what led me to consider a problem in the dependency tree

     

    But note that ctrl+alt+F9 simply forces recalculation regardless of change.  It does not necessarily rebuild the dependency tree, as ctrl+shift+alt+F9 does.

     

    Moreover, note that if we actually change the text of the formula in (PP5!)AE6, replacing AD6+AA27 with 20 for example, the value in AF6 (=AE6) does change as expected, unlike the misbehavior in your example.

     

    So I would conclude that there is indeed something hinky in the recalculation engine, but not necessarily in the dependency tree per se.

     

    On the contrary, given the latter example (changing the text of the formula), I would conclude that there is no problem with the dependency tree per se.

     

    My first guess was:  simply "editing" AE6 by pressing F2, then Enter (but not actually changing the text of the formula) does not trigger a recalculation cycle.

     

    That is indeed the case for cells with constants [1].  Pressing F2, then Enter triggers a change event, but not a calculation event for the modified worksheet [2].

     

    However, for cells with formulas, pressing F2, then Enter does indeed trigger a calculation event as well as a change event for the modified worksheet.

     

    So my guess is:  the recalculation engine re-evaluates a cell with a formula if the text of a formula (or the internal representation of a constant [1]) in a direct or indirect precedent cell was modified, but not if only (the internal representation of) the value was modified.

     

    [EDIT] Not intended to be a rigorous specification.  Obviously, additional rules apply when the formula references a volatile function and probably even a non-volatile UDF.  TBD.

     

    And I would agree that that seems to be a flaw in the design of the recalculation engine.  (But again, not in the dependency tree per se.)

     

    And thanks for that discovery.  But I think it has an effect only in a situation like I speculated in another follow-up posting in this discussion, namely:  the workbook was last modified by a different version of Excel, and different internal implementations in the two versions of Excel result in different (internal representations of) values in cells.

     

    Definitely something for us to keep in mind when users present "impossible" situations like LW's.

     

    -----

    [1] For cells with constants, simply pressing F2, then Enter does not trigger a calculation event unless the internal representation changes.

     

    For example, enter 1 into AF6 and =AF6-1-0 into AG6 formatted as Scientific.  Select AF6, press F2, then Enter.  That triggers a change event, but not a calculate event for the PP5 worksheet.

     

    On the other hand, enter =1+2^-52 into AF6, then overwrite AF6 using copy-and-paste-special-value.  Note that AG6 displays about 2.22E-16 (2^-52).  Select AF6, press F2, then Enter.  That does trigger a calculate event as well as a change event, and AG6 now displays 0.00E+00.

     

    [2] Set up the following event macros in the PP5 worksheet module:

     

    #If 1 Then

    Private Sub Worksheet_Calculate()
    MsgBox "Worksheet_Calculate: " & Me.Name
    End Sub

    Private Sub Worksheet_Change(ByVal Target As Range)
    MsgBox "Worksheet_Change: " & Me.Name & "    " & Target.Address
    End Sub

    #End If



    • Edited by joeu2004 Saturday, October 13, 2012 8:39 PM
    Saturday, October 13, 2012 8:25 PM
  • Thank you for that detailed analysis.  I understand some of it, and will work on understanding the rest.  I only have Excel 2007, and the only "tool" I have to look at precision greater than that of Excel is the xNumbers add-in for Excel.

    But I now understand some of the differences between Excel 2007 and 2010, and a bit more about some of the fine points.  In particular, the lack of recalculation on opening the file, and seeing the result from Excel 2010 when I do open it.


    Ron

    Monday, October 15, 2012 2:05 AM
  • Ron wrote:

    Thank you for that detailed analysis.  I understand some of it, and will work on understanding the rest.

     

    I'm glad they were interesting.  Let me know if I can explain things better or differently to improve your understanding.

     

    Ron wrote:

    I only have Excel 2007

     

    You might want to download recalc.zip (click here) [1].  It contains several simple files that might be useful.

     

    In particular, you can open "mod 2010.xlsx" in Excel 2007 to demonstrate the issue that most likely explains LW's problem.

     

    A1 contains the formula =MOD(80-2^-46,20).  It will initially display 20 (really less than 20), but it will display -1.4E-14 after "editing" A1 (press F2, then Enter) or pressing ctrl+alt+F9.

     

    Since there are no dependencies or other complexities in the workbook, there should be no doubt about the integrity of the dependency tree per se.

     

    Ron wrote:

    the only "tool" I have to look at precision greater than that of Excel is the xNumbers add-in for Excel.

     

    I am not familiar with that tool.  Off-hand, I would not rely on it to ascertain the exact internal representation of Excel cells.

     

    Instead, you can enter the following formula (useful for any value):

    =A1-TEXT(A1,"0.00000000000000E+0")-0

    formatted as Scientific.  Note that there are 14 zeros after the decimal point.

     

    Ron wrote:

    But I now understand some of the differences between Excel 2007 and 2010, and a bit more about some of the fine points.  In particular, the lack of recalculation on opening the file

     

    Actually, I am discovering that it is not so cut-and-dried.  Recalculation has evolved from XL2003 to XL2007 to XL2010.  Each is slightly different :-(.  This is sure to become an issue when we help people with "impossible" situations like LW's.

     

    For example, the file "mod 2007.xls" also has =MOD(80-2^-46,20) in A1.  It displays about -1.4E-14 initially since it was calculated with XL2007.  When that file is opened in XL2010, A1 immediately displays 20 (really less than 20).

     

    That demonstrates that XL2010 recalculates some or all cells at open time, unlike XL2003 and XL2007.  (TBD:  Is it just cells with functions that changed in XL2010; or is it all cells?  This might be difficult to determine, due to the following observation.)

     

    Moreover, both XL2007 and XL2010 treat formulas that call UDFs differently from XL2003.

     

    Use the file "myrand.xls":  A1 has the formula =myrand().  Open first in manual calculation mode in order to see the initial value.  Then close the file, and open again in automatic calculation mode.

     

    [EDIT] Clarification....  In the description below, "myrand 2003.xls" was saved and reopened in XL2003; "myrand 2007.xlsm" was saved and reopended in XL2007; and "myrand 2010.xlsm" was saved and reopened in XL2010.

     

    In XL2003, the myrand function is not called when the file is opened in automatic calculation mode.  I depend on this behavior to design simulations that are "frozen" until I manually execute a macro to "refresh" the simulation.

     

    But in XL2007 and XL2010, the myrand function is called when the file is opened in automatic calculation mode and the "disable macro with notification" option is selected in the Trust Center, once we enable macros manually.  (My modus operandi.)

     

    On the other hand, the myrand function is not called at open time when the "enable all macros" option is selected in the Trust Center (not recommended), even in automatic calculation mode.

     

    That difference between manual and automatic enabling of macros seems inconsistent and inexplicable, IMHO.  Dare I say "defective"? ;-)

     

    (Well, "flawed design" is a better way to say it.)

     

    And the behavior with manual enabling of macros is undesirable for my purposes.

     

    Oh well, "stuff happens!".  Par for the course with Microsoft products. :-(

     

    -----

    [1] URLs for uploading the zip file and individual files.

    "manual calc.xls"       https://www.box.com/s/uf5jvy98m9enc5k76z02
    "mod 2003.xls"          https://www.box.com/s/2r6jeztqgsjktoqej7dm
    "mod 2007.xlsx"        https://www.box.com/s/w9bt4md7hag9w81hc05s
    "mod 2010.xlsx"        https://www.box.com/s/vmycjhtef1sftiryigod
    "myrand 2003.xls"     https://www.box.com/s/3j6eickreztwecarbku0
    "myrand 2007.xlsm"  https://www.box.com/s/pak3zhnyobsyq4y3b1au
    "myrand 2010.xlsm"  https://www.box.com/s/6wz7b0r6612xlf9sud9l
    "recalc.zip"                https://www.box.com/s/edjvu796j3bsqx9iorcw


    • Edited by joeu2004 Monday, October 15, 2012 9:54 PM
    Monday, October 15, 2012 8:58 AM
  • Thank you for those files.

    And yes, xnumbers is not designed to show the internal representation of numbers in Excel, but it does purport to enable operations with a much higher degree of precision than excel.


    Ron

    Monday, October 15, 2012 12:17 PM
  • @joeu2004,

    I think there is a simpler explanation for the behaviour of myrand 2003.xls and some of the MOD behaviour you observe:

    - A workbook that was last calculated in a lower Excel version is full calculated when opened in a higher excel version. This behaviour is designed to try to ensure that version changes in the calculation engine (including function changes like those made to MOD) work correctly. So for example if you open myrand 2003.xls in Excel 2010 and then save it in a trusted location, the next time you open it with Excel 2010 the UDF will not be recalculated.

    - opening a workbook containing VBA UDFs without macros being enabled and then enabling macros will cause the UDFs to be recalculated because thats the safest way (probably the only safe way) to handle the re-enablement.


    Charles Excel MVP The Excel Calculation Site http://www.decisionmodels.com/

    Monday, October 15, 2012 5:21 PM