VLOOKUP w/TRUE to assign dates in custom periods

/in /by

Here’s an example of using True in a VLOOKUP because an approximate match is required.

We have a list of dates, and the company uses custom start and end dates to mark monthly periods. Here, “June” is officially 26MAY thru 24JUN.

In order to get 26MAY to show up as June, we use:
=VLOOKUP(A2,$G$2:$H$13,2,TRUE)

IMPORTANT NOTE: I explain the search incorrectly. It’s a common myth that the True starts at the top and goes until it finds the next highest entry. The real explanation is found here:

True conducts a “binary search” as I explain in the other video.

Sampling Jim’s Habanero Ghost Cheese

/in /by

Oz du Soleil samples Habanero Ghost Pepper Cheese from Jim’s Cheese Pantry
This is a fantastic cheese that balances the heat of the peppers, makes excellent use of the ghost peppers inside of a cheese that stands up and matches well with the peppers.

An all-around excellent experience. Add some red wine, and we’ve got LIVE ACTION!

Generating Random Data to Set Up a Pivot Table Tutorial

/in /by

Blogpost associated with this video: http://datascopic.net/generate-data/

Often we need to generate a set of bogus data in order to create examples or test solutions. In this video, I show how to generate fake call center data in order to show an example of a pivot table.

This includes: RAND, RANDBETWEEN, date formatting, Find-Replace

Basic Excel Table Demo

/in /by

Simple use of Excel Tables

Basic Demo of Excel Tables to Warn of Upcoming Deadlines

/in /by

Quick video to show the use of Excel tables, and how a table can be compromised.

http://www.DataScopic.net

Excel Inventory Control System DEMO

/in /by

This video shows an Inventory Control System that was built in Excel. The purpose is to demo the various layers to consider in creating such a document:
– Distinct Incoming, Outgoing and Inventory Sections
– Required fields so that complete info is entered
– Alerts that give users an idea of anything that’s missing
– Protected cells so that formulas can’t be accidentally corrupted
– Prevention against a user requesting something that isn’t in inventory
– Prevention against a user requesting more than what is in inventory
– Use of dependent drop lists to control input and output

I don’t go into the “how-to” but if anyone is interested, please let me know.

NOTE: Several people have contacted me about this document.
I’ve made it available for download at a low price at http://datascopic.net/downloads/inventory/

http://datascopic.net/question/top-5-excel-functions-vlookup/

VLOOKUP: compare lists and retrieve information

/in /by

I was asked for my Top 5 Excel Functions. Number 2 is VLOOKUP.
This video goes along with a blogpost on my site:

In this video I show 2 ways that VLOOKUP can be used:
1. How do we determine which of our members HAVE NOT replied to their invitations?
2. How to retrieve data from a master list to round out an incomplete list

Kurt & Oz Making Ice Cream with Liquid Nitrogen

/in /by

This afternoon Kurt and I experimented with liquid nitrogen, habanero-infused vanilla extract and coffee beans.

The video starts with the end of the coffee bean roasting. In order to get the beans to stop roasting and not get too bitter, they’re normally taken off the stove and dipped into an ice bath. Today, we poured liquid nitrogen on them. Kurt will deliver the taste test results later.

The ice cream was delicious. The habanero flavor was completely missing but the bourbon and vanilla flavors were nice.

We’re planning to do it again, folks!

Response2Alyssa

/in /by