Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss
Long spreadsheets
16 views
Skip to first unread message
dr.s.l...@gmail.com
Nov 6, 2018, 6:29:35 PM11/6/18
to
I'm new to spreadsheet calculations. I'm using LibreOffice, but would like the sheet to be Excel-compatible.
I have a "Running Total" column, a plus column, and a minus column; in pseudo-code, RT[n] = RT[n-1] + plus[n] - minus[n], all being currency. I also have a date column and a text column. I will append lines about twice a week or so. At the top there is a text heading, and after that the initial Running Total is pseudo-calculated as =0. There is also a text footer. So far so good, I think. The columns are formatted as columns, and the RT[first] expression has been dragged down. Still good.
But I need to append new lines to the calculation part. Is it *necessary* to remember to drag the expression down *each time*, or is there a way of making that automatic? I could, of course, drag the expression down manually a very long way, and never insert new lines.
I think I must have missed seeing something which should have been obvious.
Advice?
ASIDE - general spreadsheets - it should be possible to have a numeric column formatted as Date, with the dates being in any of the three main ISO8601 forms - yyyy-mm-dd, yyyy-Www-d, & yyyyddd. Please. Don't trust US coders (NIST apart) to calculate yyyy-Www-d correctly for all dates.
P.S. Is there a better newsgroup for spreadsheets? this one seems quiet.
--
(c) Dr. S. Lartius, UK. Gmail: dr.s.lartius@ |
I have a "Running Total" column, a plus column, and a minus column; in pseudo-code, RT[n] = RT[n-1] + plus[n] - minus[n], all being currency. I also have a date column and a text column. I will append lines about twice a week or so. At the top there is a text heading, and after that the initial Running Total is pseudo-calculated as =0. There is also a text footer. So far so good, I think. The columns are formatted as columns, and the RT[first] expression has been dragged down. Still good.
But I need to append new lines to the calculation part. Is it *necessary* to remember to drag the expression down *each time*, or is there a way of making that automatic? I could, of course, drag the expression down manually a very long way, and never insert new lines.
I think I must have missed seeing something which should have been obvious.
Advice?
ASIDE - general spreadsheets - it should be possible to have a numeric column formatted as Date, with the dates being in any of the three main ISO8601 forms - yyyy-mm-dd, yyyy-Www-d, & yyyyddd. Please. Don't trust US coders (NIST apart) to calculate yyyy-Www-d correctly for all dates.
P.S. Is there a better newsgroup for spreadsheets? this one seems quiet.
--
(c) Dr. S. Lartius, UK. Gmail: dr.s.lartius@ |
Luuk
Nov 7, 2018, 8:55:35 AM11/7/18
to
CTRL+D
About if there is a 'better' list, there is a mailinglist:
https://www.libreoffice.org/get-help/mailing-lists/
or a forum:
http://document-foundation-mail-archive.969070.n3.nabble.com/Users-f1639498.html
both have (as far as i know) the same content..
Eike Rathke
Nov 10, 2018, 2:09:43 PM11/10/18
to
* Luuk, 2018-11-07 13:55 UTC:
There is https://ask.libreoffice.org/ for a Question and Answers style
community support, available in different languages, for example
https://ask.libreoffice.org/en/questions/ for English.
Eike
--
OpenPGP/GnuPG encrypted mail preferred in all private communication.
GPG key 0x6A6CD5B765632D3A - 2265 D7F3 A7B0 95CC 3918 630B 6A6C D5B7 6563 2D3A
Use LibreOffice! https://www.libreoffice.org/
> About if there is a 'better' list, there is a mailinglist:
> https://www.libreoffice.org/get-help/mailing-lists/
> or a forum:
> http://document-foundation-mail-archive.969070.n3.nabble.com/Users-f1639498.html
> both have (as far as i know) the same content..
Yes, Nabble is just another representation of the mailinglists.
> https://www.libreoffice.org/get-help/mailing-lists/
> or a forum:
> http://document-foundation-mail-archive.969070.n3.nabble.com/Users-f1639498.html
> both have (as far as i know) the same content..
There is https://ask.libreoffice.org/ for a Question and Answers style
community support, available in different languages, for example
https://ask.libreoffice.org/en/questions/ for English.
Eike
--
OpenPGP/GnuPG encrypted mail preferred in all private communication.
GPG key 0x6A6CD5B765632D3A - 2265 D7F3 A7B0 95CC 3918 630B 6A6C D5B7 6563 2D3A
Use LibreOffice! https://www.libreoffice.org/
Eike Rathke
Nov 10, 2018, 2:09:43 PM11/10/18
to
* dr.s.l...@gmail.com, 2018-11-06 23:29 UTC:
row above, which also works on a selection. So you could select the
columns and press Ctrl+D once to copy all cells from above
(automatically adjusting relative references in formula expressions to
the new position) and then replace the input value with the new value.
> ASIDE - general spreadsheets - it should be possible to have a numeric column formatted as Date, with the dates being in any of the three main ISO8601 forms - yyyy-mm-dd, yyyy-Www-d, & yyyyddd. Please. Don't trust US coders (NIST apart) to calculate yyyy-Www-d correctly for all dates.
I don't quite understand what you mean with Www, if it's abbreviated
month name then it is MMM, if it is the week number then it would be WW
but then yyyy-Www-d wouldn't make sense. Anyhow, yyyy-mm-dd is
recognized as date as is yyyy-mmm-dd but of course the actual month name
abbreviation recognized depends on the current locale. A numeric yyyyddd
input can't be a date because it is a number.
> But I need to append new lines to the calculation part. Is it *necessary* to remember to drag the expression down *each time*, or is there a way of making that automatic? I could, of course, drag the expression down manually a very long way, and never insert new lines.
As Luuk mentioned, there's Ctrl+D to copy values and formulas from the
row above, which also works on a selection. So you could select the
columns and press Ctrl+D once to copy all cells from above
(automatically adjusting relative references in formula expressions to
the new position) and then replace the input value with the new value.
> ASIDE - general spreadsheets - it should be possible to have a numeric column formatted as Date, with the dates being in any of the three main ISO8601 forms - yyyy-mm-dd, yyyy-Www-d, & yyyyddd. Please. Don't trust US coders (NIST apart) to calculate yyyy-Www-d correctly for all dates.
month name then it is MMM, if it is the week number then it would be WW
but then yyyy-Www-d wouldn't make sense. Anyhow, yyyy-mm-dd is
recognized as date as is yyyy-mmm-dd but of course the actual month name
abbreviation recognized depends on the current locale. A numeric yyyyddd
input can't be a date because it is a number.
dr.s.l...@gmail.com
Nov 10, 2018, 5:56:50 PM11/10/18
to
On Saturday, 10 November 2018 19:09:43 UTC, Eike Rathke wrote:
> * dr.s.l...@gmail.com, 2018-11-06 23:29 UTC:
> * dr.s.l...@gmail.com, 2018-11-06 23:29 UTC:
> > ASIDE - general spreadsheets - it should be possible to have a numeric column formatted as Date, with the dates being in any of the three main ISO8601 forms - yyyy-mm-dd, yyyy-Www-d, & yyyyddd. Please. Don't trust US coders (NIST apart) to calculate yyyy-Www-d correctly for all dates.
>
> I don't quite understand what you mean with Www, if it's abbreviated
> month name then it is MMM, if it is the week number then it would be WW
> but then yyyy-Www-d wouldn't make sense. Anyhow, yyyy-mm-dd is
> recognized as date as is yyyy-mmm-dd but of course the actual month name
> abbreviation recognized depends on the current locale. A numeric yyyyddd
> input can't be a date because it is a number.
You should read ISO 8601, or at least https://en.wikipedia.org/wiki/ISO_8601.
>
> I don't quite understand what you mean with Www, if it's abbreviated
> month name then it is MMM, if it is the week number then it would be WW
> but then yyyy-Www-d wouldn't make sense. Anyhow, yyyy-mm-dd is
> recognized as date as is yyyy-mmm-dd but of course the actual month name
> abbreviation recognized depends on the current locale. A numeric yyyyddd
> input can't be a date because it is a number.
I should have, for consistency, put yyyy-ddd ; but the condensed forms without the '-' characters are also standard and are unambiguous for a string which is known to be an ISO 8601 date.
The 'W' indicates that a Week number (range 01 to 53) follows, and a day-of-week (from Mon=1) follows. All Weeks have seven days. Week 01 contains the first Thursday of the Gregorian year. The ISO Week Number appears (without explanation) in the bar-code box of my daily newspaper.
A seven-digit string without 'W' must be yyyyddd meaning yyyy-ddd, where ddd is the ordinal date in the year, from 000 to 366.
Normally, 4 digits - never fewer (unless none) are used for the Year; but the Standard does say what to do for years which may be above 9999.
You will notice that each of those forms, but not a mixture, can be sorted by a simple string sort; and if the non-digits are removed, by a numeric sort.
I believe that spreadsheet date/times are generally stored as IEEE Doubles from an Epoch which was intended to be (I think) 1899-12-31 = 0; but the inventors of that failed to recall that 1900 was not a Leap Year; so yyyyddd could be a spreadsheet date.
Eike Rathke
Nov 11, 2018, 6:04:42 AM11/11/18
to
* dr.s.l...@gmail.com, 2018-11-10 22:56 UTC:
Ok, point taken. Could work for the yyyy-Www-d form.
> I should have, for consistency, put yyyy-ddd ; but the condensed forms without the '-' characters are also standard and are unambiguous for a string which is known to be an ISO 8601 date.
In the context of spreadheets though any numeric input without
separators is a number.
Ok, point taken. Could work for the yyyy-Www-d form.
> I should have, for consistency, put yyyy-ddd ; but the condensed forms without the '-' characters are also standard and are unambiguous for a string which is known to be an ISO 8601 date.
separators is a number.
dr.s.l...@gmail.com
Nov 11, 2018, 7:04:41 AM11/11/18
to
On Sunday, 11 November 2018 11:04:42 UTC, Eike Rathke wrote:
> * dr.s.l...@gmail.com, 2018-11-10 22:56 UTC:
> > You should read ISO 8601, or at least https://en.wikipedia.org/wiki/ISO_8601.
>
> Ok, point taken. Could work for the yyyy-Www-d form.
If that means that you are considering implementing it, be sure to work from ISO 8601, real or Wikipedia, and do not think of using DatePart.
> * dr.s.l...@gmail.com, 2018-11-10 22:56 UTC:
> > You should read ISO 8601, or at least https://en.wikipedia.org/wiki/ISO_8601.
>
> Ok, point taken. Could work for the yyyy-Www-d form.
> > I should have, for consistency, put yyyy-ddd ; but the condensed forms without the '-' characters are also standard and are unambiguous for a string which is known to be an ISO 8601 date.
>
> In the context of spreadheets though any numeric input without
> separators is a number.
Eike Rathke
Nov 11, 2018, 6:08:55 PM11/11/18
to
* dr.s.l...@gmail.com, 2018-11-11 12:04 UTC:
days since the null-date, which is 1899-12-30.
> I type 9 into my third column, and see £9.00 appear.
Because the cell is formatted with a *display* format of
[$£-809]#,##0.00 or some such.
> I believe that they are stored as IEEE Doubles;
Yes.
> but the cells know what the number should mean.
The cell knows what the number should be displayed as. Display formats
are not input masks.
>> In the context of spreadheets though any numeric input without
>> separators is a number.
> No; I type 33333 into my first column, and see 1999-04-05 appear;
That is because dates(+time) are formatted date serial numbers, 33333
>> separators is a number.
> No; I type 33333 into my first column, and see 1999-04-05 appear;
days since the null-date, which is 1899-12-30.
> I type 9 into my third column, and see £9.00 appear.
[$£-809]#,##0.00 or some such.
> I believe that they are stored as IEEE Doubles;
> but the cells know what the number should mean.
are not input masks.
Luuk
Nov 17, 2018, 8:00:50 AM11/17/18
to
On 12-11-2018 00:08, Eike Rathke wrote:
> * dr.s.l...@gmail.com, 2018-11-11 12:04 UTC:
>>> In the context of spreadheets though any numeric input without
>>> separators is a number.
>
>> No; I type 33333 into my first column, and see 1999-04-05 appear;
>
> That is because dates(+time) are formatted date serial numbers, 33333
> days since the null-date, which is 1899-12-30.
Actually this is just the default.
> * dr.s.l...@gmail.com, 2018-11-11 12:04 UTC:
>>> In the context of spreadheets though any numeric input without
>>> separators is a number.
>
>> No; I type 33333 into my first column, and see 1999-04-05 appear;
>
> That is because dates(+time) are formatted date serial numbers, 33333
> days since the null-date, which is 1899-12-30.
see: Tools/Option/LibreOffice Calc/Calculate → Date
The options are:
1) 12/30/1899 (default)
2) 01/01/1900
3) 01/01/1904
And the strange thing is that EXCEL uses '31 december 1899'
(actually EXCEL only supports dates starting from the year 1900)
Excel also has the option to use the 1904 date.
(https://support.microsoft.com/en-gb/help/214330/differences-between-the-1900-and-the-1904-date-system-in-excel)
DATAVALUE("01-01-1900") returns 1 in Excel
DATEVALUE("31-12-1899") returns 1 in LibreOffice Calc.
DATAVALUE("01-01-1900") returns 2 in LibreOffice
Eike Rathke
Nov 17, 2018, 8:17:19 PM11/17/18
to
* Luuk, 2018-11-17 13:00 UTC:
> 1) 12/30/1899 (default)
> (actually EXCEL only supports dates starting from the year 1900)
starting from 1900-03-01 have equal date serial numbers.
> 1) 12/30/1899 (default)
> And the strange thing is that EXCEL uses '31 december 1899'
Excel thinks there was 1900-02-29 which of course there was not.
> (actually EXCEL only supports dates starting from the year 1900)
> DATAVALUE("01-01-1900") returns 1 in Excel
> DATAVALUE("01-01-1900") returns 2 in LibreOffice
So with the null date shifted back by one day in LibreOffice dates
starting from 1900-03-01 have equal date serial numbers.
0 new messages