The Firing Line Forums More work with that FBI Spreadsheet this time on AWB and Shall/May Issue
 Forum Rules Firearms Safety Firearms Photos Links Library Lost Password Email Changes
 Register FAQ Calendar Search Today's Posts Mark Forums Read

January 31, 2013, 02:46 PM   #1
JimDandy
Senior Member

Join Date: August 8, 2012
Posts: 2,556
More work with that FBI Spreadsheet this time on AWB and Shall/May Issue

Tacked on some columns with 2011 Census projected data, and some rows with the US - AWB states, and US - AWB and MAY issues states.. anyone with some excel experience want to double check my formulas?

Here's what it looks like if I got my stuff right..
Attached Files
 2011 FBI Murder stats version 2.pdf (143.6 KB, 124 views)

 January 31, 2013, 03:24 PM #2 speedrrracer Senior Member   Join Date: December 15, 2011 Location: San Diego, CA Posts: 317 if you wouldn't mind...toss in std dev, please thanks
 January 31, 2013, 03:34 PM #3 JimDandy Senior Member   Join Date: August 8, 2012 Posts: 2,556 std dev? And if I can I will. I'm looking for an editor so there are two eyes checking formula because I'm a excel worker, not an excel wonder...
 January 31, 2013, 04:12 PM #4 Madcap_Magician Senior Member   Join Date: August 13, 2009 Location: MN Posts: 668 Standard deviation. The measure of variation from the data's mean value. It should be the square root of the variance, IIRC.
 January 31, 2013, 04:18 PM #5 Woody55 Senior Member   Join Date: June 19, 2012 Location: East Texas Posts: 407 Can you post a live spread sheet instead of a pdf file?
 January 31, 2013, 04:32 PM #6 hermannr Senior Member   Join Date: March 24, 2011 Posts: 730 devil's advocate here...at first glance it looks to me like these states show the more urban the state's population, the higher the #/100k Overall, urbanization appears to be a bigger factor than an AWB. Which is an interesting point, as the states with a more rural population I would assume have more (per capita) semi-automatic rifle than those with a more urban population.
 January 31, 2013, 05:05 PM #7 JimDandy Senior Member   Join Date: August 8, 2012 Posts: 2,556 Actually the most telling trend is the geographic location. Southern States are several of the top 5. Alabama is not on the list either, along with the FLA we already knew about. But the rest of the Deep South hits the high notes pretty well, along with Maryland and DC which could be argued as border states. However, realizing that, the rest of the country with MAY issue and no AWB can still absorb those much higher rates, and still come out lower per capita than those states with an AWB, May Issue laws, or both. I still have no idea what you mean by standard deviation, not something I've dealt with before. I've got the VERY basic concept, but no clue how to discover it let alone implement it. I can't post the sheet because the website only allows PDFs and images. If you have a filesharing site I can upload it to for you, give me location and it's off to the cloud for you.
 January 31, 2013, 05:42 PM #8 Evan Thomas Staff   Join Date: July 7, 2008 Location: Upper midwest Posts: 4,804 Without going all technical, standard deviation is a measure of how tightly a set of values clusters around the mean (arithmetic average) of those values: the smaller the SD, the less those values spread out around the mean. Easy to calculate in Excel: the basic formula is =STDEV(number 1;number 2;...). Generally you'll replace "number 1..." with the cell references where the numbers can be found: so =STDEV(A1;A50) will give the standard deviation of the values in the cells from A1 to A50. Easy as pi. It's a pity that you can't just upload the spreadsheet here and insert it into your post as an attachment -- wouldn't it just raise the tone if we could all upload spreadsheets? Talk about responsible gun owners... __________________ Never let anything mechanical know you're in a hurry.
 January 31, 2013, 05:58 PM #9 Aguila Blanca Senior Member   Join Date: September 25, 2008 Location: CONUS Posts: 8,939 Excel has a function built in for Standard Deviation, but I don't know what it's supposed to demonstrate in this context. OP, we can't check your formulae because they don't show up in a .PDF. It would also help immensely if you just reformat to landscape orientation so all the columns show up on one page. Also, do you have sufficient data to break down "Firearms" into handguns, rifles, and shotguns? Showing how few murders are actually committed with rifles could go a long way (except for the Feinsteins of the world) toward showing just how foolish banning AR-type rifles is.
 January 31, 2013, 06:00 PM #10 JimDandy Senior Member   Join Date: August 8, 2012 Posts: 2,556 Right, I have to have someone willing to accept an email attachment who will PM me their email address.
