Extracting nominal ledger data from Sage Line 50 (v7) using MSQuery and transferring the results into an Excel (2000) spreadsheet results in some (but by no means all) zero values having a negative sign in front.
Viewing the data in the query shows that the data is extracted from Sage correctly and all zero values are correctly shown without a negative sign,so the sign appears to be attached during the transfer from MSQuery to Excel. There does not appear to be any pattern to which balances are changed (some balance sheet values and some P&L values).
It is not a formatting issue, the value in each affected cell is actually "-0". Interestingly if you try to type this into a cell Excel changes it to just "0". It is not a huge issue and we have got round it temporarily by changing the query to only return non-zero values but it is disturbing to find your data being changed arbitrarily.
Are we doing something wrong or is this a "feature" of the relationship between Query and Excel? Any ideas?
Nigel Hopkins
Related resources
Replies (6)
Please login or register to join the discussion.
Multiply by 1
I get the same result.(Excel 2000)
I think that in theory, Ctrl-Alt-F9 should force a complete recalculation and reformat the "-0"s correctly - it doesn't.
However, on individual cells, F2 to edit & then [enter] will force a re-format. If you've got many cells, copy a "1" and paste, special, multiply to the whole range.
Intersting solution but......
I'm pleased I'm not alone.
Yes, thanks Ian, I hadn't thoght of "paste, special, multiply" ("divide" works as well by the way).
Interesting that multiplying a "negative number" by a positive number results in a positive number!
However, it remains a solution to a problem which shouldn't exist if the software worked correctly and predictably. Any solutions to the fundamental problem?
Have you checked the number...
I know that you say it is not a formatting problem, but have you checked the number to the full range of decimal places? I have not done the same process that you have done, but I know from using Excel, that I have come across a similar problem and in that case, when the number was expanded in the number of decimal places, it showed e.g. -0.000000123
Interesting though, as I am emarking on a Sage Line 100 installation. Let me know what the answer is.
Yes, the number is literally "-0"
Andrew,
Yes the number is literally "-0", not "-0.0000000123" or anything similar. No decimals at all, just the two characters "-" and "0".
I know what you mean though since I've come accross the bizarre situation in Excel where if you add a series of numbers, all with the same number of decimal places, in two different ways (e.g. if you want to create a check total for a series of sub totals) and subtract one total from the other you sometimes get a value which turns out to be something like "1.357E-24"! Presumably another "feature" of Excel? As I said though it appears to me to be a Query/Excel problem rather than a Sage issue so don't hold up your Line 100 implementation on my account!
1.357E-24
Why does MS Excel default in this manner? If you are calculating something that results in a figure with many decimal spaces, Excel always seems to default to 1.357E-24 or something similar.
Usually you just have to resize columns (or cells) to see the true figure.
Does the E-24 relate to some kind of formula, and if so which?
Thanks
Richard
1.357E-24 (or something similar)
Richard,
The "E-24" is used by Excel to represent "ten to the power of -24" i.e. a decimal point followed by 23 zeroes followed by the number (in this case 1.357) which is arguably easier to read than 0.000000000000000000000001357.
Yes, you are correct that resizing the column will eventually show the full number
but
when the calculation should actually result in an answer of 0.00, this is not very helpful. It doesn't usually make any difference to your results (as long as your calculations do not rely on huge numbers of decimal places) but if you want zero values to be suppressed or shown as a dash, because Excel thinks the value is not zero, it treats it as any other number and shows it as 0 instead of a blank or a dash! Very irritating!