Mathematical errors in Excel / Looking for suggestions to find cause

DLB20720 · Sep 16, 2017

I bought a new Microsoft Surface Pro 4 (Win 10 / i7 / 16 GB RAM using Excel 2016 64 bit) about 6 weeks ago and had numerous issues with it right out of the box. The OS alone has more bugs than a $5 a night hotel room, but now I am finding mathematical errors in Excel and that’s something I can’t accept. Prior to this using this Surface Pro, I have never found an error in Excel, and I use it anywhere between 20 to 60 hours a week, at times for fairly complex calculations.

A few weeks ago, Excel returned a value (after running a macro) that didn’t look right so I began searching to try to figure out what I did wrong (previously, it was ALWAYS something I did wrong). To my surprise, everything I did was correct and the result displayed was wrong. I could enter the formula manually in an adjacent cell and Excel returned the correct value. When I ran the macro again it still returned the incorrect value. Out of frustration, I decided to close and reopen the workbook and the correct value was there so I continued on with my work. Some of my macros run about 5 million calculations so it would be impossible to find them all, but I encountered the same scenario several times over the next few weeks. Sometimes when I closed and reopened the workbook the correct value would be there, sometimes not. Sometimes when I ran the macro again the correct value would display, sometimes not. And the incorrect numbers are off wildly; it’s not some minor floating point epsilon.

In one workbook, I have about 7,000 rows / 800 columns of stock data and use a macro that can check anywhere from 1 to 20 parameters and ‘score’ a stock based on the total parameters met. This morning, I ran a screen searching for stocks that met 12 specific parameters and Excel returned about 50 stocks that (supposedly) met all 12. But when I looked at each stock, I could see that not all of the parameters had been met, so I checked the data and the way Excel scored that data. Some stocks showed a score of ‘12’ in the cell, but had actually only met 11 specified conditions. I could manually enter a formula to SUM the total and the correct result displayed and showed the macro result was always off by ‘1’. I checked about 40 scores and found at least 20 incorrect results. Opening and closing the workbook had no effect and the correct result did not appear until the 3^rd time I ran the macro.

I have run a RAM diagnostic tool (mdsched.exe) and that found no issues. I’ve been searching the net for a few hours now but can’t find any mention of similar issues. I’m hoping someone here may know of some tests I can run that may pinpoint the issue. I'm kind of suspecting a CPU issue but don't know how to test that.

BTW – this issue is happening in several workbooks and all macros were written in Excel 2016 so there shouldn’t be a migration issue. I’m stumped!

FDibbins · Sep 16, 2017

Before you go blaming the software/hardware, I would suggest checking your data 1st.

Find 1 (some) criteria that should have been found, but were not, then carefully check to make sure there really is a match in that item. Things to check for are leading/trailing spaces, typos, non-printable characters etc
Where is the data coming from? Has that source perhaps changed in some way?

DLB20720 · Sep 16, 2017

Thanks for your response. Good point on the non-printable characters; I've encountered them before and they are hard to detect. That may be applicable in some of the previous results that appeared to be incorrect, but I'm certain I have additional issues.

After I submitted my initial comment / question, I continued checking Excel to see if I could find anything. I discovered that one row (out of the 7,000) showed values in some columns and the macro I ran did not contain any references to those columns. The macro clears the rows and columns first, then runs the formulas. Without any reference in the macro, I should not have any values at all in those columns.

All of my data is entered into separate worksheets using COM modules and web queries and then I run macros to pull the data into one main sheet that is like a database. It's possible the data is corrupt, but I've been using the same data sources for 5 or 6 years with no issues, albeit on a different computer. I'm going to see if I can check the data though, that could be part of the problem, but I don't think it can explain everything I am seeing.

DLB20720 · Sep 16, 2017

One additional point; when I see an incorrect result, I can manually enter the same exact formula in an adjacent cell summing the exact same data as the macro, and get 2 different results. Epsilon aside, that should not happen.

FDibbins · Sep 16, 2017

DLB20720 said:
One additional point; when I see an incorrect result, I can manually enter the same exact formula in an adjacent cell summing the exact same data as the macro, and get 2 different results. Epsilon aside, that should not happen.

hmm if a manual calc gives a different (correct?) answer to the macro, then it seems that the data may be correct - for a formula, at least - but the code may not be seeing it the same way. Can you test on a small (controlled) sample to see if you can find any problems?

DLB20720 · Sep 16, 2017

I have run new macros on copied data in new workbooks and the results are always correct. But those are a small sample. Some of my workbooks eat up 1 GB of RAM, some just 200 MB. Some rum 5 million calculations, some just a few thousand. There doesn't seem to be a detectable pattern.

Mathematical errors in Excel / Looking for suggestions to find cause

DLB20720

Board Regular

Excel Facts

FDibbins

Well-known Member

DLB20720

Board Regular

DLB20720

Board Regular

FDibbins

Well-known Member

DLB20720

Board Regular

Similar threads

Forum statistics

Share this page

Mathematical errors in Excel / Looking for suggestions to find cause

DLB20720

Board Regular

Excel Facts

FDibbins

Well-known Member

DLB20720

Board Regular

DLB20720

Board Regular

FDibbins

Well-known Member

DLB20720

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock