Coyote Blog

I will confess that somehow I never really learned pivot table mechanics in Excel, so I struggle with three dimensional data.  One example might be a spreadsheet with individual tabs where each tab is a different corporate division, and on each tab is a P&L by month (so three dimensions:  Month, P&L category, Division).  Let's assume the P&L is arranged the same on every page, with, for example, one divisions June's total revenue number in the same cell number as the June total revenue number on every other divisions' tab, ie this field is in cell c8 on every worksheet tab.

Long ago I created a simple way to get a total of a particular cell across all spreadsheets.  I would add a spreadsheet tab in the workbook before all the division tabs and another after all the division tabs.  Let's say I just name these tabs "Posta" and "Postb".  Then the sum of all cells C8 that are located on a spreadsheet tab between tabs "Posta" and "Postb" would be

=SUM(Posta:Postb!C8).

The problem comes when one wants to create a summary worksheet tab that doesn't sum all the values for C8 but summarizes them in a table.  Imagine a table where column A is the division name (that matches the name of the tab for that division) and column B is that division's June revenue, ie the value of cell C8 for that division from its individual spreadsheet.  The only way I knew how to do this before was manually and tediously.

But laziness is the mother of invention, and I finally encountered a workbook that was so tedious to summarize manually that I had to find another way.  I had a spreadsheet of 150 tabs, each worksheet tab being one of our locations containing online customer review scores formatted the same way into the same cells.  That is when I found the INDIRECT function.  Basically it allows one to craft a custom cell reference in a text string, feed that to the indirect function, which will output the contents of that cell.  So if our location names in the first column exactly match the worksheet tab names, then we can write

=INDIRECT("'"&$A3&"'"&"!C$8")

The ampersand symbols are basically text string concatenation operators, and are there to create the text string of a cell reference in the format excel expects.  The funny triple quotes is just to add a single quote mark before and after the tab name.   This particular string will give us the value of cell C8 that is in the worksheet tab with the name that is in cell A3.

You can also use this cell value from the INDIRECT function in more complicated formulas.  For example

=COUNTIF( INDIRECT("'"&$A3&"'"&"!$B$3:$B$500"),D$4)

would look in the spreadsheet tab whose name is in A3 and on that tab count all the values in the range B3 to B500 on that tab that have the value given in cell D4.  For example, if D4 is equal to "5" we could be counting all the reviews that had a score of "5".

Postscript:  This may be my record for the blog post with the niche-iest audience.  Mainly it is aimed at my son, who has the enviable job of being an analyst for a craft beer company in La Jolla.  He has learned not to complain much to me about his job, as my first job was in an oil refinery in Baytown, Texas, so my sympathy level is maybe lower than it should be.  Anyway, as part of a geeky family, he and I compete on Excel knowledge so this post is mainly my way of counting coup on him.

Update:  I totally agree with the comments that a relational database is needed.  Unfortunately, at the time I did this work, we had only within weeks been given access to the review data from the government recreation reservations database, and it was all in excel.  Faster to do the analysis in excel than to figure out how to read the sheets into something like mysql (of which I am positive there are a million simple tools for doing so).  But I will accept it as a challenge for this year.

I am not particularly in opposition to or enthusiastic about the current transgender movement.  On one hand, I have no problem with people managing their lives however they wish.  I met Dr. Renee Richards in 1982, for example (she was coaching a Princeton tennis player I knew) and liked her.  Deirdre Mccloskey is freaking brilliant, I wish I had met her.   On the other hand, as with most social movements on the Left nowadays, mere tolerance and live-and-let-live acceptance is not enough -- the movement demands complete conformity, and mercilessly shames anyone even the least bit slow to discard 5000 years of social norms around gender.  And the movement tends to descend into self-parody from time to time, such as demanding that tampons be provided to people who cannot possibly have a menstrual cycle.

Anyway, most of that is beside the point and is just background to an issue I was reminded of this weekend when I was visiting San Diego.  As many of you know, my company operates public parks and campgrounds for the government.  As such, we were largely subject to Obama-era orders that in Federally-owned bathrooms, people had to be allowed to use the bathroom that matched whichever gender they self-identified as (not necessarily the one matching their birth sex).  Unlike in past rules, there was no requirement that the person had taken any surgical or hormonal steps to transition -- only a self-declaration was required.

I will have to admit that the most entertaining part of this new requirement was explaining modern gender theory to my employees and managers, who tend on average to be over 65-years-old and without a college degree.  There were a lot of wide eyes and "wtf' expressions in the room.  Their main concern seemed to be potentially allowing male sexual predators into the women's room.  I explain to my employees that the extra risk here is trivial for a variety of reasons, but mainly because in practice this comes up vanishingly few times.  There just are not that many transgender people in the world, and campground bathrooms have never been targets for a lot of sexual predation.  Every single time I can remember our employees even being asked about our policy it turned out to be an activist testing us, probably to see if they could create grounds for a lawsuit.

From my experience, then, most public fears about transgender bathroom rules have turned out to be overblown. But, it turns out there is one issue that no one is talking about that could be a real, though not particularly serious, downside for women.  Let me explain.

The one major change in the public bathroom world as a result of the transgender movement is the accelerating shift from having multi-stall female and male bathrooms to having single-stall, gender-neutral bathrooms.  If bathrooms are all single-stall, then all the culture wars over gender and bathrooms are completely sidestepped.   Every public bathroom I have seen a government agency build over the last 5 years has been of this new design, and our company's policy is only to build this sort of facility rather than the old two-sided male/female bathrooms.  Here is an example from new construction at the children's pool in La Jolla:

OK, I am going to have to criticize one gender here but since I am going to criticize males, I will be OK.  Men's bathroom habits are terrible -- we tend to pee all over the place.  Even if the median guy is careful, the marginal guy is not and makes a total mess.  We had this problem when my kids were young -- my wife would ask me to take our toddler daughter to the men's room with me and I would tell her that was impossible, that the men's toilets were likely awful.   I can say from experience from cleaning over 1000 public bathrooms a day that men's rooms take way more cleaning than women's rooms.

So if one has these single stall bathrooms, they have to be cleaned a lot.  On busy days, our staff cleans ours 4,5,6 or even more times a day.  But there are many public agencies that apparently do not have the focus or resources to clean on this kind of frequency.  The City of San Diego, or whoever cleans these bathrooms in La Jolla, clearly does not clean enough, because these bathrooms were disgusting.  I did not really want to go in there and I could stand and do my business.  My wife would never have gone in there.

So there you have it women -- something else to look forward to.   That irritating long women's room line may become a thing of the past, but it could be replaced with much dirtier bathrooms.

I took this picture in La Jolla this weekend -- it is of a house with a very narrow driveway that has a turntable to help the car get in and out of the garage facing the right way.

I thought it was amazing, but my wife immediately began saying things like "well, I think you could easily get the car in and out without it by... blah blah."  I told her you don't have a turntable because it is necessary, you have it because it is awesome.   Didn't she watch the Batman series growing up?  It's a freaking turntable for your car!  What further justification is needed?

Local swimmers have gotten a court order forcing the City of San Diego to chase away the seals from the Scripps children's pool in La Jolla.  But it is not my intention to blog on that specifically, but on this bit:

The city said it would blast recordings of barking dogs to scare away the pesky pinnipeds at the cost of $688,000 a year. San Diego cannot use force because the seals are a federally protected marine species.

Please, oh please can I get paid $688,000 a year to play loud recordings on the beach?  I have not even cracked a spreadsheet on this, but I am betting I can turn a profit on that.