• We have updated the guidelines regarding posting political content: please see the stickied thread on Website Issues.

Tech Help (Beyond This Forum)

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:
oxo66,

Just seen your post. I'll study and get back.

Thanks.
 
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,

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
 
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?
 
oxo,

Sorry, it happened again. we posted at the same time. Ignore my last post.
 
Trying you new suggestion now.
 
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.
 
Just done what I should have done at the start; joined the OpenOffice user group.
 
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.
 
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
 
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.
 
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 !
 
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.
 
No. Are you quite bonkers? ;)

Probably.

But I liked C/PM (as used back on my Amstrad PCW8256) as it allowed one to format disks with sector sizes that were appropriate to the data you were using.

There is a lot of waste with the more modern large sectors if you are only storing small data items
 
Thanks, that is a load of useful information.

I'm not intending to try install C/PM on anything at the moment, just collecting info.
 
Thanks, that is a load of useful information.

I'm not intending to try install C/PM on anything at the moment, just collecting info.
Yeah, once I'd stopped sniggering (used to have an CPC6128 myself, running both Pascal and Fortran compilers), I thought "There'll be a CPM emulator that runs on a PC somewhere..."

http://www.sydneysmith.com/wordpress/cpm-programs/

This site is the tip of a very large retroberg.
 
Int21, if you just want to try out an operating system virtualbox is usually a good solution, it lets you create a virtual computer inside your usual operating system and install an os in it. It can be useful if you say want to run Linux inside Windows or want to try out Plan 9 or something of that nature.
 
Yeah, once I'd stopped sniggering (used to have an CPC6128 myself, running both Pascal and Fortran compilers), I thought "There'll be a CPM emulator that runs on a PC somewhere..."

http://www.sydneysmith.com/wordpress/cpm-programs/

This site is the tip of a very large retroberg.

Just looked inside a 'Cashconverters' bag to find a 'ZX Spectrum +' and a '128 K Z Spectrum +2'. both with 'Spare or repair' stickers on them; £ 2 each.

Must get around to doing something with the one day. And also with the two PPC640 portables I have. Remember them ? I've seen people passing through airports with smaller baggage.

Anyway, thanks all for the input on this.

INT21.
 
On top of it all my computer died monday, replacement coming in a few weeks. Cant type great on the phone. Techies, is there any way to link a keyboard to my android phone?
 
On top of it all my computer died monday, replacement coming in a few weeks. Cant type great on the phone. Techies, is there any way to link a keyboard to my android phone?

There are keyboard apps you can obtain and install to supplement or replace your phone's built-in pseudo-keyboard.

If you're wanting an outboard / physical keyboard device ... There are such things as compact Bluetooth keyboards that can interface / interoperate with Android phones. Here's a link to an Amazon webpage listing such things:

https://www.amazon.com/slp/smartphone-keyboard/kckrt34sfmkc7up
 
Ethernet wall sockets. I attach my laptop via ethernet cable directly to the LAN port on my router, and it works. I try this with another cable, and the second also works. However, if I then run one of these known-working cables from my router to the ethernet wall socket, and the other from another socket in the house to my laptop, I have no connection. Ergo, it's a problem with the sockets, right? I have unscrewed the cover plate on both and checked the wiring: one pair of wires was indeed crossed over, so I rectified that. But the damn arrangement still doesn't work, and I am at a loss. Is there anything obvious I have missed in my trouble-shooting? What would be the next logical step to take? Is there any possibility at all that I need to change a setting in the router (Mi wifi gen 4)?

I can't really just rely on the wi-fi signal: the wireless signal is unreliable, whereas I need to have a rock-steady connection for my online classes, on which rely not only my income but also my ability to stay in this country. Also, I can't easily have long cables snaking down the corridor (I am living with a toddler and a senior citizen with poor eyesight). And given who is sleeping in which room, I can't move my workspace closer to the router itself (or vice-versa, for that matter). So all suggestions gratefully received.
 
Ethernet wall sockets. I attach my laptop via ethernet cable directly to the LAN port on my router, and it works. I try this with another cable, and the second also works. However, if I then run one of these known-working cables from my router to the ethernet wall socket, and the other from another socket in the house to my laptop, I have no connection. Ergo, it's a problem with the sockets, right? I have unscrewed the cover plate on both and checked the wiring: one pair of wires was indeed crossed over, so I rectified that. But the damn arrangement still doesn't work, and I am at a loss. Is there anything obvious I have missed in my trouble-shooting? What would be the next logical step to take? Is there any possibility at all that I need to change a setting in the router (Mi wifi gen 4)?

I can't really just rely on the wi-fi signal: the wireless signal is unreliable, whereas I need to have a rock-steady connection for my online classes, on which rely not only my income but also my ability to stay in this country. Also, I can't easily have long cables snaking down the corridor (I am living with a toddler and a senior citizen with poor eyesight). And given who is sleeping in which room, I can't move my workspace closer to the router itself (or vice-versa, for that matter). So all suggestions gratefully received.
Is the router a generic box or was it provided by the Internet Provider?
There may be some decoding/unscrambling going on in the router, which explains why you have to use it.
 
Is the router a generic box or was it provided by the Internet Provider?
There may be some decoding/unscrambling going on in the router, which explains why you have to use it.
It's generic - manufactured by Xiaomi. We bought it to replace the crappy box supplied by the ISP. I suppose I could try and reinstate the original box, and see whether that gets things working.
 
Back
Top