why pivot sum of isn't right?

[abfabphxboi]

I have 1 very simple table, and a very simple pivot table, but for some reason, the 'sum value of shares' pivot isn't calculating correctly on one of the cells (LAND)?

Please see attached excel.

I am keeping track of stocks. One of the tickers (LAND), I bought on 3 occasions, and sold on 3 occasions. To the point where I no longer have any shares.

My pivot table shows the amount I wholly paid/sold (sum of paid) for each ticker, and then the amount of shares outstanding (sum of shares) for each ticker

If you do the math...I have sold 100% of my 'LAND' shares, but the pivot table calculates that I have still have 1.11438....E-14 shares of the stock.

Why is it not calculating out to be ZERO?

I am very confused as to why this is.

[martindwilson]

1.11438....E-14 is more or less zero as it is very very small its to do with precision in excel
you need to round your calculations mind you i cant see that in your pivot where is it showing?

[abfabphxboi]

I certainly understand your reply. However, you cannot round in a pivot table. You cannot enter in a ROUNDUP or DOWN formula with a pivot table, you may only change the decimal places...on the surface, it will say 0.0, but deep down in the heart and soul of that cell, it will still be 1.11438...E-14.

I am not sure what the issue is. But the correct calculation is ZERO, but the pivot table is saying its 1.1143...E-14

[abfabphxboi]

1.11438....E-14 is more or less zero as it is very very small its to do with precision in excel
you need to round your calculations mind you i cant see that in your pivot where is it showing?

Sorry, it is showing at J5. Even though it shows 0.0000 if you look up at the formula area, it will say the exact number, which is the 1.11....E-14

[martindwilson]

having said that sumif gives the same result
=SUMIF(B4:B21,"land",C4:C21)=1.11439E-14
but subtotal when filter applied to column b gives 0
i think its the way it calculates some of those -ve decimals something to do with floating point precision

youll just have to accept thats the way excel calculates things,afaik ther is no fix
heres an example of another
42655.66-33256.06
returns 9399.60000000001
instead of
9399.6
http://en.wikipedia.org/wiki/Numeric...icrosoft_Excel

[abfabphxboi]

hmm, maybe I just shouldn't use a pivot and create a second table

this seems to work...
=ROUNDDOWN((SUMIF(B4:B21,"land",C4:C21)),1)

the formula will find land, sum it together, then round down to one digit, which will be 0