XL2010 formula copying bug?

See for yourself if you reproduce this. I am an advanced user but I spent substantial time constructing a noise-free and very explicit example so it is very very easy, and very quick, to follow the steps below and reproduce (or not) the bizarre result, though in my experience only about 3 people will do so even though the entire process takes maybe 90 seconds. For you 3, then:

XL2010 (you can try something newer too)
Ctrl-N for New WB with at least sheet1, sheet2 (add a sheet2 if you only have Sheet1 showing)
"Step A"
On sheet1 in B5 type =A5-A4 and hit enter
select B5
Ctrl-C
up arrow
shift up-arrow (B3:B4 is now selected)
Ctrl-V

"Step B"
Select B3
Ctrl-shift-down arrow
Ctrl-C
Ctrl-page down (activate sheet2)
select B1
Ctrl-V
First problem appears. Sheet2!B2 has an incorrect formula.

At this point apologists may say, well you made an invalid paste on the first line so "all bets are off." If that's an attempted explanation, try this exact same exercise in any previous version of Excel and explain that.

More observations:
"Step C" If you go to sheet1 and copy B5 and paste over sheet2!B1:B3 you get the CORRECT result.

"Step D" If you go back to sheet1 and copy B3 and paste over sheet2!B1:B3 you get two wrong results (sheet2!b2:b3). Does your Sheet2!B2 and B3 show #REF like mine does?

"Step E" On sheet1 paste B5 over B3, copy B3, and paste over sheet2!B1:B3 and now sheet2 is back to the correct result. But B3 was a copy of B5 to begin with. So why would copying B5 over B3 change anything?

"Step F" is the last one: if you now select B3:B5 on sheet1 and paste to Sheet2!B1 you now get the CORRECT answer!

Step D and Step E are the same, yet give different answers. All you did was add in Step E, copy sheet1 B5 over B3; but that is exactly what you did in Step A, making B3 a copy of B5. Brrr.

And Step F is practically the same as step B. Brr.


In case I merely have a site-specific issue, I thank anyone who confirms or denies the behavior on 2010 or 2013. If you do nothing but that, it will be helpful and appreciated.

That happens to me as well in 2010. Odd that once I do E, I can no longer get any of the incorrect results.

Thank you for confirming, Nigel. Actually, copying from B4 is still messed up after step E. "Step G:" If you copy Sheet1~B4 to the sheet 2 trio, you get #REFs.

Here's something that seems more disturbing: Now highlight sheet1!B4, and hit F2 and the enter key. Or in the immediate debug window execute activecell.formula = activecell.formula

Now as absurd as it sounds, Step G will now work. This is bad.

Absolute references do not change when copied or filled
An absolute reference is designated in a formula by the addition of a dollar sign ($)

Relative references change.. Your formula changes when you copied it to Sheet2 based on the row or column.

vlady, thank you for making the point that relative references operate as relative references. However I believe that if you spend another minute going through the steps that I outlined, the aberration will be very apparent to you. (Also try "Step G" above, which gives another disturbing result.)

Is this something that causes you ongoing problems?

Exel has bugs. This one barely rises to that level, and is so benign and easily avoided that I don't think it meets anyone's threshold of annoyance or wonder except yours.

I'd be happier if Microsoft made =combin(9,3) return a whole number.

shg is that the example you meant to give? I'm getting 84 out to full significant digits; =84=COMBIN(9,3) is TRUE

Back to the thing, I'm not so sure that the bug is so benign since it causes valid cells to show #REF.

As to easily avoided, I'm honestly interested in knowing how, since it's unlikely for MS to respond to it, even as swell as they are. Also keep in mind that I and others routinely do thousands of copy/pastes and don't visually inspect each one, sometimes with 50 sheets selected at once.

Some users might say, from their paradigm, "it's user error to produce a #REF to begin with," but the point is that I or others may truly want to leave the result as #REF" or "#VALUE" - but only when it actually IS, for gosh sakes! A power user like yourself likely has blasted through with massive formula constructions leaving them as "#VALUE" without previously insisting on having ISERROR or such; and surely you wouldn't want to see #VALUE if it's a good cell.

Originally Posted by Oppressed1

shg is that the example you meant to give? I'm getting 84 out to full significant digits; =84=COMBIN(9,3) is TRUE

That's cosmetic rounding. Enter

Please Login or Register  to view this content.

in the Immediate window.

Or, via formula,

=LOOKUP(COMBIN(9,3), {84})

That is brutal. =INT(COMBIN(9,3)) gives a different result (on the spreadsheet). Sadly that is not even a new issue (2003 has it too).

The calculation process - likely the engine itself - was modified in 2010. The bragfest is at https://support.office.com/en-gb/art...rs=en-GB&ad=GB Unfortunately MOD() was broken, and depending on what was erroneously changed, perhaps other functions. I wonder if anyone will even check. This is real, not some tinfoil rantings. Below is a taste of how MOD() broke in 2010 and 2013. To think that they are so proud of their "optimizations" in the link above, when they clearly didn't exactly know what they were doing. If only they took feedback seriously; I don't know how to get them to even read the following.

(The following actually caused real world business errors by effectively reporting that $12.00 to the nearest dime is $11.90. As you can see it works in 2003 and 2007. It fails beginning with Office 2010.)

=MOD(12,0.1)
Excel.Pro.2003.on.XP.SP.2...............-6.66134E-16
Excel.Enterprise.2007.on.XP.............-6.66134E-16
Excel.2007.SP3.on.XP.-.64.bit...........-6.66134E-16
Excel.Pro.2013-32.on.Win.7.-.64.bit.....0.1
Excel.Pro.2010.on.Win.7.-.32.bit........0.1.
Excel.Pro.2010.on.Win.7.-.64.bit........0.1


How about =MOD(ROUND(12,6),0.1)

shg you'll like this, VBA:
Debug.Print 12 Mod 0.1
gives division by zero

Cosmetic rounding, hmm haven't heard that term. Borland had "guard digits" in F.P. so that e.g. 1 equalled (10 times one tenth). MS likely has something similar. Too bad it's not exposed like proper science. It's entirely plausible that some "genius" saw that a few cycles were being expended for that purpose in MOD() so he/she removed the logic in the 2010 function rewrites, then said "hey, look, I optimized it."

As to COMBIN, it's very likely a F.P. issue giving 83.999... . Factorials get quite huge so I can see them going entirely with double precision to accommodate that. I'm sorry that I can't suggest a nongruesome workaround. The best I could offer is a small epsilon tolerance such as I did with my MOD problem, and utterly gruesome.

=MOD(12,0.1)

Those kinds of errors are just the result of the use of binary floating point.

Debug.Print 12 Mod 0.1
gives division by zero

That's not a bug; the VBA Mod operator rounds its arguments to Long.

shg, thanks. I believe you correctly explained the VBA behavior. As to "just ... floating point" - no. Otherwise =1=(10*0.1) would be false, etc. . They use epsilons or other methods beyond the simple IEEE standard for the 'cosmetic' effect - umm, formerly used them, in the case of MOD.

Aardigspook, it really is spooky and people should be at least concerned, though few will say so. Thank you for the comment, and for "exercising care!"

As to "just ... floating point" - no.

Umm, yes. 0.1 is a repeating decimal in binary, so all bets are off as to getting exact results.

Checking out of this thread.

Sorry to hear that. I wish you had had the time to read what I wrote before answering. All bets are absolutely not "off," I'm surprised you would say that. Thanks still for your contributions.

By the way ... COMBIN is just floating point

All numbers on a worksheet are of type Variant/Double, which is floating point. That doesn't change the fact that COMBIN should always return a whole number.