Formula can't read this "-1.86517468137026E-14"(Scientific) as "0" (Numeric)

[ankuun]

Hello everyone,

My pivot table returns zeros in -1.86517468137026E-14, 1.46549439250521E-13, -2.28261853862932E-13 format and I need my formulas to read this as zeros. I changed the output numbers to accounting, numeric but it didn't work.

Example;
Pivot output
A1: -1.86517468137026E-14
A2: 1.46549439250521E-13
A3: -2.28261853862932E-13

Formula in
E1 refers to A1xA3: IF(A1=0,TRUE,FALSE)

I cant get "True" out of this formula. As I said befor, I changed the output of the pivot table to numbers (displays zero) but it still doesn't work in this formula.

Thank you so much in advance!

 

[Sajan]

Hello ankuun,
You will not get A1=0 to be TRUE since the value in A1 is not-zero. Is there a range of values you want to consider as zero?

You can map the values you want to treat as zeros using a formula like the following:
=LOOKUP(A1,{-5,1},{0,1})

The above formula will return a zero for all values between -5 and 1, but return 1 for anything greater or equal to 1.

Cheers,
Sajan.

 

[NARAYANK991]

Hi ,

You might want to read this :

http://randomascii.wordpress.com/2012/02/25/comparing-floating-point-numbers-2012-edition/

The point is that when you are dealing with floating-point numbers in general , and very small numbers in particular , never check for an exact equality , whether it is 0 or any other number ; always use what is called epsilon or an error-limit value.

Thus when you are checking whether a number equals 2 , what you really want to see is whether the number is close enough to 2 that it makes no difference ; use a check such as :

=IF(AND(number>=2-epsilon,number<=2+epsilon),"equal to 2","not equal to 2")

epsilon is a very small number , say 0.000001

Narayan

 

[ankuun]

Sajan;
Good idea! I will try that but returning 1 for anything greater or equal to 1 might be a problem.

Narayank;
Its good to hear from you again! Your answers are always awesome! Good article and nice suggestion.

 

[Sajan]

Hello ankuun,
In the LOOKUP, you can change the return value to be something other than 1, as well as the threshold to something else.

For example,
=LOOKUP(A1,{-5,25},{0,10})
will return 10 for any value greater or equal to 25.
Adjust to suit your needs.

Cheers,
Sajan.