0

Because of floating point values, I cannot add a string of cells that contain values such as:

 0.08178502
 0.09262585
 0.13261762
 0.13016377
 0.12302067
 0.1136332
 0.12176183
 0.11430552
 0.09971409
 0.125285

Even if I try adding the first two through a sum formula or auto sum through selecting them, excel spits out an error. I have googled this like crazy and tried to change number formats. Is there a function that can allow me to add this information ?

Screenshot:

enter image description here

The spreadsheet is available on my Dropbox.

Jean-François Corbett
  • 37,420
  • 30
  • 139
  • 188
Underdog
  • 9
  • 3
  • actually it wouldn't let me add a direct image as I need to build some credibility but here's my dropbox added to the above – Underdog Sep 13 '14 at 15:42

4 Answers4

3

Those numbers are all preceded by a NBSP (Char Code 160). So, in order to sum them, you have to remove that. Many solutions. Here's one:

=SUMPRODUCT(--SUBSTITUTE(A1:A18,CHAR(160),""))
Ron Rosenfeld
  • 53,870
  • 7
  • 28
  • 60
  • Works but.... 160 is not an ASCII codepoint. [CHAR](http://office.microsoft.com/en-us/excel-help/char-function-HP010342259.aspx) does not take an ASCII codpoint. – Tom Blodget Sep 13 '14 at 21:31
  • 1
    @TomBlodget Do you think that in all discussions we need to explicitly differentiate between ASCII and Extended ASCII? I've always differentiated between ASCII and Unicode extensions, but never felt that it was important to differentiate between the original 7-bit and the Extended ASCII 8-bit coding. – Ron Rosenfeld Sep 13 '14 at 22:35
  • No, there is no need to mention ASCII at all. If Extended ASCII was actually exactly one character set, it would be fine to talk about that but it's not. And, it is sometimes important to differentiate between ANSI character sets. – Tom Blodget Sep 14 '14 at 00:40
  • @TomBlodget Point taken – Ron Rosenfeld Sep 14 '14 at 10:37
1

If a formula like:

=A1+A2+A3+A4+A5+A6+A7+A8+A9+A10

produces:

#VALUE!

then your "numbers" cells contain non-visible characters.

They must be removed before the formula will work.

Gary's Student
  • 95,722
  • 10
  • 59
  • 99
1

If the cells contain text strings and not actual values you will need to convert the text to numeric values before performing any calculations. The function "=value(cell)" will bring the numeric value.

e.g.: A1 contains "000.12345678" (or some other non-numeric presentation of numerals) In cell B1 type: =value(a1) Cell B1 now operates as the real number 0.12345678

John
  • 11
  • 1
-2

Oddly enough, the fact that it said 0.xxxxx in all numbers vs. .xxxxx is what the issue was. I'm just sharing that for folks who google/search and have same issue.

All I had to do was select that whole row and do a search in replace for "0." and make it just "." and now my numbers were usable in equations. For some reason the adjustment of formating as many searches suggested wasn't working

Underdog
  • 9
  • 3