Google Sheets QUERY column headings

The big problem with QUERY is that it generates unwanted column headings in its results. But I noticed that it doesn't always do that, and reading this article explained why

https://davidmeindl.com/google-sheets-how-to-remove-headers-from-query-result/

To summarise what he says, there are 2 circumstances in which QUERY adds headers, and corresponding ways of getting rid of them:

1. Including headers in the range specified as parameter 1 to QUERY
   • Specify 0 as parameter 3 to QUERY, so the headers aren't in the input
   • Specify OFFSET 1 in the query, so that the header row is not output
2. Having an aggregation function in the query, eg SUM
   • Specify eg LABEL SUM(B) '' in the query (can never make that work myself)
   • Use an outer query with OFFSET 1 to skip the header row - QUERY(QUERY(range, "SELECT SUM(B)"), "SELECT * OFFSET 1")
   • NEW If your actual result set only has 1 row of data, use CHOOSEROWS to choose it as row 2 (row 1 being the headers) - CHOOSEROWS(QUERY(range, "SELECT SUM(B)"), 2)

Google Sheets AND/OR with ARRAYFORMULA

AND and OR don't work with ARRAYFORMULA because they already have potential array->scalar scope - AND(array) ANDs the whole array together and returns a single value, for instance.

Instead use multiplication (AND) or addition (OR).

Eg array-ise AND(condition1, condition2) as ARRAYFORMULA((condition1)*(condition2)>0)

This isn't a peculiarity of ARRAYFORMULA so much as a (possibly) unexpected APL-style extra functionality of AND and OR.

There is a similar problem with any function which can take a range or a single value - eg MAX - because it will get to the A4:A style reference before ARRAYFORMULA does. Eg if you have ARRAYFORMULA(MAX(A4:A, 4)) intending ARRAYFORMULA to use A4:A to iterate over, it won't work because MAX will take the maximum of A4:A.
In those cases I don't think there's a simple workaround.

Google Sheets VLOOKUP

VLOOKUP doesn't seem to work when the search key is numeric and all the values in the range are also numeric. Even if the key is a quoted numeric string eg "603".

I find I can get round this by adding "" to the key, eg VLOOKUP("603"+"", Db, 4, 0). Presumably this coerces the search comparison to work in string terms.

Also NB that if a key range is sorted with numbers first, eg {118, 151, "AB"}, vlookup won't work properly unless you set the is_sorted param to 0.

Google Sheets 'Loading...'

If I have a Google Sheet with importrange() cells that occasionally get stuck on 'Loading...', I find I can force them to load by putting '=today()' into a blank cell on that sheet. Once they've loaded I just delete it again.

Google Query NO_COLUMN

Sometimes you can cure a persistent NO_COLUMN by putting the Range supplied to QUERY in curly brackets:

query({NamedRange}, "select sum(Col4) where Col1='Insane'")

I don't know why that works, as surely NamedRange is an array already, but it does.

xfce slow login

If xfce takes ages to log you on, try this advice
https://forum.xfce.org/viewtopic.php?id=5174

In fact it was such a godsend I'm going to copy it out here:

• command: rm -rf ~/.cache/sessions
• GUI: Settings Manager >> Session and Startup >> Session tab >> "Clear saved sessions". And when you next go to shutdown/logout make sure you UNCHECK the box that asks to save the session.

Thanks guys!

Asset proportions

Suppose you have two assets a and b, each will form a proportion of the total a+b. So if you have 60 units of a and 40 units of b, a/(a+b)=0.6 and b/(a+b)=0.4.

You want to know how b's proportion will change if you add x units to a.

So you're asking how b/(a+b+x) will relate to b/(a+b).

To get b/(a+b+x), divide b/(a+b) by the proportion of the new total a+b+x to the old total a+b.

Eg with the example above, you add 100 units to a. a+b+x=200, a+b=100, the proportion is 200/100=2, divide 0.4 by 2 to get the new proportion for b, 0.2.

Check, b is still 40, a+b+x is 200, 40/200 = 0.2.