[Solved] Sum is Not Correct

Discuss the spreadsheet application
Post Reply
oskar270
Posts: 30
Joined: Fri Oct 03, 2014 2:13 am
Location: Montreal, Canada

[Solved] Sum is Not Correct

Post by oskar270 »

I try to help a friend with the problem she has on a spreadsheet which is used to track her credit card expenses and she does that for the past 6-8 years. This problem never happened before.

On columnD, rows 8-12 I have these values (as an example) 5,10,15,20,25. In row 13 I type =sum(D8:D12) and I get 65 but it should be 75 (always misses the value in row 9)

Now if I copy the values from rows 8-12 into another column and type the same formula I get 75

I checked the format in all rows and they are formatted as number

Any ideas what is wrong?
Last edited by Hagar Delest on Tue Mar 28, 2017 8:25 am, edited 1 time in total.
Reason: tagged [Solved].
Nicolas
OpenOffice 4.1 on Windows 8.1
User avatar
Hagar Delest
Moderator
Posts: 32627
Joined: Sun Oct 07, 2007 9:07 pm
Location: France

Re: Sum is Not Correct

Post by Hagar Delest »

Can you upload the file by clearing all the content except those cells (see How to attach a document here)?
 Edit: Rory has a point (post below). I thought it was checked when you said that format was number but it's not exactly the same. 
LibreOffice 7.6.2.1 on Xubuntu 23.10 and 7.6.4.1 portable on Windows 10
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Sum is Not Correct

Post by RoryOF »

/View /Value highlighting will show if the cell in row 9 is formatted as text or a number.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
oskar270
Posts: 30
Joined: Fri Oct 03, 2014 2:13 am
Location: Montreal, Canada

Re: Sum is Not Correct

Post by oskar270 »

I will send the file but kind of difficult to find / erase important data. For now I have attached a snip of the sheet in question. You can see in column D, rows 498 to 503 the numbers and the total is wrong. All cells are formatted as number.

Also in column T you can see the copied cells of column D, rows 498 to 503 and the formula which is also shown at the top is correct and shows the correct total
Attachments
TT1_Excel.JPG
Nicolas
OpenOffice 4.1 on Windows 8.1
User avatar
robleyd
Moderator
Posts: 5055
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Sum is Not Correct

Post by robleyd »

You are using MS Excel which makes it hard for many of us to help you as we are mainly OpenOffice users.

See if there is an option on your menu under View called Value Highlighting or similar - if so, toggle that to On and see if the value for Panama - 71.51 - is shown in a different colour from the other values; if so this would indicate that the 71.51 has been entered as text rather than as a number and will have the numerical value 0. Alternatively, if the is no Value Highlighting option, place your cursor in the call containing 71.51 and look at the contents of the input area at the top of the screen - if the contents show as '71.51 [note the leading apostrophe] the contents are text not numerical.
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Sum is Not Correct

Post by FJCC »

Cell D499 is the problem, judging from the size of the error. What happens if you delete the contents of that cell and enter the data again?
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
oskar270
Posts: 30
Joined: Fri Oct 03, 2014 2:13 am
Location: Montreal, Canada

Re: Sum is Not Correct

Post by oskar270 »

I knew that cell D499 was the difference but I had rightclick it and verified the format was number. Now as it is mentioned here I just re typed the 71.51 and the sum became correct. I just dont understand why

So the problem is fixed, thanks to all, however why this happened? Can someone explain the possible reasons?
Nicolas
OpenOffice 4.1 on Windows 8.1
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Sum is Not Correct

Post by FJCC »

Once a cell contains a value, changing the format does not change the value. Spreadsheets will try to interpret the meaning of input, like accepting 2/15 as February 15 in the US, but once the value is accepted it is independent of the cell format. Spreadsheets will also do not raise a warning if the input does not match format. I just tried this experiment in my Excel 2007. I set the format of a cell to be a number with three digits after the decimal. I then entered the letter q, which was accepted, and the format of the cell remained unchanged. I replaced the q with a 6 and the cell displayed 6.000. This acceptance of any input in any cell causes a lot of hard to trace problems in spreadsheets.

Of course, your cell showed something that looked like a number but must have contained something that prevented its being interpreted as one. I tried putting leading and trailing spaces in Excel input but that was still interpreted as a number. If the content was pasted in from a web page it might have included weird invisible characters.
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
User avatar
RusselB
Moderator
Posts: 6646
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Sum is Not Correct

Post by RusselB »

Usually the reason for this is an incorrectly imported column, as can be the case where the originating file is a .csv
I don't know about Excel, but in Open Office, when importing a .csv file to a spreadsheet, there is an option to detect special numbers. For some reason (not a clue as to why) this option is unchecked by default, yet most of the problems that I see regarding numbers not acting properly, is due to the fact that the file was imported with that option unchecked.
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
oskar270
Posts: 30
Joined: Fri Oct 03, 2014 2:13 am
Location: Montreal, Canada

Re: [Solved] Sum is Not Correct

Post by oskar270 »

Thanks to all for the great help
Nicolas
OpenOffice 4.1 on Windows 8.1
Post Reply