January 31, 2013, 06:03 PM   #11
Aguila Blanca
Senior Member

Join Date: September 25, 2008
Location: CONUS
Posts: 8,939
Quote:
 Right, I have to have someone willing to accept an email attachment who will PM me their email address.
You were able to attach the .PDF. When I clicked your link, it gave me an option to open or to save the file. Can't you just attach the Excel file?

 January 31, 2013, 07:17 PM #12 JimDandy Senior Member   Join Date: August 8, 2012 Posts: 2,556 Nope, the excel extension is not one of the ones allowed by the board. Edit: If anyone knows of a free cloud hosting service I'd be happy to put it there for collaborative work as well.
 January 31, 2013, 11:34 PM #13 btmj Senior Member   Join Date: November 1, 2011 Location: Near St. Louis, Missouri Posts: 844 that is probably because excel files can contain macros, which is a kind of executable code.... and it is very hard for anti-virus software to scan a macro for a harmful intent. The people who run this website are smart to not allow excel files.
 January 31, 2013, 11:53 PM #14 JimDandy Senior Member   Join Date: August 8, 2012 Posts: 2,556 I've had someone offer to host it. As I told them, with any luck, we can get it cleaned up, glean some more info from it then start handing it out to folks with a cover letter denoting the conclusions resulting from the spreadsheet.
 February 1, 2013, 12:51 AM #15 nazshooter Senior Member   Join Date: November 7, 2008 Posts: 151 If you have a Google account you could upload it there in their format and just post the link here. Sent from my ADR6425LVW using Tapatalk 2
 February 3, 2013, 02:51 PM #16 JimDandy Senior Member   Join Date: August 8, 2012 Posts: 2,556 Here's the form on a google drive, trying to add more columns as well, i.e. AWB laws, May/Shall Issue, FOID style state permit to purchase required. 2011 FBI Murder stats version 2.xlsx - https://docs.google.com/file/d/0B0XD...it?usp=sharing
 February 3, 2013, 06:42 PM #17 Aguila Blanca Senior Member   Join Date: September 25, 2008 Location: CONUS Posts: 8,939 Jim, I don't understand the second and third columns of data. First is "Total Murders." That I get. Then you have "Non-Firearm Minus Long Guns" and "Knives Minus Long Guns." Farther over, you have a column for "Gun" (meaning, I assume, all firearms) murders per 100k population. Then you have one for "rifle." But "long guns" also includes shotguns. It would seem to me that, if you're going to break down total gun murders, it should be broken down into Handgun, Rifle, Shotgun, and Unidentified Firearm. If you want to PM me your e-mail address, I'll take a shot at doing some cleaning up of the format and send my result back to you.
 February 3, 2013, 08:45 PM #18 JimDandy Senior Member   Join Date: August 8, 2012 Posts: 2,556 I've done a few things with this spreadsheet since I found it on the FBI website... Long guns vs non guns for an AWB. To show that they just aren't used often in crime for example. Lately I've been working on States with permissive gun laws vs Restrictive... FOID cards, state AWB bans, May/Shall issue permits...
 February 3, 2013, 09:03 PM #19 Battler Senior Member   Join Date: January 23, 2000 Posts: 1,185 If you want to stick with files, the .CSV file can be exported from and loaded to major spreadsheet apps. Click Export, CSV (MSDOS), and if it asks say you want commas. The resulting file is just cells separated by commas and is not a potential security issue like a spreadsheet file.
 February 4, 2013, 02:00 PM #20 JimDandy Senior Member   Join Date: August 8, 2012 Posts: 2,556 Here's another link for those who want another path to the file, provided by a currently anonymous TFL user. It's linked here, so users can download and alter it as they see fit.
 February 4, 2013, 03:32 PM #21 JimDandy Senior Member   Join Date: August 8, 2012 Posts: 2,556 Was able to find FLA and Alabama on state .gov pages. Alabama just gave a total, while FLA broke out firearms and various non firearms, but not type of firearm, i.e. Handgun/shotgun/rifle/unknown firearm. Getting those put in now. The three missing states, FL, AL, and AR are in using their state reports. Also added the three restrictive law categories for AWB, May Issue, and Permit to Purchase. Here's the new link. https://docs.google.com/file/d/0B0XD...it?usp=sharing Edit: If someone who knows how wants to download it, and add the standard deviation stuff talked about earlier, they can PM me for my email address and I'll put that version back up. Last edited by JimDandy; February 4, 2013 at 04:31 PM.
 February 4, 2013, 10:35 PM #22 Ruthless4christ Senior Member   Join Date: June 24, 2007 Location: CNY Posts: 790 this is awesome
 February 4, 2013, 10:48 PM #23 Spats McGee Staff   Join Date: July 28, 2010 Location: Arkansas Posts: 6,392 What exactly is it that you need from AR? I might be able to track it down. __________________ I'm a lawyer, but I'm not your lawyer. If you need some honest-to-goodness legal advice, go buy some.
