Adding a carry over digit.

[Quimby]

I want to add a column take the digits greater then the ones column over to the next column and add.

example
[TABLE="width: 500"]
<tbody>[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]
9+6+5=21 carry the 2 to column B and then add. The formula works on two digit numbers, however if column B is a 3 digit number or greater I fail. I have attempted to use LEN to with no success. Please help.

A portion of the data
[TABLE="class: grid, width: 80"]
<tbody>[TR]
[TD="colspan: 2, align: center"]Total[/TD]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[TD="align: center"] column E[/TD]
[TD="align: center"] column F[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]total
107[/TD]
[TD="align: right"]total
399[/TD]
[/TR]
</tbody>[/TABLE]
I want my result to read. 146 for column E and 9 for column F. 107+39 Below is the formula I am using for column E.

=SUM(LOGBOOK!E:E)+(LEFT(SUM(LOGBOOK!F:F),(LEN(SUM(LOGBOOK!F:F)-1))))

Please not that I am adding them on a different sheet, I hope this doesn't over confuse the issue.

Thx for your input.

 

[dreid1011]

What would be the result for col B in your first example? 21?

 

[Quimby]

corrected, thank you for that.

9+6+5=21 carry the 2 for column B and then add to column A. The formula works on two digit numbers, however if column B is a 3 digit number or greater I fail. I have attempted to use LEN to with no success. Please help.

 

[dreid1011]

Ok, so for your 2nd column, you want to show only the 1's place? And the first column will be it's total + the 10's, 100's, etc from the 2nd column?

 

[Quimby]

Correct

 

[dreid1011]

Ok, here is what I came up with and the following formulas.

In the following table:

A17: =SUM(A1:A15)+0+(LEFT(SUM(B1:B15),LEN(SUM(B1:B15))-1))
B17: =0+RIGHT(SUM(B1:B15),1)
A16 and B16 are just the sums of their columns.

<style type="text/css">
table.tableizer-table {
border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif;
font-size: 12px;
}
.tableizer-table td {
padding: 4px;
margin: 3px;
border: 1px solid #ccc;
}
.tableizer-table th {
background-color: #104E8B;
color: #FFF;
font-weight: bold;
}
</style><table class="tableizer-table">
<tr class="tableizer-firstrow"><th></th><th>A</th><th>B</th></tr>
<tr><td>1</td><td>9</td><td>9</td></tr>
<tr><td>2</td><td>5</td><td>8</td></tr>
<tr><td>3</td><td>6</td><td>7</td></tr>
<tr><td>4</td><td>2</td><td>9</td></tr>
<tr><td>5</td><td>4</td><td>8</td></tr>
<tr><td>6</td><td>8</td><td>7</td></tr>
<tr><td>7</td><td>7</td><td>9</td></tr>
<tr><td>8</td><td>2</td><td>8</td></tr>
<tr><td>9</td><td>3</td><td>7</td></tr>
<tr><td>10</td><td>5</td><td>9</td></tr>
<tr><td>11</td><td>6</td><td>8</td></tr>
<tr><td>12</td><td>4</td><td>7</td></tr>
<tr><td>13</td><td>1</td><td>9</td></tr>
<tr><td>14</td><td>5</td><td>8</td></tr>
<tr><td>15</td><td>8</td><td>7</td></tr>
<tr><td>16</td><td>75</td><td>120</td></tr>
<tr><td>17</td><td>87</td><td>0</td></tr>
</table>


Let me know if this works for you.

Upon review, it seems you were on the right track with the formulas. You just missed the 0+ before the LEFT. LEFT returns a string, so you need to convert it back to a number with the 0+.

 

[Quimby]

It is not converting it back. I have a feeling it has to due with the placement of my 0+, however I am not sure at this point. cross-eyed confusion.

For data that I posted I am getting 506 for column E +column F total....

=SUM('LOGBOOK'!E:E)+0+(LEFT(SUM('LOGBOOK'!F:F),(LEN(SUM('LOGBOOK'!F:F)-1))))

 

[dreid1011]

Your 0+ is in the same place mine is. For the sample I provided, it added 12 to the 75 and resulted in 87. That leaves column B with 0.

Oh, I think I see the issue. There is a ")" out of place. See the highlight below:

=SUM('LOGBOOK'!E:E)+0+(LEFT(SUM('LOGBOOK'!F:F),(LEN(SUM('LOGBOOK'!F:F)-1))))

Move that ")" to the left, before the -1.

 

[Quimby]

Genius, it is always the simple things... you rock dreid1011. I really appreciate you staying with me on that one.

 

[dreid1011]

You're quite welcome.