More work with that FBI Spreadsheet this time on AWB and Shall/May Issue

JimDandy · January 31, 2013, 02:46 PM

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

speedrrracer · January 31, 2013, 03:24 PM

if you wouldn't mind...toss in std dev, please

thanks

JimDandy · January 31, 2013, 03:34 PM

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

Madcap_Magician · January 31, 2013, 04:12 PM

Standard deviation. The measure of variation from the data's mean value. It should be the square root of the variance, IIRC.

Woody55 · January 31, 2013, 04:18 PM

Can you post a live spread sheet instead of a pdf file?

hermannr · January 31, 2013, 04:32 PM

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.

JimDandy · January 31, 2013, 05:05 PM

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.

Evan Thomas · January 31, 2013, 05:42 PM

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

**Aguila Blanca** · January 31, 2013, 05:58 PM

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.

JimDandy · January 31, 2013, 06:00 PM

Right, I have to have someone willing to accept an email attachment who will PM me their email address.

**Aguila Blanca** · January 31, 2013, 06:03 PM

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?

JimDandy · January 31, 2013, 07:17 PM

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.

btmj · January 31, 2013, 11:34 PM

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.

JimDandy · January 31, 2013, 11:53 PM

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.

nazshooter · February 1, 2013, 12:51 AM

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

JimDandy · February 3, 2013, 02:51 PM

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

**Aguila Blanca** · February 3, 2013, 06:42 PM

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.

JimDandy · February 3, 2013, 08:45 PM

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

Battler · February 3, 2013, 09:03 PM

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.

JimDandy · February 4, 2013, 02:00 PM

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 · February 4, 2013, 03:32 PM

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.

Ruthless4christ · February 4, 2013, 10:35 PM

this is awesome

**Spats McGee** · February 4, 2013, 10:48 PM

What exactly is it that you need from AR? I might be able to track it down.

**Aguila Blanca** · February 4, 2013, 10:54 PM

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.

JimDandy · February 5, 2013, 12:27 AM

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.

January 31, 2013, 05:42 PM	#8
Evan Thomas Senior Member Join Date: July 7, 2008 Location: Upper midwest Posts: 5,631	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.

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:48 PM	#23
Spats McGee Staff Join Date: July 28, 2010 Location: Arkansas Posts: 8,821	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 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.

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search

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:58 PM	#9
Aguila Blanca Staff Join Date: September 25, 2008 Location: CONUS Posts: 18,457	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, 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: 864	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 Staff Join Date: September 25, 2008 Location: CONUS Posts: 18,457	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, 10:35 PM	#22
Ruthless4christ Senior Member Join Date: June 24, 2007 Location: CNY Posts: 790	this is awesome