Weird #NUM! error when using substraction instead of given value (ACOS)

Question

Following formula produces a #NUM! error in excel for Mac:

=ACOS(COS(RADIANS(90-28,6557))*COS(RADIANS(90-28,6557))+SIN(RADIANS(90-28,6557))*SIN(RADIANS(90-28,6557)))

= #NUM!

When using the calculated value of 90-28,6557= 61,3443 it does not.

=ACOS(COS(RADIANS(61,3443))*COS(RADIANS(61,3443))+SIN(RADIANS(61,3443))*SIN(RADIANS(61,3443)))

= 0

What is the explanation for this?

The same formulas used in excel on Windows give 0,0000000149011611938477 as a result for the first equation and 0 for the second one.

Digging deeper:

=COS(RADIANS(90-28,6557))*COS(RADIANS(90-28,6557))

gives:

0,2299635617702540000000

and

=COS(RADIANS(61,3443))*COS(RADIANS(61,3443))

=0,2299635617702550000000

Help me understand where this difference comes from.

Does the explanation lie here somewhere: https://learn.microsoft.com/en-us/office/troubleshoot/excel/floating-point-arithmetic-inaccurate-result ?

Thanks :)

Answer 1

Hi

I'm AnnaThomas and I'd happily help you with your question. In this Forum, we are Microsoft consumers just like yourself.

The issue you are experiencing is due to the fact that Excel uses floating-point arithmetic to perform calculations, which can sometimes lead to inaccuracies in the results. This can be especially problematic when working with trigonometric functions such as ACOS.

In your case, the difference in results between the two formulas is due to the fact that you are using different values for the input of the COS function. The value of 90-28,6557 is not exactly equal to 61,3443 due to floating-point inaccuracies. When you use the calculated value of 61,3443 instead of 90-28,6557 as input for the COS function, you get a more accurate result.

To avoid floating-point inaccuracies in Excel, you can try setting the "Precision as displayed" option before applying a number format to your data. This option forces the value of each number in the worksheet to be at the precision that is displayed on the worksheet.

Another way to avoid floating-point inaccuracies is to use the ROUND function to round your results to a specific number of decimal places. This can help eliminate small differences that may arise due to floating-point inaccuracies.

I hope this helps ;-), let me know if this is contrary to what you need, I would still be helpful to answer more of your questions.

Best Regards,

AnnaThomas

Give back to the community. Help the next person with this problem by indicating whether this answer solved your problem. Click Yes or No at the bottom.

Answer 2

(Resubmitted as reply to TW so that he/she will see it)

Anna wrote:

To avoid floating-point inaccuracies in Excel, you can try setting the "Precision as displayed" option before applying a number format to your data. This option forces the value of each number in the worksheet to be at the precision that is displayed on the worksheet.

Not necessarily. PAD applies only to the final cell value, not to value of expressions in formulas.

So, for example, ISNUMBER(MATCH(90-28.6557,{61.3443},0)) returns FALSE, even though =ISNUMBER(MATCH(A1,A2,0)) returns TRUE,

where A1 is =90-28.6557 and A2 is 61.3443, formatted as Number with 4 decimal places and PAD is set.

In general, I deprecate the setting of PAD for many reasons. That is one of them. The other is: PAD applies to the entire workbook, not just to selected cells. Consequently, it is very risky to set PAD in an existing workbook, especially when there are multiple worksheets, and we overlook unintended side-effects of setting PAD.

Answer 3

PS.... Partial explanation....

Note that 90-28,6557 is 7.105427357601E-15 greater than 61,3443.

So yes, your problems and differences are probably all related to floating-point anomalies.

Just a guess. I would still prefer to look at the actual Excel file.

And the usual work-around applies: explicitly round calculations to the precision that you expect to be accurate -- perhaps 4 decimal places.

But that might problematic for the output from trig functions.

Answer 4

Your initial formula works (?) for me when I copy-and-paste and change comma to period for my environment, to wit:

=ACOS(COS(RADIANS(90-28.6557))*COS(RADIANS(90-28.6557))+SIN(RADIANS(90-28.6557))*SIN(RADIANS(90-28.6557)))

(But that returns 1.49011611938477E-08, not

But obviously, your problem is not about comma vs period decimal point. If I try to use the wrong decimal point, I get a syntax error pop-up, not #NUM.

Have you over-simplified the presentation of the problem?

For example, do you literally type 28,6557 (comma dp) or 90-28,6557?

Or do you really have references to cells that display those values?

---

And yes, the difference between your working formulas (edited) are probably due to floating-point anomalies.

For a dispositive answer, it would be best if you provide a link to an Excel file that displays the #NUM error and the difference working results.

Answer 5

(Resubmitted, removing the request for the file. You seem to have found a good solution with MIN/MAX)

One way to see infinitesimal differences is with formulas of the form =SUM(90 - 28,6557; -61,3443).

=90 - 28,6557 - 61,3443 does not work reliably because sometimes, Excel replaces the actual arithmetic result with exactly zero in order to hide the infinitesimal difference (klunk!).