SUM problem: adding formula results
 

I had several thousand cells with "0 true", "1 true", "1 false", and "0
false".

I finally removed all text using the LEFT function, leaving just 0 or 1
in each cell. I need to sum the rows now, and of course I couldn't do it
right away, I figured that I could "paste special", then check "values".


Here's the problem: I still can't calculate a sum. The 1's and 0's are
on the left side of the cell. If I simply type a 1 or 0 in the cell,
replacing the 1 or 0 already there, it shows up on the right of the
cell, and then it will sum. But obviously, I don't want to do this to
thousands of cells, I need to quickly convert these LEFT formula
results to values that will sum, but "paste special", "values" won't
work.

Thanks!


--
JPN5804
------------------------------------------------------------------------
JPN5804's Profile: http://www.excelforum.com/member.php...o&userid=29137
View this thread: http://www.excelforum.com/showthread...hreadid=488557

SUM problem: adding formula results
 

Hi!

The value returned by the LEFT formula is TEXT.

Either:

=LEFT(A1,1)*1
=--LEFT(A1,1)

Will convert the returned values to numeric numbers.

If you have already converted the formulas to constants by doing a
copy/paste special/values, try this:

Select an empty cell.
Goto EditCopy
Select the range of "numbers" in question.
Goto EditPaste SpecialAddOK

That should convert them into numeric numbers.

Biff

"JPN5804" wrote in
message ...

I had several thousand cells with "0 true", "1 true", "1 false", and "0
false".

I finally removed all text using the LEFT function, leaving just 0 or 1
in each cell. I need to sum the rows now, and of course I couldn't do it
right away, I figured that I could "paste special", then check "values".


Here's the problem: I still can't calculate a sum. The 1's and 0's are
on the left side of the cell. If I simply type a 1 or 0 in the cell,
replacing the 1 or 0 already there, it shows up on the right of the
cell, and then it will sum. But obviously, I don't want to do this to
thousands of cells, I need to quickly convert these LEFT formula
results to values that will sum, but "paste special", "values" won't
work.

Thanks!


--
JPN5804
------------------------------------------------------------------------
JPN5804's Profile:
http://www.excelforum.com/member.php...o&userid=29137
View this thread: http://www.excelforum.com/showthread...hreadid=488557

SUM problem: adding formula results
 

Thanks, paste special, add worked. I did not think that the formula
would yield a TEXT value.


--
JPN5804
------------------------------------------------------------------------
JPN5804's Profile: http://www.excelforum.com/member.php...o&userid=29137
View this thread: http://www.excelforum.com/showthread...hreadid=488557