Welcome to InsuranceForums.info!   

Advertisments:


Sponsor Links

Affordable Pet Insurance
Travel Insurance Comparison


Is Excel Drunk? It Says 1-1=-1.82e-11?

Discussion forum for Insurance Agents

Is Excel Drunk? It Says 1-1=-1.82e-11?

Postby Inness » Wed Oct 04, 2017 4:19 pm

This is just info so that my question makes more sense.

I have data in five columns, tracking amounts owed by people

Column

A - Yes or No

B - Name

C - Sub-total

D - Tax

E - Total

In column F I have a list of the different names, and beside each name, in column G, I have the function, =SUMIFS(E:E,B:B,FX,A:A,"No"), So that I can track the total owed by each person.

I also have, in H1 =SUM(E:E), to track overall income

and in H2 =SUMIF(A:A,"Yes",E:E)

In cell H3, I did, =H1-H2-SUM(G:G), just to make sure that I didn't forget someones name.

QUESTION:

So cell H3 should be zero right, but no, it is some insanely small number, -1.82E-11.
I mean, the logic makes sense that since all numbers are calculated using the same data point, in order to arrive at the same number, H3 should be zero. Can anyone please explain this? Thank you VERY VERY much, in advance.
Inness
 
Posts: 44
Joined: Tue Apr 08, 2014 12:26 am

Is Excel Drunk? It Says 1-1=-1.82e-11?

Postby Andrian » Thu Oct 05, 2017 2:28 am

Floating point numbers are approximations to the real numbers.
This is the same problem in any language or utility.

The tax rate you apply is exact to some 1 to 3 significant figures, and the floating point representation is 15 or 16 sig figs, and comes as close as it can.

Same for amount of tax, and then line item total.

If you want exact, fill tax with:

int( Sub_total * tax_rate * 100 + 0.5 ) / 100 (assuming you use cents and not mills)

... and even this is going to be "really close but not quite right".

The only way to be dead accurate is to work in cents (or mills).
Andrian
 
Posts: 45
Joined: Sat Feb 15, 2014 8:52 pm

Is Excel Drunk? It Says 1-1=-1.82e-11?

Postby Seraphim » Thu Oct 05, 2017 11:15 pm

When it comes to the results of calculations, Excel keeps track of the actual result values (which might involve the floating point math described by others) but uses cell formatting to show you more visually-appealing results. Depending on your formulas, you very well might have calculated 1.0000000000182 somehow. In fact, entering that number in an Excel cell will show just "1" using the default format, while you will see the actual value in the formula bar.

You don't mention what formulas or values you use in columns A through E (especially D, whose values might be the source of your trouble).

You can use the Evaluate Formula command to see how Excel arrives at a particular result.
Seraphim
 
Posts: 64
Joined: Thu Jan 16, 2014 8:49 am

Is Excel Drunk? It Says 1-1=-1.82e-11?

Postby Axton » Sat Oct 07, 2017 8:32 pm

Nope!!.
Axton
 
Posts: 51
Joined: Sun Mar 30, 2014 7:44 am

Is Excel Drunk? It Says 1-1=-1.82e-11?

Postby Emmett » Thu Oct 12, 2017 12:16 am

Floating point numbers cannot be represented exactly in binary computation. Everything is an approximation. Standard IEEE-754 guarantees that some operations will be guaranteed exact, but others cannot be. You hit one of the non-guaranteed ones.

Note that this is not a problem with Excel or Windows or Microsoft - it is a problem built into mathematics. There is no solution. You have to write your formulae so they expect (and then ignore) very small differences.
Emmett
 
Posts: 34
Joined: Tue Apr 15, 2014 7:12 pm


Return to Insurance Agents

 


  • Related topics
    Replies
    Views
    Last post