Question description:
This user has given permission to use the problem statement for this
blog.
The sum of NUMBERS, not text, add up to zero instead of the correct number.
Solved by D. D. in 20 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
06/07/2018 - 05:26
Hello, I understand that your sum formula is returning a zero result even though you expect something else, right?
Excelchat Expert
06/07/2018 - 05:26
Are you able to share your file so I can analyze it?
User
06/07/2018 - 05:26
I am not able to share it
User
06/07/2018 - 05:27
But yes, that is the problem
User
06/07/2018 - 05:27
The cells are numbers, not text
Excelchat Expert
06/07/2018 - 05:27
Can you create a copy of your file with just the numbers?
Excelchat Expert
06/07/2018 - 05:28
I'm asking because sometimes there are things that aren't obvious but are causing the problem.
User
06/07/2018 - 05:28
I don't think I am able to create a copy. Is there anything I can try to fix this ?
Excelchat Expert
06/07/2018 - 05:29
There are several ways to fix this. But that's what makes this difficult, there's just too many possible reasons.
Excelchat Expert
06/07/2018 - 05:29
Let's start by trying to recreate your data using the document to the right.
Excelchat Expert
06/07/2018 - 05:29
Just show me how your data looks like.
User
06/07/2018 - 05:30
In my doucment, the sum does not add up to 81
Excelchat Expert
06/07/2018 - 05:31
Alright, if that's the case there is a very good chance that your numbers aren't really numbers.
Excelchat Expert
06/07/2018 - 05:31
But they may have been stored as text.
Excelchat Expert
06/07/2018 - 05:31
Without seeing the actual file, I can only speculate.
Excelchat Expert
06/07/2018 - 05:31
Let me try something.
Excelchat Expert
06/07/2018 - 05:31
Are you using Excel?
User
06/07/2018 - 05:31
Yes
User
06/07/2018 - 05:31
I am pretty sure they are all number
Excelchat Expert
06/07/2018 - 05:31
Okay, try this formula:
Excelchat Expert
06/07/2018 - 05:32
They might look like numbers but they may not be.
Excelchat Expert
06/07/2018 - 05:32
I've encountered this personally several times.
Excelchat Expert
06/07/2018 - 05:32
=isnumber(A1)
Excelchat Expert
06/07/2018 - 05:32
Try this formula and make sure that you change A1 to reference one of your numbers.
User
06/07/2018 - 05:33
Okay
User
06/07/2018 - 05:33
It says false
Excelchat Expert
06/07/2018 - 05:33
That's what I'm referring to. It's clearly not a number.
Excelchat Expert
06/07/2018 - 05:33
Are your numbers in the right side of the cell or on the left side?
User
06/07/2018 - 05:33
right
Excelchat Expert
06/07/2018 - 05:34
Okay, please select one of your numbers then change the cell formatting to NUMBER.
Excelchat Expert
06/07/2018 - 05:34
Then try the formula again to see if it results to false still.
User
06/07/2018 - 05:35
It is still false
Excelchat Expert
06/07/2018 - 05:35
That alright, there are still several ways we can do this.
Excelchat Expert
06/07/2018 - 05:35
Let's try something else.
Excelchat Expert
06/07/2018 - 05:36
About how many rows of numbers do you have?
Excelchat Expert
06/07/2018 - 05:36
Still with me?
User
06/07/2018 - 05:36
Yes\
User
06/07/2018 - 05:36
About 10 rows
Excelchat Expert
06/07/2018 - 05:37
Try this formula in the column next to your numbers:
Excelchat Expert
06/07/2018 - 05:37
=A1*1
Excelchat Expert
06/07/2018 - 05:37
Then drag it all the way down.
User
06/07/2018 - 05:37
I think I figured it out
User
06/07/2018 - 05:38
One of the values in the column was a formula that equals zero, but not actually a zero
Excelchat Expert
06/07/2018 - 05:38
I'm sure you did. You just needed to be reassured that your numbers are not being recognized as numbers.
Excelchat Expert
06/07/2018 - 05:39
Yes, this is just one of the examples of how numbers may look like numbers but are not being recognized as one.
User
06/07/2018 - 05:39
Okay. Thank you for your help
Excelchat Expert
06/07/2018 - 05:39
Anyway, I'm glad I was of help.
Excelchat Expert
06/07/2018 - 05:39
Would there be anything else that I can help you with regards to the original question?
User
06/07/2018 - 05:39
No, I think that is all. Thanks.
Excelchat Expert
06/07/2018 - 05:40
Alright, if you have no other questions regarding the original concern, I'd appreciate a 5-star rating and your feedback if you think I deserve it.
Excelchat Expert
06/07/2018 - 05:40
I'll be giving you high ratings as well so the other experts will know that you are a good client and should be taken care of.
Excelchat Expert
06/07/2018 - 05:40
Please do not forget to click the End Session button otherwise I'll be stuck here until the timer expires and be unable to help others. Thank you.
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
