300 level Archives - Page 2 of 2

23Oct 2013 by Charlie Nichols 2 Comments

Fuzzy VLOOKUP in Excel

300 level, Excel

[wp_ad_camp_1] [wp_ad_camp_4] pwrVLOOKUP. Just like VLOOKUP, only Better. You can do a VLOOKUP operation in Excel and get a fuzzy match instead of being limited to the exact match function built into Excel. To do a fuzzy VLOOKUP you need to use the pwrVLOOKUP function that is part of the Excel PowerUps Premium Suite add-in which is available as a free download on this site. Usage of the pwrVLOOKUP function is very similar to the built-in VLOOKUP function. The first 3 parameters are the same (lookup value, lookup range, and column offset). The difference starts in the fourth parameter. In pwrVLOOKUP, the fourth parameter is a strength-of-match value. It is a number between 0 and 100. If you do not provide this value, an exact match (strenth-of-match = 100) is…

30Sep 2013 by Charlie Nichols No Comments

How to Generate Random Demographic Data

300 level, Excel

[wp_ad_camp_1] [wp_ad_camp_4] How to Generate Random Demographic Data Why Generate Random Demographic Data? There are several reasons I can think of that you would want to or need to generate random demographic data. They primarily center on testing scenarios. Due to the nature of the data, it may be unfeasible for you to access real data to test your application or process. You wouldn’t want to risk exposing PII (Personally Identifiable Information) or other sensitive information. Also, some of the data you may need to test would be restricted or protected under the Sarbanes-Oxley Act (SOX) and/or the Health Insurance Portability and Accountability Act (HIPAA). There are complex regulations that you don’t want to run afoul of, but most of all you want to protect your customer’s sensitive data –…

15Jul 2013 by Charlie Nichols 14 Comments

Create a trip calculator with Excel and web services

300 level, Excel

[wp_ad_camp_1] [wp_ad_camp_4] Using Excel, you can create a trip calculator that uses web services to provide dynamic information that is up to date. I created this post after reading a post with a similar theme from Glen Gilchrist. In the example below, you'll see that you can create the trip calculator using Excel functions and without needing to write any code. Note: The illustrations below will work with Excel versions 2003 - 2010 by installing the Excel PowerUps Premium Suite. If you have Excel 2013, you can create this trip calculator without the add-in -- just remove the "pwr" prefix from the function names in the examples below. Excel 2013 already has built in WEBSERVICE and FILTERXML functions! Here's the gist of how we'll put this together For the calculator,…

30May 2013 by Charlie Nichols 14 Comments

How to create a resource utilization chart in Excel

300 level, Excel

[wp_ad_camp_1] [wp_ad_camp_4] "Resource utilization charts" The name I use to refer to these charts is “resource utilization chart”. I use this name because that’s what I’ve come to know them as within Microsoft Project. They may have another, better suited name. If so feel free to note that in the comments below. Edit: Resource utilization histogram Going for a PMP certification? You'll need A Guide to the Project Management Body of Knowledge: PMBOK(R) Guide. Strategy for creating the resource utilization chart The general strategy here is to use a stacked column chart, and define a pair of calculated fields from the data that will actually be charted. I explain that more below, and use a scenario that may be similar to one of yours to help make it clear. So,…

24May 2013 by Charlie Nichols 1 Comment

Fuzzy search in Excel to find similar text values in Excel

300 level, Excel

[wp_ad_camp_1] [wp_ad_camp_4] Sometimes you have a need to compare text strings that don't exactly match. You might need to match "(425)555-1212" to "4255551212" for example. Or perhaps you'd like to match "12345 Main st" to "12345 main street". The Excel PowerUps Premium Suite add-in (available as a free trial download) includes a function that helps you do just that by enabling a fuzzy search in Excel. Fuzzy Search and Replace If you want to do a fuzzy search and replace you can use the Fuzzy Find and Replace tool. You can see an example of it's use here. Fuzzy Search in Excel with a New Function for your Formulas The function is called pwrSIMILARITY. It simply compares the two text strings and returns a percentage value that represents how similar the…

24May 2013 by Charlie Nichols No Comments

How to generate a random date between two dates

300 level, Excel

How do you generate a random date between two dates? I was asked how to generate a set of random dates between two dates. At first glance, it didn't look like Excel had a function that would easily allow that. However, it turns out that using the RANDBETWEEN function is perfectly suited for this. Sample formula You will use the DATE function to specify your start and end dates for the range. Let's say you want to generate a date between January 1, 2013 and December 31, 2013. The function would look like the following. =RANDBETWEEN(DATE(2013,1,1),DATE(2013,12,31)) How does this work? This works because the DATE function returns a number that represents the date value in Excel. Then RANDBETWEEN generates a random number between those two date values (or numbers). The…

10Apr 2013 by Charlie Nichols No Comments

How to determine whether a given year is a leap year

300 level, Excel

Identify a leap year in Excel: We all know what a leap year is. Every 4 years (almost) we get a February 29th. The ‘almost’ is the key thing. There are some conditions (none of which most of us will see in our lifetimes) where a leap year may get skipped. Here’s the algorithm for the determination that I found on Wikipedia. You can also read up on the history and such of the leap year there as well if you like. If the Year is divisible by 400, it IS a leap year.Otherwise if the year is divisible by 100, it’s NOT a leap year.If none of the above, and the year is divisible by 4, it IS a leap year.Otherwise it is just NOT a leap year. How…

09Apr 2013 by Charlie Nichols 3 Comments

How to find the number of months between two dates in Excel

300 level, Excel

[wp_ad_camp_1] [wp_ad_camp_4] Number of Months Between Two Dates? How many months are between two dates? The answer is “it depends” because your real question might be one of the following: How many months are between two days where the number is expressed as a fractional number?How many months are between two days where we’re only counting whole months?How many months are between two days rounding half months up to the nearest whole month?How many months are between two days where a fractional month counts as a whole month?How many months are between two days where days in any given month count as a whole month?As we’ll see below, the answer to each of the questions is different. And in your case, the specific question you’re really asking may be one…

04Apr 2013 by Charlie Nichols No Comments

How to find values in one set that are duplicated in another set in Excel

300 level, Excel

[wp_ad_camp_1] [wp_ad_camp_4] Ok, you have a set of values in a column or a row and you want to find out if any of those values exist in another list (which could be in either a row or a column elsewhere). You can do that in Excel by using the MATCH function to test for a specific value in another range. We’ll look at that below. Worksheet setup First, let’s set up a worksheet example. You can replace with any values you like of course. Here’s a list we want to find duplicates from. Think of this as your ‘Test’ or source list. Red, Pink, Purple, Blue Here’s a list that we want to look into. Think of this as our ‘Master’ list. Red, Orange, Yellow, Green, Blue, Indigo, Violet…

21Mar 2013 by Charlie Nichols No Comments

Do you need your VLOOKUP to look to the left instead of the right?

300 level, Excel

[wp_ad_camp_1] [wp_ad_camp_4] I've used VLOOKUP for many many years. It's a straightforward enough concept: scan down a column for a match in a cell, then look over to the right in that row for the value we want to retrieve. And, all of this is wrapped into a single function call: VLOOKUP. But you may have found VLOOKUP has a particular limitation. It can only look to the right of the cell you've matched. If you've been able to plan ahead and lay out your data so that the column you're scanning is to the left of the range of information you're looking for, you're in luck I suppose. But if you're like me and your data is provided to you from another source or you don't always envision every…