sum formula

J

jaypee

good day!
i copied a table from a html and paste to excel2000
i try to get the sum =SUM(B5:B10) but the outcome is 0.when i check the
number on the cell,i found out that theres a space at the end. i try this
formula {=SUM(VALUE(SUBSTITUTE(B5:B10," ","")))}
but the result was #VALUE!.
 
B

Biff

Hi!
i copied a table from a html
Click to expand...

Chances are the "space" is really a char(160)   space.

Try this:

=SUMPRODUCT(1*SUBSTITUTE(B5:B10,CHAR(160),""))

Your best course of action would be to get rid of those characters and then
use a simple =SUM(B5:B10).

I posted this last night:

Here are some things to try:

Select the range of cells that contain the numbers.
Goto Data>Text to Columns
Select Fixed width
Click Next, Next, Finish

If the "spaces" were standard char 32 spaces this should remove them and
convert the values to numbers.

If the "spaces" are char 160 (html nbsp;) T to C won't work.

Select the range of numbers
Goto Edit>Replace
Find what: ALT 0160 (hold down the ALT key and using the numeric keypad type
0160)
Replace with: nothing, leave this empty
Replace All

Biff
 
R

Ragdyer

Since you enclosed you example formula in curly brackets, I assume you
realize that this is an *array* formula.

See if this *array* formula works:

=SUM(VALUE(SUBSTITUTE(B5:B10,CHAR(160),"")))
 
R

Ragdyer

<<<"If the "spaces" are char 160 (html nbsp;) T to C won't work.">>>

TTC *will* work just as well.

Select data, then:
<Data> <Text To Columns> <Delimited> <Next>
Click "other" and in the next box type:
<Alt>0160
(of course, nothing is visible)
Then <Finish>
 
B

Biff

Of course it does. Don't know what I was thinking.

Maybe I was referring to being able to just click straight through.

BTW, I just realized that neither of our formulas/formulae <g> work if there
are empty cells in the range. Then I started tinkering and thinking of
scenarios where some cells may be real numbers without 160's and some cells
may be text without 160's and empty cells and..................argh!

I think the OP should just clean the data range and use a simple =SUM( ).

Biff
 
R

Ragdyer

But didn't you just say:

<<<"Your best course of action would be to get rid of those characters and
then
use a simple =SUM(B5:B10).">>>

Which is really what I thought your whole idea was, in the first
(second?<bg>)place, behind using TTC anyway, which I agree, is the best
approach in this whole, entire thread.<g>
 
J

jaypee

thanks!!!

Ragdyer said:
But didn't you just say:

<<<"Your best course of action would be to get rid of those characters and
then
use a simple =SUM(B5:B10).">>>

Which is really what I thought your whole idea was, in the first
(second?<bg>)place, behind using TTC anyway, which I agree, is the best
approach in this whole, entire thread.<g>

--
Regards,

RD

-------------------------------------------------------------------------- -
Please keep all correspondence within the NewsGroup, so all may benefit !
-------------------------------------------------------------------------- -
benefit
Click to expand...
 
Top