4 ms·

A hack I'm disturbingly proud of, and its connection to some real math

16 points by ssp 17y ago

tptacek 17y ago
This is the answer to an old D.E. Shaw interview question, too.
ramanujan 17y ago
If he was on a Mac, copy the column and then: pbpaste | sort | uniq -c (assuming GNU uniq)
ewjordan 17y ago
Uhh...does OpenOffice not have COUNTIF? COUNTIF is pretty bread and butter when you're using Excel... [/me decides not to be lazy, Googles] Yup, it's there: http://wiki.services.openoffice.org/wiki/Documentation/How_Tos/Calc:_COUNTIF_function http://wiki.services.openoffice.org/wiki/Documentation/How_T... Get to know your spreadsheet functions, it will save you a lot of time and effort in the long run, half the stuff that you need is built in. The other half, you won't believe that it's not, but at least you'll know when you actually need to waste your time implementing simple counting hacks.
- param 17y ago
  heh, even though these are not completely analogous, I am reminded of old discussions when I would say 'I implemented this cool thing in C', and my roomie would say 'I just used this java library method'.
  - ewjordan 17y ago
    This brings up a good point, though: there's a reason people that have struggled with the lack of built in functionality in languages like C tend to be better programmers, even if they make a switch to languages with more featureful standard libs. The act of hacking your way through even the smallest tasks teaches you a lot. And there are plenty of times in Excel where you'll need to hack your way through - it's got a lot of functionality, but once you move past what it's specifically designed to do, you'll start to get annoyed. That's when you start seeing INDIRECT and VLOOKUP all over the place, and you start to curse the developers that designed the whole damn system. More than a few times I've started some analysis in Excel, but decided in the end that I'd just rather fiddle with primitive arrays in a sane programming language because of the hoops you need to jump through in Excel. Perhaps if the scripting language was anything but VBA, if it was less tedious to add built in functions that you can use from the spreadsheet, or if the syntax for working with the spreadsheet from within VBA wasn't so heavy, it would be more tolerable, but you can't win them all...
BobbyH 17y ago
It's awesome that he mentioned SUMPRODUCT, which is a function that anybody who uses Excel for startups should master. It allows you to do things like calculate the amount of sales in a region, which is enormously useful to analyze data and A/B tests. For instance, say you have a spreadsheet listing every sale in 2009 along with the region it was in. Column A is the sale amount per transaction. Column B is the region its in. You could write a SUMPRODUCT formula to see how much revenue you made in the West region, like so: =SUMPRODUCT ( ( A1:A1000 ) * ( B1:B1000 = "WEST" ) ) You could repeat the formula for every region to see how sales varied by region (or whatever metric). If Column C had another metric, you could add a further filter for that too by adding a term: =SUMPRODUCT ( ( A1:A1000 ) * ( B1:B1000 = "WEST" ) * (C1:C1000 = "Male" ) ) COUNTIF is useful for its limited purpose, but SUMPRODUCT is an incredibly powerful function that I use all the time to calculate summary stats by period and other metrics.

16 points by ssp 17y ago

tptacek 17y ago

This is the answer to an old D.E. Shaw interview question, too.

ramanujan 17y ago

If he was on a Mac, copy the column and then: pbpaste | sort | uniq -c (assuming GNU uniq)

ewjordan 17y ago

Uhh...does OpenOffice not have COUNTIF? COUNTIF is pretty bread and butter when you're using Excel... [/me decides not to be lazy, Googles] Yup, it's there: http://wiki.services.openoffice.org/wiki/Documentation/How_Tos/Calc:_COUNTIF_function http://wiki.services.openoffice.org/wiki/Documentation/How_T... Get to know your spreadsheet functions, it will save you a lot of time and effort in the long run, half the stuff that you need is built in. The other half, you won't believe that it's not, but at least you'll know when you actually need to waste your time implementing simple counting hacks.

param 17y ago
heh, even though these are not completely analogous, I am reminded of old discussions when I would say 'I implemented this cool thing in C', and my roomie would say 'I just used this java library method'.
- ewjordan 17y ago
  This brings up a good point, though: there's a reason people that have struggled with the lack of built in functionality in languages like C tend to be better programmers, even if they make a switch to languages with more featureful standard libs. The act of hacking your way through even the smallest tasks teaches you a lot. And there are plenty of times in Excel where you'll need to hack your way through - it's got a lot of functionality, but once you move past what it's specifically designed to do, you'll start to get annoyed. That's when you start seeing INDIRECT and VLOOKUP all over the place, and you start to curse the developers that designed the whole damn system. More than a few times I've started some analysis in Excel, but decided in the end that I'd just rather fiddle with primitive arrays in a sane programming language because of the hoops you need to jump through in Excel. Perhaps if the scripting language was anything but VBA, if it was less tedious to add built in functions that you can use from the spreadsheet, or if the syntax for working with the spreadsheet from within VBA wasn't so heavy, it would be more tolerable, but you can't win them all...

BobbyH 17y ago

It's awesome that he mentioned SUMPRODUCT, which is a function that anybody who uses Excel for startups should master. It allows you to do things like calculate the amount of sales in a region, which is enormously useful to analyze data and A/B tests. For instance, say you have a spreadsheet listing every sale in 2009 along with the region it was in. Column A is the sale amount per transaction. Column B is the region its in. You could write a SUMPRODUCT formula to see how much revenue you made in the West region, like so: =SUMPRODUCT ( ( A1:A1000 ) * ( B1:B1000 = "WEST" ) ) You could repeat the formula for every region to see how sales varied by region (or whatever metric). If Column C had another metric, you could add a further filter for that too by adding a term: =SUMPRODUCT ( ( A1:A1000 ) * ( B1:B1000 = "WEST" ) * (C1:C1000 = "Male" ) ) COUNTIF is useful for its limited purpose, but SUMPRODUCT is an incredibly powerful function that I use all the time to calculate summary stats by period and other metrics.