Today we weave together a few Excel features to build out a dynamic solution. We have Offices, Reps and their Percent of Quota. Using dependent dropdown lists, we solve a problem of changing the Office selection but the Rep name stays next to the wrong Office. With the help of Excel MVP Jordan Goldmeier, we […]
Here’s a mini app that I developed based on a challenge that Mr. Excel answered: How do I randomize an alphabetical list of cafeteria specials? Kevin Lehrbass also took this on with a different spin http://www.myspreadsheetlab.com/2014/01/helping-dean-pelton-with-his-menu-in-excel/ This whole thing was intriguing to me as I thought about the various problems that would come up as […]
Sharon asked: how do I assign people the correct number of days off? Employees receive a number of Paid Days Off based on how long they’ve been with the company. In this tutorial we use the functions: YEAR YEARFRAC and VLOOKUP with True
(NOTE: the features in this video are not available to Mac users or PC users with Excel 2003 or 2007) A common blunder in spreadsheet development is the separation of data into categories. Stop it! When you have 12 tabs for each month and want to know how Q2 went for a particular office, you’ve […]
Logarithmic, Scales, Combo Charts & Secondary Axes. Often we get data that is hard to analyze because the range between the data is huge. Here, we look at 2 examples: 1. City populations that range from 300 to 614,000 2. Volunteer hours that range from 1.5 to 91 To create meaningful views, we use logarithmic […]
This video accompanies the blogpost: http://datascopic.net/excel-data-model/ Today we have 3 sets of data and 7 data fields related to website users and their activity.. 1. User ID, User Type 2. Personal ID, Name, Profession 3. Log-in Date, ID, Time Spent Logged In What do we do when we want to look at correlations between 2 […]
It’s always a student who asks a question that I hadn’t thought to ask. “So, Oz, since True is approximate can I use it to match Ann Giamatti and Anne Giamatti?” No! That’s a good intuition but VLOOKUP w/True is used for categories and tiers. Let’s look at it this way: We’re grading students where […]
In this example we see the AGGREGATE function in this video and use it for managing a list of members and guests. Challenges: we have the list of members and number of guests, and then people start saying they aren’t attending. GEE WHIZ! And then our row numbers get screwed up. Solution: use AGGREGATE and […]
NOTE: Data Explorer is now called Power Query We want a simple bar chart showing U.S. Governors, by party, and the year their terms end. To do this, we use 1. Excel’s Data Explorer Preview (a free Microsoft AddIn for Excel 2010, 2013. Sorry, Mac users. This is another feature that’s only on the PC […]