

A hack I'm disturbingly proud of, and its connection to some real math - ssp
http://godplaysdice.blogspot.com/2009/12/hack-im-disturbingly-proud-of-and-its.html

======
ewjordan
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_T...](http://wiki.services.openoffice.org/wiki/Documentation/How_Tos/Calc:_COUNTIF_function)

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
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
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
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.

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

------
ramanujan
If he was on a Mac, copy the column and then:

    
    
      pbpaste | sort | uniq -c
    

(assuming GNU uniq)

