Get Machine epsilon in Microsoft Excel

excel epsilon
SSilk picture SSilk · Feb 21, 2012 · Viewed 10.7k times · Source

This seems like a question better directed at those with some programming experience rather than just general Excel users, hence my asking on here as opposed to Superuser.

Is there any way, preferably through a function, to return epsilon (i.e. the smallest non-zero number representable in Excel's calculations)? If it's not retrievable through a function, is there a quick way to calculate it through a compact function?

To be clear, I'm not looking for a VBA-based solution, I'd like an Excel formula/ spreadsheet based solution that does not require running macros.

My searches online have mostly turned up discussions on how to display the symbol Epsilon.

Thanks.

Answer

phoog picture phoog · Feb 21, 2012

Excel always operates on IEEE doubles.

A non-VBA expression that will get you the smallest non-denormalized floating point number greater than 0 is

=2^-1022

Machine epsilon, on the other hand, is the smallest number that can be added to 1 and result in a number that is greater than 1. For double precision it is given by 

=2^-52

Related questions

Is there a way to crack the password on an Excel VBA Project?

I've been asked to update some Excel 2003 macros, but the VBA projects are password protected, and it seems there's a lack of documentation... no-one knows the passwords. Is there a way of removing or cracking the password on a VBA …

Read More
IF statement: how to leave cell blank if condition is false ("" does not work)

I would like to write an IF statement, where the cell is left blank if the condition is FALSE. Note that, if the following formula is entered in C1 (for which the condition is false) for example: =IF(A1=1,B1,"") …

Read More
'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine

I'm trying to get data from an Excel file on a button click event. My connection string is: string connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\source\\SiteCore65\\Individual-Data.xls;Extended Properties=Excel 8.0;"; When I click on the button, …

Read More