Skip to main content

Things to remember - Google Sheets - how to show the last number in a column

By
I have a Google Sheets file that I put all the water information for our farm into. It's fairly comprehensive with formulas etc to let me know the following once I measure the water level from the top of the tanks:

  • volume of water remaining
  • amount used since the last recording date
  • average usage since last recording date
  • amount of rain we can have until the main collection tank overflows (so I know when to pump it up to the feeder tanks)
I would like a summary of the actual levels in each tank in a nice little chart that I can import into our personal intranet site hosted with Google Sites (this part is ridiculously easy), but I don't want to have to change the site information overtime I think about it. 

I found this:

=FILTER( A10:A100 , ROW(A10:A100) =MAX( FILTER( ArrayFormula(ROW(A10:A100)) , NOT(ISBLANK(A10:A100)))))
Now if you put that into a cell, alter the ranges to suit, it will give you the last number in the column (and maybe row, but I haven't tested that). It works very well - not only for numbers but for the dates as well. The little summary chart now shows:
  • The Date of last reading
  • amount of water remaining in each tank
  • amount of rain we can store until the collection tank overflows
And with Google Sheets adding in some decent conditional formatting I have it all colour coded and it looks a bit like this:


Not bad eh? The bit that tells me the overflow is elsewhere on the page. And yes, we're in pretty decent shape given that it's the start of Autumn and we should get rain soon!
Labels:

Comments

Post a Comment

Popular posts from this blog

Plone - the open source Content Management System - a review

By
One of my clients, a non-profit, has a lot of files on it's clients. They need a way to digitally store these files, securely and with availability for certain people. They also need these files to expire and be deleted after a given length of time - usually about 7 years. These were the parameters I was given to search for a Document Management System (DMS) or more commonly a Content Management System (CMS). There are quite a lot of them, but most are designed for front facing information delivery - that is, to write something, put it up for review, have it reviewed and then published. We do not want this data published ever - and some CMS's make that a bit tricky to manage. So at the end of the day, I looked into several CMS systems that looked like they could be useful. The first one to be reviewed was OpenKM ( www.openkm.com ). It looked OK, was open source which is preferable and seemed to have solid security and publishing options. Backing up the database and upgradin
Post a Comment
Read more

Musings on System Administration

By
I was reading an article discussing forensic preparation for computer systems. Some of the stuff in there I knew the general theory of, but not the specifics of how to perform. As I thought about it, it occurred to me that Systems Administration is such a vast field. There is no way I can know all of this stuff. I made a list of the software and operating systems I currently manage. They include: - Windows Server 2003, Standard and Enterprise - Exchange 2003 - Windows XP - Windows Vista - Windows 2000 - Ubuntu Linux - OpenSuSE Linux - Mac OSX (10.3 and 10.4) - Solaris 8 - SQL 2005 - Various specialised software for the transport industry I have specific knowledge on some of this, broad knowledge on all of it, and always think "There's so much I *don't* know". It gets a bit down heartening sometimes. For one thing - I have no clue about SQL 2005 and I need to make it work with another bit of software. All complicated and nothing straightforward. Irritating doesn&
Post a Comment
Read more

Traffic Monitoring using Ubuntu Linux, ntop, iftop and bridging

By
This is an update of an older post, as the utilities change, so has this concept of a cheap network spike - I use it to troubleshoot network issues, usually between a router and the network to understand what traffic is going where. The concept involves a transparent bridge between two network interface cards, and then looking at that traffic with a variety of tools to determine network traffic specifics. Most recently I used one to determine if a 4MB SDSL connection was saturated or not. It turned out the router was incorrectly configured and the connection had a maximum usage under 100Kb/s (!) At $1600 / month it's probably important to get this right - especially when the client was considering upgrading to a faster (and more expensive) link based on their DSL provider's advice. Hardware requirements: I'm using an old Dell Vostro desktop PC with a dual gigabit NIC in it - low profile and fits into the box nicely. Added a bit of extra RAM and a decent disk and that&
6 comments
Read more