#ExcelPedroWave

Every day occurs calculation bugs in our work and personal life, often without meaning and worse without being aware of their frequency.

I am not referring to errors in measurement (I leave to mechanical engineers, their calibers, their calibration and their CAD tools), but mathematical calculation errors in a formula or computer with exact solution. That is, the glaring errors!

The measurement of error calculations is based on the theory of errors and the Gaussian statistical distribution, with formulas like these:


that, you see, are edited in Excel 2010 through powerful equation tools, with the formulas for calculating errors:
(1) The average value of the measure.
(2) The standard deviation refered to the measures of dispersion around the average value.
(3) The real value of the measure with regard to the value of the absolute error.
(4) The relative error represents the proportion of measured value that is affected by the error.

All this you know in theory if you are an engineer (even if you are a software engineer ) and if you do the development of CAD tools for design, with more reason.

During the development and testing of software we must be aware that any function or algorithm can escape our control, causing miscalculations, such as:

Software bugs are generally due to the rush to deliver a prototype, which the generated solutions are not memorized enough or the source lines of code are not documented, in order to reuse later. When faced with unfamiliar rules or algorithms, for its novelty or the most common case that there was from another software programer, is usually wrong to extrapolate the rules or steps to be skipped so that the calculations or algorithms will be correct in all situations raised or to any user input.

Examples of calculation errors in programming or known bugs:

- Erroneous forecasts in the economic calculation policy (see Merkel in Germany and Zapatero in Spain).

- The Mars Climate Orbiter crashed into the planet's surface at the end of 1999 due to an incorrect metric conversion on their computers.

- Vulnerabilities in telecommunications equipment due to software distributed to date.

- The error of the millennium or Year 2000 problem (Y2K) is a software bug or error caused by programmers and servers and PCs, omitting the years for storing dates, making the January 1, 2000 come back at 1900. The truth is that a multinational, I know well, could not resolve the problem in time and changed the calendar for its equipment, during the months it took to resolve the bug, from 2000 to 1972, which was also leap and with the same special starting on Saturday, which made equipments rejuvenate 18 years in a single New Year's Eve.

- MS Excel mistakenly assumes 1900 is a leap year.

- The floating point arithmetic in Excel 2007 is in jeopardy due to miscalculations

- The largest software company lost customer data in October 2009 because of errors in its server applications in the cloud.

- A world leader in digital security had problems in January 2010 with the recognition system of bank cards and knocked out service to millions of German users.

- If you think the list ends there, a candidate known for the near future are all computers with 32 bits UNIX operating systems, or based on the C language, because they'll stop working on January 19, 2038. It's called Year 2038 problem (Y2K38) than fall back on a journey back in time to his start on 1 January 1970.

- Collection of other software bugs here.

After this short list my dear reader will be thinking that the latest versions of the products are error free because last software projects are making better. This isn't true!, each modification involves an undetermined number of errors, and drag those who already had previous versions and generate overlap and collateral new errors.

Returning to the spreadsheet application par EXCELence. Still generates rounding errors in floating point arithmetic, for example in the formula:

=1*(0,5-0,4-0,1)
which should be equal to 0 gives a value of -2.77555756156289E-17 even in the latest version of Excel 2010, please check:
How to correct rounding errors in floating-point arithmetic

And now comes the list of errors of calculation known to date in the Perpetual Calendar that I posted on this blog

1) The months of January and February 1900 are incorrect because Excel consider incorrectly the first year of their system of dates is a leap year.

2) Use the formula WEEKNUM(reference,type) incorrectly, where type is 2 to calculate the Gregorian Calendar in European countries where such is intended for countries where day 1 is included in the first week of the year, making Monday the first day of the week. In Excel 2010 you can use the type 21 that complies with ISO 8601, which says that the first week of the year is one that includes the first Thursday, so the formula is:
=WEEKNUM(H6,21)

In Excel 2003 and 2007 can substitute by:
=INT((H6-DATE(YEAR(H6-WEEKDAY(H6-1)+4),1,3)+WEEKDAY(DATE(YEAR(H6-WEEKDAY(H6-1)+4),1,3))+5)/7)

3) When two or more events coincide on the same day only one of them are colored by that date.

4) The algorithm for calculating the Easter Sunday, according to the Gregorian calendar for the churches of the West, is valid until the year 4099 and can not be extrapolated to 9999 (check here: Easter Algorithm for a Computer Program)

The first error is complex to solve because is internal to Excel and affects only two months of the (9999-1899) * 12 = 97,200 months can be viewed with the Perpetual Calendar.

The second error is corrected when you update and uploading new versions of the calendars.

The third error involves generating many more conditional formats of those already there, or use a range of colors that are permitted only in Excel 2010, which would still unsolved for Excel 2007.

The fourth error is significant because of the 9999-1899 = 8100 years, it fails in 9999-4099 = 5900 years, 72% of years, but there is still time to fix it...

Compiling the list of calendars published on this blog so far:

If you find any calculation error, you could comment me or shut up forever!

Further comment on methods of software quality control to minimize calculation errors. Leave "perfectionists" to completely eliminate errors.

Definition of perfectionism.
1. m. Tendency to improve work indefinitely without deciding to consider it finished.