[Excel] Formulas return incorrect results

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,

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

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

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!