Get adjacent column value in OpenOffice.org Calc

Question

I'm trying to get the value of adjacent columns in OpenOffice.org Calc.

I have several rows, each with a column that should have its value incremented by one when a column immediately to the right has a value.

So I'm hoping to have something like this.

1 Some content $50
2 More content $320
3 Even More    $70
4              $110

In the above example 1, 2, 3 and 4 are arrived at by incrementing by 1 as there was a value to the right in at least one of two columns.

Answer 1

What's the problem exactly? Is it a matter of referencing the adjacent cells, or of conditionally calculating the values? Regarding relative references, see Eugene's reply. To conditionally insert content, you could combine the IF function together with the CONCATENATE function:

=IF(CONCATENATE(B1;C1) <> "";1;"")

Concatenating results to a string if one of the concatenated cells contain a value.

If the content to insert depends on the row above, you will either have to start the table in the second row, or use a different function from the second row on:

=IF(CONCATENATE(B2;C2) <> "";SUM(A1;1);"")

Next point is what to do if, for example, the eigth row has no values in B8:C8. Just augmenting the above cell's value in A9 will start again from 1:

If you want to continue counting, you will have to count the cells above from the current one that aren't empty or 0. There's a COUNTIF function that does exactly that. So, your function may look like (again starting from row 2):

=IF(CONCATENATE(B2;C2) <> "";SUM(COUNTIF($A$1:A1;">0");1);"")

or, split up on different lines and with comments:

=IF(
    CONCATENATE(B2;C2) <> ""; # Condition to check
    SUM(                      # output if condition matches
        COUNTIF($A$1:A1;">0");# count cells from $A$1 to cell above if not 0
        1                     # augment value by 1
    );
    ""                        # output if condition doesn't match
)

This is the function for the second row; just copy the cell to the next rows. It should give the following result:

Answer 2

Search for "relative references" in the help file of OpenOffice. What you need is a relative reference.

In the first column, first row you have 1 in $A$1. So on the second row ($A$2) you can write "=A1+1". A1 is a relative reference - if you copy the cell (not the formula, but the cell itself) from $A$2 to $A$3, new cell will automatically get the formula "=A2+1".

There can also be absolute references, which "lock" row or column part of the reference. They are accomplished by placing dollar sign before the part. So to lock reference to column A you would write a formula as "=$A1+1", while to lock the formula to the first row, you'd write "=A$1+1"