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

Column A	Column B
4	9
3	6
8	5

<tbody>
</tbody>

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

Total
column E	column F
1	6
1	6
1	8
1	7
	4
1	6
1	5
1	7
2	1
2	0
1	8
1	7
1	8
1	8
2	1
1	6
1	8
1	2
2	1
1	4
1	7
1	7
1	7
	9
1	6
1	9
1	9
1	6
1	9
2	0
2	3
3	0
2	1
1	8
1	8
2	1
2	4
2	1
1	9
2	3
2	1
1	8
1	5
1	7
	8
2	2
1	7
1	7
1	6
1	9
2	2
2	1
1	7
1	0
1	1
2	0
2	0
1	6
1	8
1	7
1	6
2	1
1	6
1	9
1	0
2	4
1	7
2	0
1	9
1	5
1	6
1	9
2	3
2	0
1	6
	9
3	4
4	9
2	0
2	4
total 107	total 399

<tbody>
</tbody>

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.