I use MS Excel every single working day.
I would say that 90% of the time (if I am not in meetings), I would be staring at an Excel spreadsheet and tinkering away with numbers and formulae. Along the way, I have discovered and learned new things.
Thanks to EVADEDAVE for the screenshot.
1. Use SOLVER
I learned for example that the inbuilt SOLVER in Excel is a good tool - someone interested in optimization (minimization and maximization) should consider it as a first-stop. I have used it to fit theoretical models on data - specifically in fitting curves (S-curves, Sigmoid/Gompertz Curves are my favorite these days...) against a data-set.
Well. It's not the perfect solution - I am sure that there are a few more in the market. But for someone cash-conscious as me, it works. It sometimes doesn't - but hey, it comes with the bundle so I am happy with it.
One of the things that I have tried doing is embedding SOLVER in optimization and Monte Carlo routines in VBA. It works on my PC in the office (using Office 2007) - but it sometimes conks in my Mac Office 2004. I have used it to 'simulate' a very basic genetic algorithm in optimization. (Note the words *very basic*.)
SOLVER in Excel however is very good for a basic stuff: Say, you have 1'000USD and you'd want to spend it across different campaigns (yeah, I know it's unrealistic) given certain preconditions (at least 30% ought to be on the internet, minimum of 40% ought to be on press... etc) and given certain goals (e.g., reach 40% of the targets at least three times).
SOLVER can easily do that.
If you've taken up Linear Programming or Calculus's "First-order Derivatives and Optimization" in college - and are finding that you're doing more and more of similar stuff in your work, consider SOLVER. It's not the best tool - and certainly not a panacea. But again, it's the first step.
There are a few alternatives - power-solvers - in the market. I have come across SOLVER.Com. But I have yet to try their stuff. (The last time I tried, my 512KB machine couldn't handle it... so I guess, you have to have at least 1GB RAM.)
2. Visualize in Excel
Sometimes, when I deal with masses of numbers (a survey for example of 400 respondents with more than 10'000 columns representing their responses), I get overwhelmed too. (I guess one never gets immuned from "mathphobia and numerophobia".)
Some people I know would dive right in and do descriptive statistics on the data, calculate means and standard deviations, kurtosis, and skewness, after having checked the data's integrity. It creates more numbers - essentially - and adds to the perceived enormity of the task at hand.
(Actually, I am one of those who'd dive right in and do descriptives...)
Others would visualize them first - chart the data, look for aberrations and outliers, and then dive into a deeper analysis.
Some of the resources on Excel visualization and charts I really like include Vertex42, PeltierTech.Com, Andrew Engwirda's blogsite, and Jorge Camoes's.
I just recently discovered (and I should have discovered them earlier!) Juice Analytics. Here's one their entries on basic visualization using Excel.
Thanks to EVADEDAVE for the screenshot.
One of the beautiful things that OFFICE2007 has though is the in-built visualization techniques, under "Conditional Formatting". They have these icons that get embedded based on the value of the numbers - they are preset to assign icons (e.g., red-yellow-green) based on percentiles, but you can change that.
That's one way of visualizing in Excel.
3. Use the Data Analysis Toolpak
The Data Analysis Toolpak in Excel is quite basic - but it does make life a little easier. It has single/multiple-IV linear regression, histogram building, correlation and covariance extraction, and a few more routines available.
But if you really need high-powered stuff for analytics - and you don't want to spend that much money on SPSS, SAS, and other high-end softare (or just simply, you are familiar with Excel and don't want to get away from that environment) - then XLSTAT might be a good bet.
I use XLSTAT for my more cumbersome calculations. It does factor analysis, segmentation through cluster (hierarchical and k-means) analysis, Principal Components Analysis, and Multidimensional Scaling. In addition, it generates more complete tests on regression analysis than the inbuilt analysis toolpack in Excel. I would recommend it for intermediate needs.
I have also used XLMINER which is pretty good for CART (Classification and Regression Trees), Neural Networks, and supervised and unsupervised Data-Mining. It's kinda expensive though - and memory-consuming. But for those who are really interested in intermediate data-mining, XLMINER is a good alternative.
A Few More...
There are a few more stuff that you can do on Excel. I was told - but have not tried - that Excel can handle massive databases and relate it with MS Access or a SQL database. I have tried getting live stock price data using Excel2007 - but it keeps on hanging.
I think Excel is a great tool - and while Google's spreadsheets would probably be a challenger soon with its 'cloud computing' offering, right now, Excel is still my choice.
