Tag: 23 things

LibreOffice Calc

Tools such as Excel and its open source competitor Calc are useful for managing all kinds of data. Because of their flexibility it is possible to hone in on a particular task and construct useful tools for data management. This post explores a particular spreadsheet I made in LibreOffice Calc, which uses array functions and dynamic named ranges to keep track of garden beds and plant germination.

As part of this project, I wanted to become more confident with Excel. However – I wanted to try out the open source version Calc, published as part of LibreOffice. The interface and expressions are very similar. Because embedding the sheets into the page was problematic I’ve included screenshots, walkthrough videos and code.

Selecting these images will open a new window.

  • Screenshot of the homepage of the Calc Spreadsheet
  • Screenshot of a comment in a Calc spreadsheet
  • Screenshot of the Garden Beds
  • Screenshot of plantings page in Calc spreadsheet
  • Screenshot of the Germination Page
  • Screenshots of the Families page

Videos

The following three videos will walk you through the spreadsheet, the germination page, and the array functions used.

Code!

In hindsight, I could probably now write the code a lot cleaner, however these are some of the significant codes in the cells.

This is the code which returns the most recent date that a plant family was planted in a particular location;

=IF((SUMPRODUCT(--($Plantings.$D2:$D100=D4),--($Plantings.$B2:$B100=B4),$Plantings.$A2:$A100))=0,"Never",(MAX(IF(test=1,$Plantings.$A2:$A100))))

The code for returning how many plants to be planted;

=SUMPRODUCT(--(Glist="Y"),(Gcount))-SUMPRODUCT(--(Pcount="Y"),(Gcount))

To return the number of germinated plants;

=COUNTIF(Gdate,">1")

To return the next fertilizer date;

=INDEX(fert,MATCH(B4,name,0))
Design a site like this with WordPress.com
Get started