Excel: AutoSum = 0 and manual sum = error
Solved
jds2016
Posted messages
14
Status
Membre
-
jds2016 Posted messages 14 Status Membre -
jds2016 Posted messages 14 Status Membre -
Hello,
I usually have no problem performing sums with Excel.
But now I've opened an Excel file created by someone else, and I can't perform a sum:
- either I do an automatic sum and the result =0
- or I do a manual sum (for example, I write in a cell: =A1+A2+A3) and the error message #VALUE! appears.
I want to specify that these are whole numbers and that the cell format is set to standard. After looking through various forums, I've tried changing the cell format to number, accounting, and currency, but nothing works. I've also tried converting the cells, but that doesn't change anything.
Any solution?
Thanks in advance!!!
I usually have no problem performing sums with Excel.
But now I've opened an Excel file created by someone else, and I can't perform a sum:
- either I do an automatic sum and the result =0
- or I do a manual sum (for example, I write in a cell: =A1+A2+A3) and the error message #VALUE! appears.
I want to specify that these are whole numbers and that the cell format is set to standard. After looking through various forums, I've tried changing the cell format to number, accounting, and currency, but nothing works. I've also tried converting the cells, but that doesn't change anything.
Any solution?
Thanks in advance!!!
5 réponses
Hi,
in your file, the SUPPRESPACE, SUBSTITUTE or REPLACE syntax doesn't work
or with SUPPRESPACE you have to use the CAR(160) syntax
for example in H1 you can paste one of these formulas that you increment downwards
SUBSTITUTE(F1,CAR(160),"")*1
or
=LEFT(F1,LEN(F1)-1)*1
then if you want to replace your values in column F you select your value range in column H and COPY
you select your equivalent range in column F and
PASTE SPECIAL/select VALUES and ok
See you
Mike-31
I am responsible for what I say, not for what you understand...
in your file, the SUPPRESPACE, SUBSTITUTE or REPLACE syntax doesn't work
or with SUPPRESPACE you have to use the CAR(160) syntax
for example in H1 you can paste one of these formulas that you increment downwards
SUBSTITUTE(F1,CAR(160),"")*1
or
=LEFT(F1,LEN(F1)-1)*1
then if you want to replace your values in column F you select your value range in column H and COPY
you select your equivalent range in column F and
PASTE SPECIAL/select VALUES and ok
See you
Mike-31
I am responsible for what I say, not for what you understand...
Hello,
It can be standard but certainly considered as text because there's a space in front or behind your value or even as a separator
start by entering the value 1 in an empty cell and copy this cell, then select your range of values and right-click on your selection/paste special/check multiplication and ok
- or the second method with a 0 and in that case paste special/addition
if that doesn’t work, we would need to check your file
A+
Mike-31
I am responsible for what I say, not for what you understand...
It can be standard but certainly considered as text because there's a space in front or behind your value or even as a separator
start by entering the value 1 in an empty cell and copy this cell, then select your range of values and right-click on your selection/paste special/check multiplication and ok
- or the second method with a 0 and in that case paste special/addition
if that doesn’t work, we would need to check your file
A+
Mike-31
I am responsible for what I say, not for what you understand...
Re,
anonymize your file and post it to us
1) Click on this link https://www.cjoint.com/
2) Click on the Browse button to select your file
3) Click on the Create link button at the bottom of the page
4) After a few seconds, a link will be generated at the top of the page in blue underline, select it and copy it into a reply
--
A+
Mike-31
I am responsible for what I say, not for what you understand...
anonymize your file and post it to us
1) Click on this link https://www.cjoint.com/
2) Click on the Browse button to select your file
3) Click on the Create link button at the bottom of the page
4) After a few seconds, a link will be generated at the top of the page in blue underline, select it and copy it into a reply
--
A+
Mike-31
I am responsible for what I say, not for what you understand...
Thank you!
Here is the link: https://www.cjoint.com/c/GDCltBjOh8G
I am trying to calculate the sum of column F.
Here is the link: https://www.cjoint.com/c/GDCltBjOh8G
I am trying to calculate the sum of column F.
Re,
I notice that your values are on the left which confirms that your values are considered as text, so it's perfectly normal that the SUM syntax returns 0 since it's impossible to add text and #VALUE! if you're using the +
a screenshot doesn't allow for work, I asked you for a piece of the Excel file so for my part without this file I won't go any further in the discussion
See you+
Mike-31
I am responsible for what I say, not for what you understand...
I notice that your values are on the left which confirms that your values are considered as text, so it's perfectly normal that the SUM syntax returns 0 since it's impossible to add text and #VALUE! if you're using the +
a screenshot doesn't allow for work, I asked you for a piece of the Excel file so for my part without this file I won't go any further in the discussion
See you+
Mike-31
I am responsible for what I say, not for what you understand...
Sorry for the delayed response, I just saw your messages!
The formula =LEFT(F1,LEN(F1)-1)*1 doesn’t work but the formula SUBSTITUTE(F1,CHAR(160),"")*1 works!
That’s great, thank you so much!