Tech Help

INT21

Antediluvian
Joined
Jul 18, 2016
Messages
7,074
Likes
5,632
Points
279
Does OpenOffice Calc genuinely operate with spreadsheet formulae in the predictable ways that Microsoft Excel manages to?
Not sure, I have only briefly used Excel.

Mytho,


I have MS works (somewhere on a hard drive as well as a genuine CD) but that wouldn't get me to the answer; it will only shift it to a different application.

I'll try and explain it a bit better..
A B C D E
1
8 5 4 5
2 4 7 2 9
3 8 9 5 4
4 5 3 6 2
5 8 4 5 4
6 6 1 4
8 6 2 7
9 7 4 3
10 4 4 8

I wish to create column 'E' .

And the first instruction (after highlighting the cell E1) would be =(B1).

Now , I wish to the contents of cell B3 into E2. That would be (B(1+2)) into E2.

Done manually it is pretty straight forward, but that entails entering the formula into every column E cell; all five of them in this case.

The idea is to increment the value of (B) in steps of 2. Thus extracting every second value in the B coulmn and using this data to create column E.

I have some thousands of data points in columns around 200 cells deep. So really need to automate this.

Hope this sheds some light on things.

INT21.

Just noticed I have missed Row 7.
 
Last edited:

INT21

Antediluvian
Joined
Jul 18, 2016
Messages
7,074
Likes
5,632
Points
279
oxo66,

Hi.

Sadly it didn't work.

There seems to be a few differences in Open Office.

Firstly it uses Semi-colons and not commas to delineate. And the use of the 'String' sign ($) keeps asking for a name. So I assume it is looking for a string of text in the cell.

A B C D
1 20 21 4
2 21 2 17
3 22 15
4 23 18
5 24 9
6 25 4
7 26 16
8 27 14
9 28 15
10 29 14
11 30 17

I wish to create column 'D'

If, when in D1 I use =OFFSET(C1;5;0) I copy cell C6 into D1 i.e. Copied the cell 5 rows below C1

And, moving down to D2, if I use =OFFSET(C1;10;0) I copy Cell C11 into D2. i.e copied the 10th cell below C1 into D2.

All well and good, but I can achieve that using =C6 and =C11. As you say, it is easily done by hand.

But the mathematical bit (A(x)+1)) etc is proving elusive. And the use the 'FILL DOWN' function to carry this on.

INT21
 

oxo66

Junior Acolyte
Joined
Nov 25, 2017
Messages
61
Likes
79
Points
19
oxo66,

Hi.

Sadly it didn't work.

There seems to be a few differences in Open Office.

If, when in D1 I use =OFFSET(C1;5;0) I copy cell C6 into D1 i.e. Copied the cell 5 rows below C1

And, moving down to D2, if I use =OFFSET(C1;10;0) I copy Cell C11 into D2. i.e copied the 10th cell below C1 into D2.

All well and good, but I can achieve that using =C6 and =C11. As you say, it is easily done by hand.

But the mathematical bit (A(x)+1)) etc is proving elusive. And the use the 'FILL DOWN' function to carry this on.

INT21
I have LibreOffice which has near-identical syntax to excel. I did not realise OpenOffice was so distinct.
To my taste, the way to do what you want is to set up an additional column -most conveniently E- containing the offsets you need 5,10,15,20... and proceed as you have above: so OFFSET(C1;E1;0) E1 is 5 , OFFSET(C1;E2;0) E2 is 10 ...

But if you prefer not to have a new column you can use the index in column A and multiply by 5:
OFFSET(C1;A1*5;0) , OFFSET(C1;A2*5;0) , ...

However you use OFFSET, you need an absolute reference to C1 so that it remains C1 when you copy the formula down; in excel and libreoffice this is effected by C$1, I'm surprised that OpenOffice does anything different.

There are other ways to do what you want: you might prefer using ADDRESS(row; column) (so ADDRESS(6; 3) produces "C6" as text; then use INDIRECT(text) to interpret that "C6" as a cell reference.
In D1 you would have INDIRECT(ADDRESS(A1*5+1; 3)) - that should work if you copy it down
 

INT21

Antediluvian
Joined
Jul 18, 2016
Messages
7,074
Likes
5,632
Points
279
Still getting nowhere with this problem.
I have roped in a techy person from the local library and the chef at my local cafe (who is highly recommended) as part of the task force.

I think the possibility oxo66 posted was from Excel (it uses commas and not semicolons) so there may be other differences too.

Does no one here use OpenOffice Calc?
 

INT21

Antediluvian
Joined
Jul 18, 2016
Messages
7,074
Likes
5,632
Points
279
oxo,

Sorry, it happened again. we posted at the same time. Ignore my last post.
 

INT21

Antediluvian
Joined
Jul 18, 2016
Messages
7,074
Likes
5,632
Points
279
Just been watching some video on this.

It seems the OO equivelent of 'effected by C$1' is $C$1. an extra $ before the C

there is a possibility that it simply can't be done in OpenOffice. But I find that hard to believe.
 

INT21

Antediluvian
Joined
Jul 18, 2016
Messages
7,074
Likes
5,632
Points
279
Just done what I should have done at the start; joined the OpenOffice user group.
 

INT21

Antediluvian
Joined
Jul 18, 2016
Messages
7,074
Likes
5,632
Points
279
And here is the result.

If you don't have a column header and data begins in A1: =OFFSET($A$1;10*(ROW()-1);0) In D1, fill the formula down the column.
If you have a column header in Cell A1 and data begins in A2: =OFFSET($A$2;10*(ROW()-2);0) in D2, fill the formula down the column.


So now we know.

oxo, you were pretty close.

INT21.
 

tuco

Devoted Cultist
Joined
Feb 11, 2020
Messages
144
Likes
291
Points
63
Location
south of south
And here is the result.

If you don't have a column header and data begins in A1: =OFFSET($A$1;10*(ROW()-1);0) In D1, fill the formula down the column.
If you have a column header in Cell A1 and data begins in A2: =OFFSET($A$2;10*(ROW()-2);0) in D2, fill the formula down the column.


So now we know.

oxo, you were pretty close.

INT21.
Wow, I wish I knew what that was about, I just click on things and see what happens, that looks like the devils work to me, hats off to you sir
 

INT21

Antediluvian
Joined
Jul 18, 2016
Messages
7,074
Likes
5,632
Points
279
Hats off to the guys and gals at OpenOffice forums who provided the answer in about five hours.

I struggled for hours with those innocuous lines of code.

That is the problem (and the beauty) of computer code; it has to be exact or it won't work.

(Actually, it's quite fun)

INT21.
 

tuco

Devoted Cultist
Joined
Feb 11, 2020
Messages
144
Likes
291
Points
63
Location
south of south
Hats off to the guys and gals at OpenOffice forums who provided the answer in about five hours.

I struggled for hours with those innocuous lines of code.

That is the problem (and the beauty) of computer code; it has to be exact or it won't work.

(Actually, it's quite fun)

INT21.
I'm impressed by all of you, its another language to me !
 

INT21

Antediluvian
Joined
Jul 18, 2016
Messages
7,074
Likes
5,632
Points
279
I'm impressed by all of you, its another language to me !

It's the 21st Century.

To paraphrase someone who's name I forget.

Every modern man has two language. His own and C++.

INT21.
 
Top