Login required to started new threads

Login required to post replies

excel help - strange behaviour...
Quote | Reply
Trying to format a CSV file for import into Great Plains General Ledger, in order to do so the sum of the "amount" column must equal exactly zero.

however, the sum of the column is 0.000000000944.....

in the column of numbers there are no amounts which go beyond the second decimal place. the problem remains if I use the round() function.

if I multiply the cell contents by 100 the issue disappears. if I then divide the multiplied cells by 100 to return to the original values the issue returns.

any ideas?

Swimming Workout of the Day:

Favourite Swim Sets:

2020 National Masters Champion - M50-54 - 50m Butterfly
Quote Reply
Re: excel help - strange behaviour... [JasoninHalifax] [ In reply to ]
Quote | Reply
JasoninHalifax wrote:

any ideas?

Sign up for a remedial Excel course.

How does Danny Hart sit down with balls that big?
Quote Reply
Re: excel help - strange behaviour... [JasoninHalifax] [ In reply to ]
Quote | Reply
How are the cells formatted?

Change the formatting on the cells in question to General, and you should be able to see any extended decimal places.

Try formatting the cells as Currency, that appears to do the necessary rounding.

1.231239 2.123817 -3.36 -0.00494
$1.23 $2.12 -$3.36 $0.00
Quote Reply
Re: excel help - strange behaviour... [JasoninHalifax] [ In reply to ]
Quote | Reply
TRUNC(target_cell,2)

"The right to party is a battle we have fought, but we'll surrender and go Amish... NOT!" -Wayne Campbell
Quote Reply
Re: excel help - strange behaviour... [efernand] [ In reply to ]
Quote | Reply
It's not a cell formatting issue. I can see what is in each cell, nothing goes beyond 2 decimals

Spreadsheet has about 1200 rows, so it isn't huge, but too much to input manually.

Trunc() function didn't work. Sum of the column is 9.4434E-10

Swimming Workout of the Day:

Favourite Swim Sets:

2020 National Masters Champion - M50-54 - 50m Butterfly
Quote Reply
Re: excel help - strange behaviour... [burnman] [ In reply to ]
Quote | Reply
burnman wrote:
TRUNC(target_cell,2)

you may still have to plug this if after this you end up with a real rounding variance eh?
Quote Reply
Re: excel help - strange behaviour... [JasoninHalifax] [ In reply to ]
Quote | Reply
It's a known problem with Excel and really any floating point math on computers. The numbers aren't represented exactly as you'd expect.

Formatting the cells (including the sum) as Currency, should "show" you what you expect, even if internally, Excel has fudged the math by tiny amounts.

As you've seen,multiplying by 100 and treating everything as integers works, because computers are great at integer math.
Quote Reply
Re: excel help - strange behaviour... [JasoninHalifax] [ In reply to ]
Quote | Reply
You could use "=ROUND(cell,2)"

Edit: posted too fast - formatting as currency as efernand says should help.
Last edited by: riotgear: May 19, 17 7:59
Quote Reply
Re: excel help - strange behaviour... [SailorSam] [ In reply to ]
Quote | Reply
SailorSam wrote:
burnman wrote:
TRUNC(target_cell,2)

you may still have to plug this if after this you end up with a real rounding variance eh?

If you perform arithmetic operations on the figures subsequent to this, then yes, you will have the same floating point issue. As a terminal step, though, it should suffice.

"The right to party is a battle we have fought, but we'll surrender and go Amish... NOT!" -Wayne Campbell
Quote Reply
Re: excel help - strange behaviour... [JasoninHalifax] [ In reply to ]
Quote | Reply
Assuming the numbers in question are in column A, then in column B put in the following equation: =(INT($A1*100))/100
The sum should equal zero... unless someone at Great Plains was doing some creative accounting.

Remember - It's important to be comfortable in your own skin... because it turns out society frowns on wearing other people's
Quote Reply