Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
Date stamp formula results? Tricky problem? | Excel Discussion (Misc queries) | |||
no formula results in cell | Excel Worksheet Functions | |||
Function Formula is displayed not results | Excel Worksheet Functions | |||
I want the results of a formula to show in cell, NOT THE FORMULA! | Excel Discussion (Misc queries) |