[Solved] Sum is Not Correct
[Solved] Sum is Not Correct
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?
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].
Reason: tagged [Solved].
Nicolas
OpenOffice 4.1 on Windows 8.1
OpenOffice 4.1 on Windows 8.1
- Hagar Delest
- Moderator
- Posts: 32627
- Joined: Sun Oct 07, 2007 9:07 pm
- Location: France
Re: Sum is Not Correct
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
Re: Sum is Not Correct
/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
Re: Sum is Not Correct
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
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
Nicolas
OpenOffice 4.1 on Windows 8.1
OpenOffice 4.1 on Windows 8.1
Re: Sum is Not Correct
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.
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
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
Re: Sum is Not Correct
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.
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Re: Sum is Not Correct
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?
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
OpenOffice 4.1 on Windows 8.1
Re: Sum is Not Correct
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.
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.
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Re: Sum is Not Correct
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.
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.
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.
Re: [Solved] Sum is Not Correct
Thanks to all for the great help
Nicolas
OpenOffice 4.1 on Windows 8.1
OpenOffice 4.1 on Windows 8.1