TIL: Conditional sum product in Google Sheets

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

Just learnt an easy way to do conditional `SumProduct`1 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 :)

Biometrics – to identify, not authorise

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

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

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.