Archive for the ‘Data Analysis’ Category.
Of course I use my sample data generator when I need some sample data. But sometimes I want some real data. I looked high and low for data sources to include in the upcoming 101 Ready to Use Excel Formulas book that Mike and I are writing. If you like data as much as I do, there might be a few sources you haven’t found on my pinboard page.
If you have a good one that’s not on that page, leave it in the comments.
A common requirement is to generate a set of random numbers that meet some underlying criterion. For example, a set of numbers that are uniformly distributed from 1 to 100. Alternatively, one might want random numbers from some other distribution such as a standard normal distribution.
While there are specialized algorithms to generate random numbers from specific distributions, a common approach relies on generating uniform random numbers and then using the inverse function of the desired distribution. For example, to generate a random number from a standard normal distribution, use =NORM.S.INV(RAND())
Another common requirement is the generation of integer random numbers from a uniform distribution. This might be to select people for something like, say, training, or a drug test. Or, it might be to pick a winner for a door prize at a social event. It might also be to assign players to groups for a sport tournament such as golf.
For a version in a page by itself (i.e., not in a scrollable iframe as below) visit http://www.tushar-mehta.com/publish_train/xl_vba_cases/0806%20generate%20random%20numbers.shtml
Recently, I received yet another promotion from yet another company offering me money at zero percent interest with the predictable asterisk next to the zero percent. Instead of just shredding the offer I decided to create a downloadable Excel template to analyze the offer, which was an interest-free 18 month loan for a 4% transaction fee with a minimum $10 fee.
Obviously, the transaction fee makes sure that the money is not ‘free.’ So, how does one calculate the cost of the loan? I settled on an “effective interest rate.”
For a version in a page by itself (i.e., not in a scrollable iframe as below) visit http://www.tushar-mehta.com/publish_train/xl_vba_cases/0920%20free%20money.shtml
TM Goal Seek enhances the existing user interface to Excel’s Goal Seek feature. The built in Goal Seek is a simple optimization tool that suffices for a large number of scenarios. The UI, unfortunately, is extremely unwieldy and unfriendly. TM Goal Seek is a simple add-in that is easier to use than the default dialog box because of three critical benefits:
- The target value can be a number or a reference to a cell that contains a number,
- The add-in retains values previously entered in the dialog box, and
- One can interact with the worksheet even with the dialog box open.
The motivation to develop the add-in came from work I was doing for a client that involved risk analysis on multiple investment scenarios of financial derivative trades. The default Goal Seek interface took way too long.
The current version of the add-in will expire on May 1, 2013.
For more see http://www.tushar-mehta.com/excel/software/goal_seek/index.htm
Analyze data with Excel on the web
Microsoft has introduced a new web based capability that extends its Excel Services offering. This new capability provides a limited interactive view of any table in a web page.
An introduction to this service is below. For those interested in additional capabilities and more advanced and useful capabilities see:
For the consumer:
Learn more about Interactive View
Analyze a table in any web page with a dynamic interactive view
For the developer:
Implement the Interactive View feature for 1 table
Implement the Interactive View feature for multiple tables
Improve the formatting and layout of the 'Interactive View' buttons
As an example, the image below shows a table, the Interactive View button, and the result.
For a live demo, developer tips, and more, please see http://www.tushar-mehta.com/publish_train/data_visualization/15c%20interactiveview/index.htm