Jump to content

Wikipedia:Reference desk/Archives/Computing/2018 May 13

From Wikipedia, the free encyclopedia
Computing desk
< May 12 << Apr | May | Jun >> May 14 >
Welcome to the Wikipedia Computing Reference Desk Archives
The page you are currently viewing is a transcluded archive page. While you can leave answers for any questions shown below, please ask new questions on one of the current reference desk pages.


May 13

[edit]

Age-tracking spreadsheet

[edit]

I would like to make a spreadsheet like this. The date cell is in date format and then the cells below show people's ages at different years. I'm having trouble figuring out what to put in the cells and make it start from around 2008. Please help if you know. Many thanks!!!

Anna Frodesiak (talk) 23:48, 13 May 2018 (UTC)[reply]

It's in Open Office, by the way. Anna Frodesiak (talk) 00:04, 14 May 2018 (UTC)[reply]

Let's say the first number is in cell B5. Go to cell B6 and input =B5+1 then click the cell's bottom right corner and drag downwards until you have enough rows. Repeat for each column. Alternatively, let's say the first year is in B5, and the rest go to B6, B7 etc., and you've put the birth year in C4. Then you can do what I said for the B column, while for the C column, put =$B5-C$4 and click+drag. The $ is an absolute reference and ensures that the reference to the fourth row never changes (so you effectively get B6-C4, B7-C4, B8-C4 etc. in following cells). When you put the absolute reference to $B, you can even drag it sideways to create other columns for other people's birth years without having to change anything. See [1] for a longer explanation of absolute references. This is for LibreOffice on Linux (Open Office doesn't exist anymore). For Calligra you need to shift+click. 93.139.26.207 (talk) 02:47, 14 May 2018 (UTC)[reply]
Thank you kindly. Actually, I know that part. It is making a cell show an age using the cell that shows the date of birth. That's what has me stumped. Best, Anna Frodesiak (talk) 04:02, 14 May 2018 (UTC)[reply]
Resolved
...thanks to coffee and blind luck. Anna Frodesiak (talk) 06:09, 14 May 2018 (UTC)[reply]
  • I would suggest ditching OpenOffice in favour of LibreOffice (which isn't a big change, generally). AFAIK (and this was certainly true when I shifted), OpenOffice only supports the A1, B2 notation from Lotus 1-2-3 for referencing other cells. Excel also supported the R1C1 notation. This was enough of a drawback that I used to pay to use Excel (and was thus locked into Windows), just for this one feature. LibreOffice though supports both.
The difference is that the syntax for relative referencing is far easier to manage with R1C1. Andy Dingley (talk) 08:45, 14 May 2018 (UTC)[reply]
Thanks . Thanks, Andy. LibreOffice sounds good and I just may give that a try. Best wishes, Anna Frodesiak (talk) 06:15, 15 May 2018 (UTC)[reply]