TIL: Conditional sum product in Google Sheets

=SUMPRODUCT(D5:D1009="",C5:C1009,B5:B1009)

Just learnt an easy way to do conditional SumProduct1 calculations in Google Sheets2.

In above example, I wanted the SumProduct of values in columns B and C, when the respective cell in column D was empty.

The solution is to simply introduce the conditional column as another input, with the condition stated in the input.

Sheets does the SumProduct calculations, with each true as 1, and false as 0, thus making it a Conditional SumProduct :)

Continue reading TIL: Conditional sum product in Google Sheets

Biometrics – to identify, not authorise

Biometrics are your username, not your password.

This tweet by Koushik made a lot of sense on first reading. But I couldn’t place my finger on why I agreed with it. Until I read the paragraph below:

All ‘passwords’ should be replaceable. If your credit card gets stolen, you can block it and get a new card. If your Aadhaar number and fingerprint are leaked, you can’t change it, you can’t block it.

Pranesh Prakash in HT

That clinches it for me.

If my password gets stolen, I can reset it to something new, something stronger.

What do I do if my fingerprint is my password? Can’t get a new fingerprint.

Can’t get a new retina, or DNA either. And they’re all a fair bit easier to steal than a strong password.

Sure, use biometrics to identify if you want. But follow the identification with authentication (with a password, or more), before giving that identity any authority.

Cropped axes – a flag for misrepresentation

Economist's misrepresentation of car prices, and how much of it is tax credits.

Saw this chart – on prices in a car segment in the US – in this article in The Economist. I just couldn’t get over how grossly it misrepresented the data, so here’s the crib.

Illusion

On a quick look, the chart above seems to indicate that the US government discounts electic car prices by anywhere from 37.5% (BMW i3) to 60% (Tesla Model 3) of the total price – the ratio of light blue bar segment to total.

The reason for this apparent fallacy is the cropped axis on which the values are plotted – instead of starting from zero, it starts from about $22,500 and goes up to $45,000. Adding to the crime of cropping is the location of the axis labels – in the already text heavy section on top.

Result: most viewers would casually interpret that tax credits form a huge rebate on electric cars.

Reality

Electric Car Pricing - Corrected Axis Chart

The interpretation changes quickly when the axis is expanded to start from zero.

The tax credit is now clear as just a small share of the real price – a standard $7,500, whatever the car price – rather than a hefty 60% for a Tesla Model 3.

What’s more, it now highlights how competitively priced the Tesla Model 3 and Chevy Bolt are, even if the price subsidy is removed – only BMW 3 series beats them. And that’s before the running & servicing cost savings are accounted for.

Don’t crop the axis!!

Based on my experience of reading, and creating, countless charts – one of my key learnings is simply:

If the axis are cropped, the chart creator is trying to send a false reading – so dig in deeper.

In the case of this chart, the cropping is compounded by hiding the labels in the text heavy section of the graphic.

It’s either a super lazy job by the chart making (and the editor). Or a case of making the chart fit the story / bias.

Please, don’t do it.

How I automate fetching Chrome Web Store user counts & ratings

Ego booster (or deflator) charts
Ego booster (or deflator) charts

I have 7 Chrome extensions and apps and, as a chronic numbers addict, I like to keep track of their user numbers (WAU), and ratings.

I use a spreadsheet (Google Sheet) to collect the data, and analyze trends, and catch (to diagnose) outliers. The same spreadsheet also functions as a JSON-providing backend for data being funneled elsewhere (e.g. for user numbers on this page).

While the analysis part, and the JSON-feed worked well, the data collection part was painful. Google doesn’t provide an API to fetch extension data, so I’ve had to fill the data manually into the spreadsheet every day!

For a long time, I used to open my Chrome Web Store (CWS) developer dashboard every morning, and one-by-one fill in the numbers into the spreadsheet cells. While this was relatively easy, if menial, on the desktop, it’s quite painful on the phone – copying numbers between two apps on the small screen.

Continue reading How I automate fetching Chrome Web Store user counts & ratings