The Firing Line Forums

Go Back   The Firing Line Forums > The Conference Center > Law and Civil Rights

Reply
 
Thread Tools
Old January 31, 2013, 02:46 PM   #1
JimDandy
Senior Member
 
Join Date: August 8, 2012
Posts: 2,402
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
File Type: pdf 2011 FBI Murder stats version 2.pdf (143.6 KB, 124 views)
JimDandy is offline  
Old January 31, 2013, 03:24 PM   #2
speedrrracer
Senior Member
 
Join Date: December 15, 2011
Location: San Diego, CA
Posts: 259
if you wouldn't mind...toss in std dev, please

thanks
speedrrracer is offline  
Old January 31, 2013, 03:34 PM   #3
JimDandy
Senior Member
 
Join Date: August 8, 2012
Posts: 2,402
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...
JimDandy is offline  
Old January 31, 2013, 04:12 PM   #4
Madcap_Magician
Senior Member
 
Join Date: August 13, 2009
Location: MN
Posts: 606
Standard deviation. The measure of variation from the data's mean value. It should be the square root of the variance, IIRC.
Madcap_Magician is offline  
Old 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?
Woody55 is offline  
Old 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.
hermannr is offline  
Old January 31, 2013, 05:05 PM   #7
JimDandy
Senior Member
 
Join Date: August 8, 2012
Posts: 2,402
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.
JimDandy is offline  
Old January 31, 2013, 05:42 PM   #8
Vanya
Staff
 
Join Date: July 7, 2008
Location: Upper midwest
Posts: 3,840
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...
__________________
"Once the writer in every individual comes to life (and that time is not far off), we are in for an age of universal deafness and lack of understanding."
(Milan Kundera, Book of Laughter and Forgetting, 1980)
Vanya is offline  
Old January 31, 2013, 05:58 PM   #9
Aguila Blanca
Senior Member
 
Join Date: September 25, 2008
Location: CONUS
Posts: 6,301
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.
Aguila Blanca is offline  
Old January 31, 2013, 06:00 PM   #10
JimDandy
Senior Member
 
Join Date: August 8, 2012
Posts: 2,402
Right, I have to have someone willing to accept an email attachment who will PM me their email address.
JimDandy is offline  
Old January 31, 2013, 06:03 PM   #11
Aguila Blanca
Senior Member
 
Join Date: September 25, 2008
Location: CONUS
Posts: 6,301
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?
Aguila Blanca is offline  
Old January 31, 2013, 07:17 PM   #12
JimDandy
Senior Member
 
Join Date: August 8, 2012
Posts: 2,402
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.
JimDandy is offline  
Old January 31, 2013, 11:34 PM   #13
btmj
Senior Member
 
Join Date: November 1, 2011
Location: Near St. Louis, Missouri
Posts: 758
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.
btmj is offline  
Old January 31, 2013, 11:53 PM   #14
JimDandy
Senior Member
 
Join Date: August 8, 2012
Posts: 2,402
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.
JimDandy is offline  
Old February 1, 2013, 12:51 AM   #15
nazshooter
Senior Member
 
Join Date: November 7, 2008
Posts: 150
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
nazshooter is offline  
Old February 3, 2013, 02:51 PM   #16
JimDandy
Senior Member
 
Join Date: August 8, 2012
Posts: 2,402
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
JimDandy is offline  
Old February 3, 2013, 06:42 PM   #17
Aguila Blanca
Senior Member
 
Join Date: September 25, 2008
Location: CONUS
Posts: 6,301
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.
Aguila Blanca is offline  
Old February 3, 2013, 08:45 PM   #18
JimDandy
Senior Member
 
Join Date: August 8, 2012
Posts: 2,402
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...
JimDandy is offline  
Old 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.
Battler is offline  
Old February 4, 2013, 02:00 PM   #20
JimDandy
Senior Member
 
Join Date: August 8, 2012
Posts: 2,402
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.
JimDandy is offline  
Old February 4, 2013, 03:32 PM   #21
JimDandy
Senior Member
 
Join Date: August 8, 2012
Posts: 2,402
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.
JimDandy is offline  
Old February 4, 2013, 10:35 PM   #22
Ruthless4christ
Senior Member
 
Join Date: June 24, 2007
Location: CNY
Posts: 790
this is awesome
Ruthless4christ is offline  
Old February 4, 2013, 10:48 PM   #23
Spats McGee
Staff
 
Join Date: July 28, 2010
Location: Arkansas
Posts: 5,013
What exactly is it that you need from AR? I might be able to track it down.
__________________
A gunfight is not the time to learn new skills.

If you ever have a real need for more than a couple of magazines, your problem is not a shortage of magazines. It's a shortage of people on your side of the argument. -- Art Eatman
Spats McGee is offline  
Old February 4, 2013, 10:54 PM   #24
Aguila Blanca
Senior Member
 
Join Date: September 25, 2008
Location: CONUS
Posts: 6,301
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.
Aguila Blanca is offline  
Old February 5, 2013, 12:27 AM   #25
JimDandy
Senior Member
 
Join Date: August 8, 2012
Posts: 2,402
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.
JimDandy is offline  
Reply

Thread Tools

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 Jump


All times are GMT -5. The time now is 02:57 AM.


Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2014, vBulletin Solutions, Inc.
This site and contents, including all posts, Copyright © 1998-2014 S.W.A.T. Magazine
Copyright Complaints: Please direct DMCA Takedown Notices to the registered agent: thefiringline.com
Contact Us
Page generated in 0.13613 seconds with 10 queries