###TL;DR: Don't bother uncrating. It's not worth it, (probably) even if you have ALL of the keys and ALL of the crates in the world.

So I got bored and created a little (or not) spreadsheet. This spreadsheet calculates the profitability (or not) of opening Mann Co. Supply Crates.

However, I could not figure out what to do with the 1% chance of getting "An Exceedingly Rare Special Item". This 1% chance is really the only thing that could make these crates profitable. If anyone knows what I should do about that, let me know.

Speaking of suggestions, if anyone sees anything wrong with anything (calculations, spelling, etc), please let me know.

For those who don't want to see a giant spreadsheet, I've posted an overview below. Don't worry though, for those that do I've linked the complete spreadsheet all the way at the bottom of the page.

-------------
Begin excerpt
-------------

Spreadsheet made by AWaterFountain (SteamID64:76561198034047807) (Reddit: u/A_Water_Fountain)

Prices taken from tf2spreadsheet.blogspot.com

Supplement prices from backpack.tf/pricelist when prices not available on spreadsheet, indicated with BLUE (#00b0f0)

Drop Rate taken from wiki.teamfortress.com/wiki/Mann_Co._Supply_Crate

Crate value not taken into consideration with the exception of Series #1, #19, #21, #30, #40. These values are static and must be changed in "Value of crate" and the "Expected Profit" calculation

"Or an Exceedingly Rare Special Item!" not taken into account due to the variance of possible outcomes

Intersection of "Value" and "Total" is an average value of the possible products

In case of a range of value on an item, the average is taken

All item pricing done on 2012.07.09, evening EST (UTC-5)

Full permissions to redistribute and edit as long as credit is given to AWaterFountain, with reference to both name, SteamID64, and Reddit URL, and any other editors there may be

If you make edits, please document them in column F, next to the applicable row. Leave a name for the edit to be credited to and set the background of the affected cell(s) to PURPLE (#7030a0)

This spreadsheet uses basic arithmetic, AVERAGE(), SUM(), STDEV(), and sets a local variable "key" in B15

An overview is available at the bottom of the spreadsheet

----
snip
----

Oveview: Crates sorted in descending order of profitability

Series #	EV	St Dev
23	-0.69	0.55
14	-0.73	0.31
13	-0.76	0.32
21	-0.77	0.66
31	-1.05	0.44
46	-1.11	0.08
19	-1.13	0.57
28	-1.20	0.14
25	-1.27	0.15
9	-1.33	0.15
33	-1.36	0.20
45	-1.43	0.10
20	-1.43	0.19
44	-1.44	0.13
3	-1.48	0.15
27	-1.50	0.20
26	-1.52	0.25
42	-1.53	0.09
24	-1.54	0.15
15	-1.54	0.16
7	-1.63	0.16
38	-1.66	0.15
40	-1.72	2.78
39	-1.72	0.07
41	-1.74	0.05
4	-1.76	0.15
8	-1.81	0.13
43	-1.82	0.03
37	-1.86	0.06
17	-1.86	0.05
11	-1.91	0.05
10	-1.93	0.04
34	-1.94	0.04
18	-1.95	0.04
2	-1.95	0.06
29	-1.96	0.07
16	-1.97	0.04
1	-2.01	0.25
12	-2.02	0.04
32	-2.03	0.03
5	-2.07	0.06
30	-6.52	3.56
36	N/A	N/A
35	N/A	N/A
22	N/A	N/A
6	N/A	N/A

NOTE: Couldn't get that :--: next to "Expected Return (in ref)" to go away. Makes me a little sad (it's for center justification, table breaks w/o it).

Link for the more-fun people: https://dl.dropbox.com/u/22365883/crates.xlsx

MD5- 4D59ACBA 56D72A36 CF80FD3C 1ED03D58
:D Have fun!

EMERGENCY EDIT:

Well, it's not all that big a deal.

Crate 40 has an expected return of -1.7187.

No crate is positive!

http://www.reddit.com/r/tf2trade/comments/wb3jh/psa_the_truth_behind_mann_co_supply_crates/c5bt7lk

EDIT: Series order, special for u/cheekynakedoompaloom :D

Series #	EV	St Dev
46	-1.11	0.08
45	-1.43	0.10
44	-1.44	0.13
43	-1.82	0.03
42	-1.53	0.09
41	-1.74	0.05
40	-1.72	2.78
39	-1.72	0.07
38	-1.66	0.15
37	-1.86	0.06
36	N/A	N/A
35	N/A	N/A
34	-1.94	0.04
33	-1.36	0.20
32	-2.03	0.03
31	-1.05	0.44
30	-6.52	3.56
29	-1.96	0.07
28	-1.20	0.14
27	-1.50	0.20
26	-1.52	0.25
25	-1.27	0.15
24	-1.54	0.15
23	-0.69	0.55
22	N/A	N/A
21	-0.77	0.66
20	-1.43	0.19
19	-1.13	0.57
18	-1.95	0.04
17	-1.86	0.05
16	-1.97	0.04
15	-1.54	0.16
14	-0.73	0.31
13	-0.76	0.32
12	-2.02	0.04
11	-1.91	0.05
10	-1.93	0.04
9	-1.33	0.15
8	-1.81	0.13
7	-1.63	0.16
6	N/A	N/A
5	-2.07	0.06
4	-1.76	0.15
3	-1.48	0.15
2	-1.95	0.06
1	-2.01	0.25

###TL;DR: Don't bother uncrating. It's not worth it, (probably) even if you have ALL of the keys and ALL of the crates in the world.

EDIT: Standard Deviation added

EDIT: Sig figs taken into account