[Excel] Formulas return incorrect results

Question

Hello,  I have a model sheet created in Excel 2003 with a data tab and a tab with crazy formulas (like 5-6 lines)  I just realized that when opening the same file in Excel 2007 and Excel 2010, the results of the formulas are different. When recalculating manually yesterday, it seemed that 2007 was correct while 2010 was wrong.  This morning I took another sheet and it seems that 2010 is correct and 2007 is wrong.  I'm lost. What do you think?      Configuration: Windows XP / Firefox 14.0.1

Patrice33740 · Accepted Answer

Hello,  Even with crazy formulas, Excel is only rarely wrong!!!  It is much more likely that the context is not rigorously identical or that the formulas are not perfectly suited.  -- Patrice

Patrice33740 · Answer

Re,  I don’t see why the results are different, it may be due to the syntax used.  Try this formula which seems more correct to me: =IF(OR(AI6="–",ISERROR(AVERAGE(OFFSET(Date_debut_exploit,($AI6-Date_debut_exploit)/pas_exploit,1,1/pas_exploit))*24)),"–",AVERAGE(OFFSET(Date_debut_exploit,($AI6-Date_debut_exploit)/pas_exploit,1,1/pas_exploit))*24)  Can the value of "pas_exploit" be greater than 1? If so, the formula is oddly formulated! -- Best regards Patrice

mihego32 · Answer

Naivety in believing that Excel does not make mistakes or that one can always rely on an Excel spreadsheet: a taxpayer boasted about deceiving the administration by presenting spreadsheets to support his declaration; the only issue was that his boast had been published in magazines and his totals were manipulated.  Well! On the other hand, I confirm that EXCEL no longer knows how to calculate correctly. With Office 2010, I have 7.33 in A1, 6.95 in A2, 0.31 in A3, and 0.07 in A4. With formula =A1-A2-A3-A4 in A5, the incorrect result is -1.11022E-16. Absurd.  I entered the same values in column B but with negative values for B2 to B4, then I summed them with =SUM(B1:B4): same incorrect result, and it's striking. Then in C5, I typed "=7.33-6.95-0.31-0.07": also incorrect.  My Texas TI-1766 calculator from the early 1970s gives me the only correct result, which is 0 or ZERO, which any child capable of calculating will achieve, and he will rightly tell you that EXCEL no longer knows how to count. Inadmissible for MS, not for the child.  Another example: - in C3, I typed 108.49 (2 decimal places and calculation accuracy according to display) and in I3 I have "=C3-100". Simple, right? Not for Bill MS-Mismatch-ErrorMaker, because when I increase the number of decimal places, I read: 8.4899999999999000. Who can be trusted? I will look into Libre Office, and if it does not make the same stupid error, I will abandon MS Office, to which I had been loyal since 1989!

mihego32 · Answer

Hello,  Generally speaking, having used Excel since its early versions, as well as other similar programs like Visicalc, SuperCalc, etc., I don't recall encountering such basic errors. But given my age (68 years) and my long experience in computing and software development (over 40 years), it may be likely in your eyes that my memory glorifies the past.  > eriiic Thank you for responding, preferably sticking to the topic "[Excel] Formulas give incorrect results" and not, as per your imagination, "different results on different Excel versions." Touché!  > Patrice 33740 additionally Then explain to me the usefulness of the option "Set calculation with precision as displayed." If the binary calculation actually stores values different from an displayed zero, how could one test a value equal to zero for conditional formatting, among others? Even in old Fortran, I could choose variables as floating-point or in fixed decimal format.  Correction: MEA CULPA: after several tests, the problem seems to stem from the fact that in practice, Excel retains the "General" format even for cells that contain only numeric values, and thus displays the result as "-1.11022E-16". As soon as we choose the "Number" format with the appropriate number of decimal places, the result displays from "0" to "0.000000000000000".  Perhaps an option allows Excel to automatically choose a format suitable for the content (in the absence of deliberate choice) rather than remaining in "General" format?  Have a nice day,

Patrice33740 · Answer

Hello,  “Perhaps there is an option that allows Excel to automatically choose a format suited to the content (in the absence of a deliberate choice) rather than remaining in 'Standard' format?”  You can modify the Normal Style (for the entire workbook).  -- Best regards Patrice

[Excel] Formulas return incorrect results

5 answers

Waiting for a fnac delivery for 2 months

Car gps tracker

What is your experience with android tablets abor

Disable office

Comaps on play store

I would like to know how to report a scam

Reinstallation of w11 impossible after pc crash...

Site unreachable

Nonexistent sent items folder

Transfer mp3 from pc to mobile