spreadsheets-office 2000

Posted by WDF on December 07, 2001 12:20 PM

Ea column my sheet has a the sum at the bottom of the column(by autosum). When checking the sum of each column by hand, the autosum is incorrect. I cannot seem to find the error. Also checking the math across the sheet I find errors in the totals. I have formatted several cells with formulas and they seem to work ok but their accurracy is off slightly by dollars and cents.
Please advise

Posted by Mark W. on December 07, 2001 12:23 PM

Did you check for hidden rows?

Posted by Mark W. on December 07, 2001 12:29 PM

Actually, it'd have to be a row with a very small heigth

Posted by Tom Urtis on December 07, 2001 12:32 PM

Maybe you've selected Precision as Displayed? Or cells are calculating with more decimal places than you are manually checking for?

Posted by jack on December 07, 2001 2:58 PM

Re: Actually, it'd have to be a row with a very small heigth

This sounds formiluar... its not off by $1 or 1cent is it?

Or have you copied down data by the fill handle and latter added rows??
Poss can cure this one!

Have you loaded Excel 2000 from cold blank install?
Or wa it upgrade from Exel 97?
Did you apply patch SR1 to Excel97?

Posted by WDF on December 08, 2001 4:28 PM

did not select precision as displayed, cells as best I can tell are formated to two decimal places. I checked the sum of one column to see how far it is off. this column has 95 cells @ 9.25 ea. the total reads 878.63, should be 878.75.
I do suspect this may be a decimal problem though
just need to know how to set the sheet up to ensure precision. the office 2000 came loaded on the puter not an upgrade, don,t think I have any hidden cells ?

Posted by Tom Urtis on December 08, 2001 5:03 PM

This is not a hidden rows problem, because your Sum function will sum the range whether rows are hidden or not, unless there is some code in your application that only allows for visible cells to be summed.

It sounds as if you are only off by a few cents, and depending on the number of rows you have, this might be due to decimal settings in different cells, where some cells are calculated based on (for example) 4 decimal places, and others for 2 decimal places. This can happen with cells containing imported data that arrived in a 4 decimal format but are being displayed as 2 decimals. Remember, formatting only changes the way a value is displayed; it leaves the actual number intact.

The Precision As Displayed feature is adjusted by clicking on Tools > Options > Calculation tab, and selecting "Precision as displayed". You will see a warning box that data will lose accuracy, indicating the trade-off between displayed perceived accuracy versus actual accuracy in calculations.

Please do not treat my ideas here as recommending what you should do...the choice is yours and you are in the best position to know what's right. There may well be a totally different cause and it's quite possible that I am 'way wrong with my guess, so keep an open mind when examining your file, as you have been doing.

I'd be interested to know what the answer is, if you ever do find out, so please repost and help us learn too. One thing about this stuff, there's always something new to learn.

Good luck.

Tom Urtis

: Maybe you've selected Precision as Displayed? Or cells are calculating with more decimal places than you are manually checking for? :

Posted by Jack on December 09, 2001 11:43 AM

: Maybe you've selected Precision as Displayed? Or cells are calculating with more decimal places than you are manually checking for? :

To see if you have hidden rows look to see if a number of a row is missing, then its hidden, you can not really remove a row the 1 2 3 4 5 always follows regardless, cut a row and the data shuffels up

could be hard ctrl-A
Right click
see unhide if can choose then yes rows col hidden can be made un hidden

HTH