Category Archives: Excel

History Through Spreadsheets: Executive Orders

Posted on 6-February-2017 by mathscinotes

During my recent seminar on Excel's Power Query feature, I showed my team how to grab data executive order data from the web and generate a simple plot (Figure 1). After generating the plot, I asked the audience what we could learn from this graph. I was expecting to hear that the early 1900s – the time between Teddy Roosevelt and Franklin Roosevelt – was a time of massive use of executive orders. Continue reading →

Posted in Civics Through Spreadsheets, Excel, History Through Spreadsheets | 3 Comments

Calorie Per Acre Improvements in Staple Crops Over Time

Posted on 4-January-2017 by mathscinotes

My family has strong agricultural roots – mainly in dairy and potato farming – and our holiday conversations frequently turn to discussions of crop yields (bushels per acre or lbs per acre). As I listened to the discussion between my brothers on this year's crop yields, I realized that the yield numbers they were quoting were much higher today than we saw as children. This made me curious, and I decide to go out to the US Department of Agriculture's National Agricultural Statistics Service crop database and download CSV files on the yield of some key staple crops for processing by Power Query (i.e. recently renamed Get and Transform). I will be using this file to train my staff on defining Power Query functions. No macros were used in this analysis. Continue reading →

Posted in Excel, General Science, History of Science and Technology, History Through Spreadsheets | 5 Comments

Using Excel's Solver and VBA For Repetitive Table Calculation

Posted on 16-December-2016 by mathscinotes

I will be providing some employee training on Excel in January, and I need an example of how to automate the use of Excel's Solver add-in – a powerful optimization tool that few engineers use effectively. When I give a training seminar, I make a serious effort to show how I use Excel on real problems. While I generally use Mathcad for most optimization applications, Mathcad does not support integer programming – an optimization method where some or all variables are restricted to be integers. Here is where Solver shines – it supports integer programming. Continue reading →

Posted in Construction, Excel | 9 Comments

Excel Data Table with More Than Two Input Variables

Posted on 30-October-2016 by mathscinotes

I am a big fan of Excel data tables, but I often struggle because they are designed to work with one or two input variables and a single output function. Many of calculations that I do have more than two input variables that I need to vary. These calculations also often have multiple output values. This post discusses how to create a data table with more than 3 input variables and more than one output variable. Continue reading →

Posted in Excel | 2 Comments

Using Excel's Icon Sets for Testing Equality -- Ugh

Posted on 7-October-2016 by mathscinotes

I use Excel everyday, but that does not mean that I have used every feature. Yesterday, I was asked to prepare an analysis for our marketing group of the maximum possible distances that over which a customer can be served using various types of fiber optic communication systems – we call this parameter "reach". During this analysis, I saw what I thought was an ideal opportunity to use Excel's icon sets for the first time (Figure 1). Continue reading →

Posted in Excel | 7 Comments

Optical SFP Power Estimation Using Curve Fitting

Posted on 26-May-2015 by mathscinotes

I was asked today how to use Excel to estimate the power usage of two optical components at case temperatures for which we had no data. I initially solved the problem in Mathcad by fitting an equation of the form $latex c_0 \cdot e^{c_1 \cdot T_{Case}}+e_2 to the data and computing the corresponding power. Continue reading →

Posted in Excel, General Mathematics | Comments Off on Optical SFP Power Estimation Using Curve Fitting