Variable view? We don't have no stinkin' variable view.
August 5, 2011 3:06 AM Subscribe
I know my way around SPSS, but I may shortly be required to do a lot of data analysis using only Excel. Where is the best place for me to learn advanced Excel skills online, with an emphasis on data management and analysis, rather than accounting?
Response by poster: Not in an academic setting, sadly - hence, relegated to Excel.
posted by embrangled at 5:37 AM on August 5, 2011
posted by embrangled at 5:37 AM on August 5, 2011
Not sure what you mean by "advanced excel" skills, but since you mention SPSS as a base a few things popped to mind that I use regularly. The usefulness of these will vary based on what "a lot of data analysis" means and if your challenge is the management or the analysis part.
1. If you need tools for cleaning up and standardizing data in the set, Google Refine does a good job of letting you clean up large messy sets. Data goes in and out of excel seamlessly.
2. If you need to clean up the dataset and run through descriptive/clustering and visualization tasks, the community edition of Rapid Miner is great. It comes from data mining (not statistics) world, but you will quickly grow to appreciate the utility of all of the pre-processing tools when it comes to making large sets ready for work.
3. If needing to do statistics without SPSS is the challenging part of the task, you can make an investment in learning R. It will have all of the statistics you could ever imagine needing as well as a strong set of graphing tools. It will be a throwback to command-line syntax days, so for people who have only done stats through a GUI it can be a steep learning curve.
I am not trying to avoid excel in my answer, but if this is not a one-off project and you expect to do these sorts of things again down the road then it is worth looking at some tools that will serve you well and be a little less kludgy. Life it too short, let the robots handle data management.
posted by cgk at 6:12 AM on August 5, 2011 [3 favorites]
1. If you need tools for cleaning up and standardizing data in the set, Google Refine does a good job of letting you clean up large messy sets. Data goes in and out of excel seamlessly.
2. If you need to clean up the dataset and run through descriptive/clustering and visualization tasks, the community edition of Rapid Miner is great. It comes from data mining (not statistics) world, but you will quickly grow to appreciate the utility of all of the pre-processing tools when it comes to making large sets ready for work.
3. If needing to do statistics without SPSS is the challenging part of the task, you can make an investment in learning R. It will have all of the statistics you could ever imagine needing as well as a strong set of graphing tools. It will be a throwback to command-line syntax days, so for people who have only done stats through a GUI it can be a steep learning curve.
I am not trying to avoid excel in my answer, but if this is not a one-off project and you expect to do these sorts of things again down the road then it is worth looking at some tools that will serve you well and be a little less kludgy. Life it too short, let the robots handle data management.
posted by cgk at 6:12 AM on August 5, 2011 [3 favorites]
I came to point out R, Rapid Miner, Google Refine (and I'll throw in Wrangler for quick conversions between excel data and useful datasets). Rapid miner is an excellent tool. We've expanded a lot of what we do in it, because we don't have the business case yet to drop the $30K on the SAS datamining package.
I'll also say that excel is not designed for certain tasks. More importantly, people who use excel generally don't have, nor need the moderate/heavy lifting aspects you get from SPSS (or SAS in my case). As such, I'll take our SAS and EViews models, and rebuild our simplified versions in excel using VBA for non-modelers to use. That means periodically I am doing a lot of VBA coding to fit my data.
Some (in some cases "once") useful links for VBA coding:
Range Objects
Multiple outputs from a single function
VBA Knowledgebase
Working with arrays in VBA
Multithreading in VBA
Decent Excel/VBA message board
A great example of class implementation in Access, which is easily translateable to excel
FindAll Function
use of .find
finding the last used cell
Data Validation
JKP
How to name a range on the fly, also the Mr. Excel forums
Using Circular references to solve recursive problems (I've used this to build autoregressive terms)
Also, depending on the modeling and use of SPSS: Eviews is not a bad economics statistics package for relatively little cash...
posted by Nanukthedog at 6:59 AM on August 5, 2011 [1 favorite]
I'll also say that excel is not designed for certain tasks. More importantly, people who use excel generally don't have, nor need the moderate/heavy lifting aspects you get from SPSS (or SAS in my case). As such, I'll take our SAS and EViews models, and rebuild our simplified versions in excel using VBA for non-modelers to use. That means periodically I am doing a lot of VBA coding to fit my data.
Some (in some cases "once") useful links for VBA coding:
Range Objects
Multiple outputs from a single function
VBA Knowledgebase
Working with arrays in VBA
Multithreading in VBA
Decent Excel/VBA message board
A great example of class implementation in Access, which is easily translateable to excel
FindAll Function
use of .find
finding the last used cell
Data Validation
JKP
How to name a range on the fly, also the Mr. Excel forums
Using Circular references to solve recursive problems (I've used this to build autoregressive terms)
Also, depending on the modeling and use of SPSS: Eviews is not a bad economics statistics package for relatively little cash...
posted by Nanukthedog at 6:59 AM on August 5, 2011 [1 favorite]
Google Refine as mentioned above is great for handling large sets of "erroneous" data.. spelling mistakes, blank cells, decimal in the wrong place. It has great tools for the first pass to eliminate complete outliers and bizarre numbers that shouldnt be in your data in the first place.
Then:
Free and Included in excel, enable the Analysis Tool Pak and Solver-addin is your excel. (Options /Addins ).
If you are using Excel 2007 , under the data tab there should be a section now called analysis.
A lot of the basic statistic can be done like t,f and z tests and even complex ones like Analysis of variance for 2 factor.( Complete list of its capabilities)
Solver is more for a transportation problem , where you have a equation with constraints and are solving to get the most optimum solution.
posted by radsqd at 9:55 AM on August 5, 2011
Then:
Free and Included in excel, enable the Analysis Tool Pak and Solver-addin is your excel. (Options /Addins ).
If you are using Excel 2007 , under the data tab there should be a section now called analysis.
A lot of the basic statistic can be done like t,f and z tests and even complex ones like Analysis of variance for 2 factor.( Complete list of its capabilities)
Solver is more for a transportation problem , where you have a equation with constraints and are solving to get the most optimum solution.
posted by radsqd at 9:55 AM on August 5, 2011
« Older What books should I read to learn about pre-modern... | Help me get the most out of TED Newer »
This thread is closed to new comments.
posted by rumposinc at 4:30 AM on August 5, 2011 [1 favorite]