[Solved] Relative addressing in running column formula

[alexopenoffice]

I do know this has been asked and answered however after reading about 5 hours worth of answers none work for my spread sheet, so I will ask again.

Formula in English:
Column d (in current row) = SUM of column d (previous row): column c (current row)

Working Single row examples:
=SUM(d3:c4)
...
=SUM(d99:c100)

Latest try (of many)
=SUM(d$-1:c$1)
which comes back displaying in cell as #NAME? and apparently has been converted to value =SUM(d$-A1:C$1),

Any help in making conversion so I could simply paste the formula from second occurrence of cell D to end of sheet would be welcome.

Thank you for your time and sharing of your expertise,
Alex...

[FJCC]

I think you want

Code: Select all

=D3+C4

[alexopenoffice]

Thank you FJCC.

Is there a way to format so I can cursor out the entire column and paste in so the specific values of D3 and C4 will set as D(current row -1) and C(current row)?

....

[FJCC]

If you paste the formula I posted from D4 to D5, it will adjust to

Code: Select all

=D4+C5

My formula does mean "Previous D row + current C row".

[alexopenoffice]

I took formula =D7+C8 from 8th row and pasted in 9th, 10th, 11th and 12th.

The result in row 8 through 12 are the same. The formula did not adjust per row, it just stays the same?

[FJCC]

Are you copying the formula from the formula bar? That will copy the literal formula. If you copy the cell and paste that, the formula will adjust.

[keme]

On a side note.

When you specify two cell addresses separated by colon, that pair constitutes one range address, not two cell addresses. With a range address, you reference the entire rectangle where the specified cells are in opposite corners.

=SUM(D3:C4) is equivalent to =C3+C4+D3+D4

The SUM() function can take single cell or range as parameter, and will accept more than one parameter input.

What you want is to reference the two single cells. Multiple parameters are separated by semicolon, not colon.

=SUM(D3;C4) is equivalent to =D3+C4

The arithmetic layout is usually easier to read than the SUM() function, but choice is a matter of taste, I guess.

I took formula =D7+C8 from 8th row and pasted in 9th, 10th, 11th and 12th.

The result in row 8 through 12 are the same. The formula did not adjust per row, it just stays the same?

Don't copy the formula from the cell or the formula bar (i.e. don't enter "edit mode" to select the cell formula content). This bypasses "cell context" so it will transfer verbatim when pasting.

Just select the cell and copy that, then paste to new cells. This will preserve cell context for the contained formula, so pasting to other cells will abide by the relative addressing.

[alexopenoffice]

Thank you again.

I can't seem to find the formula bar to pull it but in the worst case I can do it all manually.

Thank you for defining the ; v : which certainly makes sense.

Any ideas how to get an older brother to do his job rather than him not doing it for 20 years before deciding to dump it on his younger brother (smile...)

Thank you again. Your instruction is right to the point and just what I needed,

Alex... New (or actually returning OpenOffice user after many many years,,,)

[keme]

If you need to copy the formula down along an existing list of figures, the fill handle is your friend:

When a cell is selected, the bottom right corner shows a square dot. This dot is the fill handle.

• Drag it to copy content into adjacent cells.
• Double click it to "fill down the table" (continues as far as there is content in the previous column).

[alexopenoffice]

Guess I'm just too old (Smile)

Could you suggest a vid on You-Tube so I can watch it in action?

Thanks, for your help, guidance and patience....
Alex...

[Hagar Delest]

Have you even tried? There are just 2 actions.
In "double-click it", the "it" is for the handle bottom right of the cell (with the cursor being a cross).

[alexopenoffice]

Have a great thanks giving. Thank you again for your patience


Cell highlighted (second formula, double click at cross keeps same value. not running formula on each line???

Amount     Running Bal.
$0.32        $51,501.95
$0.32        $51,502.27
-$1,217.46   $50,284.81    (=D4+C5)  Chose, double clicked corner
-$912.04     $50,284.81    (=D5+C6)
-$315.00     $50,284.81    so formula looks correct but still reports =D4+C5
-$70.00      $50,284.81
-$35.26      $50,284.81
-$28.31      $50,284.81
-$2.85       $50,284.81
-$2.85       $50,284.81
-$2.85       $50,284.81
-$135.00     $50,284.81

[keme]

... formula looks correct but still reports =D4+C5 ...

That behavior usually means that automatic recalculation is disabled.
Enable it by menu selection Tools - Cell content - AutoCalculate

If, for some reason, you don't want automatic recalculation, press function key F9 to manually trigger recalculation.