Skip to main content

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

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!

Comments

Popular posts from this blog

Windows 10 Enterprise Eval - gotchas

After an annoying turn of events where my Windows 10 Enterprise USB drive failed, attempts to install Win10 onto a computer failed miserably. I turned to the net and managed to get my hands on Microsoft's Windows 10 Enterprise Evaluation. I have an enterprise key so I thought - cool! Here's the opportunity to get it going and to then upgrade the license later. Full install, patched etc and all is swell. Except when I try to upgrade. I straight up tried changing the licence key only to get a variety of errors, most of which are pertaining to the activation system being unavailable. The I try this: https://winaero.com/blog/upgrade-windows-10-evaluation-to-full-version-easily/ but it doesn't work either. Next I'll try this: h ttp://www.edugeek.net/forums/windows-10/174594-upgrading-windows-10-enterprise-90-evaluation-full.html And if all else fails, in goes the bootable USB I've now created. If only I'd had this in the first instance I would not be writing t

Plone - the open source Content Management System - a review

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

Fixing a black screen after doing a Kali Linux update

Kali Linux is a rolling Linux distribution designed for security and penetration work. You can find details on it here: www.kali.org . We run this excellent product for a range of different security work and it's been great. I built the image in VMplayer, then shared it to the team and we've all been at it since. A recent update broke it though - black screen, no network and completely unresponsive. There are lots of posts about similar things - mostly to do with graphics adaptors, however, we found that executing the following at a root prompt fixed it. But how to get to the root prompt from a blank screen? Linux has a number of terminals available to the user - most of us use the graphical one to do our day to day, but you can access a command line prompt without much trouble. Simply hold CTRL-ALT and then F2 or F3 down at the same time and it drops you to a command line login. BOOM. Time to fix it up. For me, and for the other fellas in the team, all it too was to