February 4, 2013, 10:54 PM   #24
Aguila Blanca
Senior Member

Join Date: September 25, 2008
Location: CONUS
Posts: 8,939
Quote:
 Originally Posted by JimDandy Edit: If someone who knows how wants to download it, and add the standard deviation stuff talked about earlier, they can PM me for my email address and I'll put that version back up.
I know how to use the Standard Deviation function -- I use it at work. But I have no idea how it can be used with respect to any of these data.

Standard deviation applies when you have a string of data points all measuring the same thing, and you want to see how much or how little variation there is within the string. Ammo chronograph readings are a perfect example. You shoot a string of ten or twenty rounds of the same ammo, and the chrony records each shot. It will then give you back the average velocity, the max and min velocities, the extreme spread, and the standard deviation.

The standard deviation is an indicator of how much or how little the overall string varies (deviates) from the average. Smaller standard deviation equates to better consistency.

To whoever proposed adding Standard Deviation here -- what do you want it applied to? There are no data here that are really susceptible to a standard deviation analysis.

 February 5, 2013, 12:27 AM #25 JimDandy Senior Member   Join Date: August 8, 2012 Posts: 2,556 I imagine they want a standard deviation for the gun homicides per 100,000 people. If I get it workin right after that, I'll even try and sort it by several categories. I'll have to play with the category math at the bottom now that I've included permit to purchase in restrictive laws. Edit I got the stuff from AR mostly. They didn't break things down quite the same as the fed, but three states were missing. Alabama, Arizona, and Florida. I checked their state websites and found at least a basic count of firearm homicides for each state allowing me to fill in that far. Last edited by JimDandy; February 5, 2013 at 12:36 AM.

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts BB code is On Smilies are On [IMG] code is On HTML code is Off Forum Rules
 Forum Jump User Control Panel Private Messages Subscriptions Who's Online Search Forums Forums Home Hogan's Alley     Tactics and Training     Handguns: The Semi-automatic Forum     Handguns: The Revolver Forum     Handguns: General Handgun Forum The Hide     The Art of the Rifle: Bolt, Lever, and Pump Action     The Art of the Rifle: Semi-automatics     The Art of the Rifle: General     The Dave McCracken Memorial Shotgun Forum     NFA Guns and Gear     The Hunt The North Corral     Lock and Load: Live Fire Exercises     Competition Shooting     Curios and Relics     Black Powder and Cowboy Action Shooting The Skunkworks     Gear and Accessories     The Smithy     Handloading, Reloading, and Bullet Casting         Bullet Casting     The Harley Nolden Memorial Institute for Firearms Research The Conference Center     General Discussion Forum         TFL Photo Contest     Law and Civil Rights         Legal and Political     S.W.A.T. Magazine The Firing Line Gun Show     S.W.A.T. Exchange     Retail Deals and Feedback Forum Support     Site Questions and Tech Support (NO FIREARMS QUESTIONS)     Software and Function Testing

All times are GMT -5. The time now is 08:28 AM.

 -- vBulletin 3 ---- vBulletin 3 - variable fonts ---- Low profile (unsupported) Forum Home Page - Archive - Top