Product Comparison Based on Importance of Criteria

/in /by

File download: http://datascopic.net/ComparisonTool

A friend was torn between 1. a personal massager that was really nice but had to be ordered online and 2. an ok massager that was at a nearby store.

In this video, we build a tool in Excel to compare several massagers based on multiple criteria and varying levels of importance. You will experience:

1. The thought process
2. Getting the math right
3. A way to break tied scores
4. Use of an icon to flag the top choice

Excel: Can a sheet be deleted safely?

/in /by

Here’s a problem:
You’re working with a workbook and need to delete some worksheets, but it’s not clear if deleting a sheet is going to crash formulas on other sheets.

Here, the Inquire add-in gives us a diagram showing what’s tied to what.

http://datascopic.net/cussin-calc

Cussin’ Calculator: Using Excel’s SUMPRODUCT

/in /by

Download the Cussin’ Calculater here:

PROBLEM: How can you assign numerical values to text and have Excel generate a sum based on entry of the text?
ANSWER: SUMPRODUCT

Here’s how to tally the amount of cussin’ your kids are doing.

Download the spreadsheet here:

Distinguish Between Bold and Non-Bold Text

/in /by

Here’s something really quick, and no frills.
Someone asked about writing an IF statement to distinguish between bold and non-bold text.

No, you can’t write an IF statement. But you can take advantage of 2 features:

1. Find/Replace can find the bold and replace with a colored fill
2. Excel can sort and filter by the colored fill.

Google Sheets and Excel: Head-to-Head

/in /by

Here is head-to-head comparison of Google Sheets (GS) and Excel, and how they handle a few basic data-related needs. We look at:
– Input Controls (Data Validation)
– Pivot Tables
– Pivot Table Calculated Fields

My concern is that the people who tout GS as a legitimate Excel replacement really don’t know what they don’t know. They probably don’t know that it can help them to control cell inputs–something that GS does very poorly (unless you write JavaScript code).

These are basics. See for yourself.

Excel Unpivot and Query to Match Canadian Area codes w/Provinces

/in /by

Classic use of VLOOKLUP is interrupted by the fact that we don’t have a master list to retrieve our values from. We just have 1300 names and area codes.

Ok! We can create a master list using Text-to-Columns, and Power Query’s Online Search and UnPivot.

You’ve got to check out UnPivot!

In The Band Using Excel to study song structure

/in /by

My daughter Jekyll has joined a band. One thing that young bands struggle with is everyone’s desire to play all the time.

In this video, we study the song Pressman, by Primus. They are 3 guys (Les Claypool, Tim Alexander, Larry LaLonde) who are a brilliant example of a small band that makes clever use of instrumentation to generate endless ideas.

I map the song out in Excel to show the unique features in the instrumentation and arrangement.

Excel Tables & The Ruined Birthday Party

/in /by

I tried to have a birthday party for my son, and it was ruined by all the bad kids that were invited.

Easy use of excel tables and pivot tables helps tally what’s owed by each parent–whether they had 1, 2 or 3 kids at the party.

One parent convinces me to factor in the cost of the gift one of his children brought.

Excel Helps Determine the Dog’s Fate

/in /by

This is an example of Excel used with no formulas. There’s simple use of basic features to get at some important details: did the children to enough to keep the dog?

You see: tables, pivot tables, pivot charts

The trick is in the layout. All of the data is in 3 columns. Notice that walks and baths aren’t separated. Everything is in one range.