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
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 |
Comment
July 7, 2012, 1:51 pm by Tushar Mehta
Excel supports two different ways to filter data that are in tabular format. Autofilter is a built-in capability driven via the user interface. As sophisticated as Autofilter has become in recent versions of Excel, no pre-defined setup can possibly cater to all the different questions that the consumer may want answered. These require a custom filter and Advanced Filter provides that capability. It is a data-driven mechanism that uses Excel formulas to extract specific information from the original data. For those who may have heard of SQL but have never been motivated to learn it, you can now leverage some of the power of SQL without learning a single word of SQL!
The layout of this document is as follows: 1) Introduction to the data set used in the examples, 2) Introduction to the Advanced Filter dialog box, 3) Filter using column headers, 4) Filter using Excel formulas, 5) Extract unique data, 6) Work with dynamic source data, and 7) Create a filter in a different worksheet or workbook.
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/data_analysis/06.shtml
Tushar Mehta
Tags:
advance filtering,
advanced data filter,
advanced filter,
advanced filter criteria,
advanced sort & filter,
custom filter,
data analysis,
Excel 2007,
Excel 2010,
Excel Basic,
Excel intermediate,
filter advanced,
remove advanced filter Category:
Excel 14,
Excel Basic,
Excel Intermediate,
Excel12,
Filtering,
User Interface |
9 Comments
March 2, 2012, 4:48 pm by Tushar Mehta
The TM Match Target add-in analyzes a list of numbers and finds combinations that sum to a given total. This has applications in a range of disciplines including processing receipts, reconciling payments such as health care insurance reimbursements or payments by a customer for many outstanding invoices, operations management and operations research, and supply change management.
There are a number of posts, easily found through Google or Bing, that address the same issue, including my own Find a set of amounts that match a target value. This add-in packages the analysis in an easy-to-use interface, includes a means to stop the analysis if it is taking too long and preserve the combinations found so far, together with various options on what results should be shown.
For more please visit http://www.tushar-mehta.com/excel/software/match_target/.
– Tushar Mehta
