Archive for the ‘Data Analysis’ Category.

April 29, 2014, 6:30 am by Dick Kusleika

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.

https://pinboard.in/u:dkusleika/t:data/

If you have a good one that’s not on that page, leave it in the comments.

July 7, 2013, 11:44 pm by Tushar Mehta

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

Tushar Mehta

Tags:

CDF,

data analysis,

Excel 2010,

Excel 2013,

Excel intermediate,

exponential function,

inverse function,

normal distribution,

random number,

uniform distribution Category:

Data Analysis,

Excel 14,

Excel 15,

Excel Intermediate,

For Normal People,

Worksheet Functions |

5 Comments
April 25, 2013, 12:40 am by Tushar Mehta

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

Tushar Mehta

February 21, 2013, 4:12 pm by Tushar Mehta

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

Tushar Mehta

August 13, 2012, 8:06 pm by Tushar Mehta

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

Tushar Mehta

Tags:

Charting,

data analysis,

Data Visualization,

Excel on the web,

Excel Services,

HTML table,

Visual Display Category:

Charting,

Data Analysis,

Data Visualization,

Excel Mashup,

Excel Services,

User Interface |

1 Comment