OzSTOC
No Parking Zone! => Off Topic, Off Colour, and non-motorcycle related => Topic started by: atoyot on August 28, 2015, 10:01:15 AM
-
A computer once beat me at chess, but it was no match for me at kick boxing. :CB--
-
I can relate to that.
This computer has recently started coming up with creative calculations in a business spreadsheet I depend on. How the heck it does that I have NO IDEA! But if I take the spreadsheet over to my laptop, it gets the calculations right. Go figure. :o
I don't dare take it on in kick boxing because it still does everything else the way it should. :CB--
-
Bill, it's probably something to do with the way Excel (more than likely?) is setup to handle fractions or whatever form they might have...decimal places maybe? Safest way to check is to manually compare the Excell settings on each PC, but there are quicker but more dangerous ways of exporting settings for Excel in some of the relevant registry hives.
-
Are they both the same version of Excel?
-
Are they both the same version of Excel?
Yes.
And it's not accumulated rounding error. This is almost random and illogical. It just gets the cents wrong, but not consistently.
Three worksheets are linked. I'll put say $23.00 into sheets A and B (double entry book-keeping) and the appropriate cell in sheet C (which should cancel out to zero) will read $0.18!
I'll put $23.45 into A and B and C will read -$0.32.
It just crept in over the past couple of months, and it only seems to affect one section of Sheet A (which links to B1, B2 etc).
Spooky, since I've used the same spreadsheet on the same computer with the same OS for 11 years. :'(
-
Have you copied the formula from the working part of the spreadsheet over the offending part. (Copy and right click, past special, formula)
-
Could be the background calculation settings.
-
Have you copied the formula from the working part of the spreadsheet over the offending part. (Copy and right click, past special, formula)
I know copy/special paste/ formula, but I don't know what you're calling "the working part". I'm checked all the formulae thoroughly.
What background calculation settings are there? I know row vs columns.
There is one difference- the desktop still runs XP, the lappy is Win 7.
Still doesn't explain why this spreadsheet has gone feral recently without any other changes.
-
By the working part I mean where the balance comes to zero, or is it not working in the entire worksheet?
-
Under options somewhere, there is a setting to preform calculations across the whole spreadsheet after each entry (in the background) or not.
-
Hi Bill,
Are they both 64 bit version of Windows? Not many people used XP 64 bit, so it *might* be something to do with that....maybe?
-
By the working part I mean where the balance comes to zero, or is it not working in the entire worksheet?
Thanks Gary. No, it's only that cell that misbehaves. Other related cells do the right thing all the time..
I have the whole sheet updating with every entry.
No Adrian. Both computers are 32 bit.
See why it weirds me out? :crazy
-
Yep. You've exhausted my knowledge without looking at it. It is confusing.
Cheers,
Gary
Sent using Tapatalk
-
This might be drawing a bit of a long bow, but are the processors in both computers Intel or AMD? Still doesn't really explain anything as even if they are different, you'd think that the way they'd handle co-processor calculations would be consistent between processors. Even so, the errors seem to be random with no particular pattern. Microslop wouldn't entertain a support call, as XP has been sunset a while ago now, but that's probably what they'd put it down to. If the old one could be upgraded to Win 7 +, that might go some way to solving it, but you probably, like so many, just want to leave it on XP, not withstanding the costs of an upgrade licence.
It's probably not worth chasing it too hard, as it does sound to be very random, and I assume that a repetition of conditions causes a different result each time? What about a new blank workbook? Same thing? Anyway, you probably didn't mention this situation to ask for help resolving it, so you might need to just go and kick box with something that you can't rely on too much. The nearest politician maybe?
-
Forgot to suggest that you might try putting the C sheet in another workbook?
-
Since the calcs have been working consistently in the past another possabillytea is infection from an excel macro virus. Never seen one myself as I wrote my own calc code and avoided excel, but heard of them being a problem. Bill, if u haven't got it I think the free version of AVG Antivirus checks 4 macro viruses.
Here's an article on macro viruses:
http://www.howtogeek.com/171993/macros-explained-why-microsoft-office-files-can-be-dangerous/ (http://www.howtogeek.com/171993/macros-explained-why-microsoft-office-files-can-be-dangerous/)
-
Since the calcs have been working consistently in the past another possabillytea is infection from an excel macro virus. Never seen one myself as I wrote my own calc code and avoided excel, but heard of them being a problem. Bill, if u haven't got it I think the free version of AVG Antivirus checks 4 macro viruses.
Here's an article on macro viruses:
[url]http://www.howtogeek.com/171993/macros-explained-why-microsoft-office-files-can-be-dangerous/[/url] ([url]http://www.howtogeek.com/171993/macros-explained-why-microsoft-office-files-can-be-dangerous/[/url])
That's a thought.
I have eSet Smart Security, a paid-for virus checker/firewall, which is pretty bullet-proof.
-
Forgot to suggest that you might try putting the C sheet in another workbook?
Hadn't tried that.
I use a continuously updated template from which I produce each month's workbook. To avoid an infected copy I used last December's workbook and purged it of data and set it up for August 2015 (had to incorporate the intervening changes- tiresome- spent a night on it). It looked like I'd beaten the bug after I entered the data that had caused the grief, then blow-me-down, the next data in that area went ape. That's when Missus Biggles suggested trying it on the laptop (to hose down my tantrum).