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@ |

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On 7-11-2018 00:29, [email protected] wrote:

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.

I only know the possibility to copy a formula from the line above, using
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..

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

* Luuk, 2018-11-07 13:55 UTC:

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.

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/

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On Saturday, 10 November 2018 19:09:43 UTC, Eike Rathke wrote:

* [email protected], 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 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.

--
(c) Dr. S. Lartius, UK. Gmail: dr.s.lartius@

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

* [email protected], 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

--
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/

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On Sunday, 11 November 2018 11:04:42 UTC, Eike Rathke wrote:

* [email protected], 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.

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.

No; I type 33333 into my first column, and see 1999-04-05 appear; I type 9 into my third column, and see £9.00 appear. I believe that they are stored as IEEE Doubles; but the cells know what the number should mean. Perhaps, to help low-level work, it
should be possible to format the displayed numbers as Hex or Octal, and to interpret input correspondingly.

--
(c) Dr. S. Lartius, UK. Gmail: dr.s.lartius@

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

* [email protected], 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.

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.

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/

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On 12-11-2018 00:08, Eike Rathke wrote:

* [email protected], 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.
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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

* Luuk, 2018-11-17 13:00 UTC:

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.

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/

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)