Apropos Tech Solutions Corp. Blog

Category: spreadsheets

  • LibreOffice Calc: circular reference

    A formula in which the answer depends on itself is called recursive in computer science. Some spreadsheets call it a circular reference.

    The following is my understanding.

    Let’s imagine the following case: tax is assessed as 35 percent of an earner’s after-tax income. Such a calculation is a circular reference: tax=0.35*(Income-tax).

    LibreOffice Calc allows such a calculation. However, circular reference needs to be enabled. Under Tools->Options->Calculate, check a box Iterations. I set the Minimum change to 0.001, then clicked OK.

    Next, I went to cell a1 and typed 75000, then to c1 and typed =0.35*(a1-c1). The answer appeared in c1: 19444.44.

    In that case, 19444.44 should be 35 percent of (75000-19444.44). Well, 75000-19444.44=55555.56, and 35 percent of 55555.56 seems to be 19444.44. Apparently, LibreOffice Calc delivered:)

    Source:

    software.codidact.com

    -JS

  • LibreOffice Calc: Goal Seek

    Goal Seek can be used to solve equations.

    The following is how I understand it.

    Let’s imagine this scenario: cost is 1000 +0.44x, while revenue is 1.5x, where x is units sold. What, then, is the break-even point for units sold?

    In LibreOffice Calc, Goal Seek could be used to find the break-even point.

    In one cell (say a3), one types the formula 1.5*c3 – 0.44*c3 – 1000.

    It seems to me that the variable cell (in this case c3) needs to be initialized in order for Goal Seek to work. So, in this case, one might just enter 0 in c3.

    Next, in the Tools menu, find Goal Seek. It first asks what the Formula cell is, which in this case would be a3. Next, it asks what the Target value is: in this case, 0. Finally, it asks for the Variable cell: in this case, c3. Next, click OK or else press Enter.

    When I did so, Goal Seek replied that it had succeeded, and that the answer would be 943.396 (to 3 decimal places). It asked if I would like that value entered into the variable cell, and I clicked Yes. 943.396 then appeared in c3.

    Slick, eh?

    Source:

    help.libreoffice.org

    -JS

  • LibreOffice Calc: Day 0

    On spreadsheets, date and time calculations are important. What about numeric representations?

    Today I entered 2026-may-29 on a LibreOffice Calc spreadsheet, then pressed Enter. It self-converted to 2026-05-29. Next, I clicked “Format as Date.” It answered with 46171.

    I wondered how it was getting that number, so looked it up. Apparently, by default in LibreOffice Calc, Day 0 was Dec 30, 1899. Every day since increments the date by 1. Therefore, Jan 1, 1900, as a numeric date, is 2. Jan 1, 1901, 365 days later, is 367, and so on. (1900 was not a leap year, believe it or not. I was surprised, too; that’s for another post.)

    On spreadsheets, dates are normally compared with other dates, so the “zero reference” might not matter too much, so long as it’s consistent. However, Excel’s day 1, apparently, is January 1, 1900 (whereas LibreOffice thinks that’s day 2, it seems).

    Therefore, one wonders: how compatible are LibreOffice Calc and Excel spreadsheets? Well, it seems that a LibreOffice Calc sheet can be saved in xlsx format. What about changing the default start date? Apparently, one can do that as well.

    This post is spilling in numerous directions; I plan to follow up. For now, at least, it seems clear how 2026-05-29 becomes 46171 on a LibreOffice Calc spreadsheet.

    Source:

    LibreOffice 26.2 Help: Date and Time Functions

    Microsoft Support: DATEVALUE function

    learn.microsoft.com: …1900 is not a leap year as a century needs to be divisible by 400 to be a leap year.

    -JS