Excel Tools, Models, Tips, and Tricks

Non LOOKUP Alternative to Nested IF

Brian Cohen — Mon, 10 Dec 2012 18:11:46 +0000

You want to know if an input contains the value 1, 3, or 5. If any of those values are contained within the input, you want to see “YES” as output. Below, A2 contains the input and B2 displays the output:

The common method calls for a nested IF statement, like this:

=IF(A2=1,”YES”,IF(A2=3,”YES”,IF(A2=5,”YES”,”NO”)))

Another common technique calls for VLOOKUP or MATCH functions, with the values 1, 3, and 5 contained within a list.

Here’s a simpler approach. Utilize the OR function in conjunction with an array.

In C2, type:

=IF(OR(A2={1,3,5})=TRUE,”YES”,”NO”)

The formula checks for the values 1, 3, and 5 in one motion.

Brian Cohen
brian@briancohenconsulting.com

Convert Decimal to Time

Brian Cohen — Wed, 21 Nov 2012 13:30:48 +0000

Here’s a technique to convert a decimal into time.

This isolates the integer, 6. Then it deals with 0.4 separately, converting it into minutes or hours.

Brian Cohen
brian@briancohenconsulting.com

Fill Cells with Last Non-Blank Entry

Brian Cohen — Tue, 20 Nov 2012 13:46:40 +0000

Let’s say you want to turn this:

into this:

Your challenge is to fill the non blank cells with the last entry. Here’s the best technique:

Select Cells B3:B19.
Press Control-G.
Click “Special” and select “Blanks.”
Type: =
Press the up-arrow.
Press Control-Enter
You did it! Now Paste-values to overwrite formulas.

That technique works well when no formula is required. Here’s a formula-based technique:

In A3, =B3
In A4, =IF(ISBLANK(B4),A3,B4)
Fill down.
You did it! Now Paste-values to overwrite formulas.

Brian Cohen
brian@briancohenconsulting.com

Dates to Text, Maintaining Chronology for Pivots

Brian Cohen — Wed, 13 Jun 2012 12:13:54 +0000

Converting complicated date and time stamps into a simplified form is a common task among intermediate-level users. For example, it may be desirable to simply this….

…to this:

The common conversion technique calls for the =TEXT formula.

For many purposes, that technique works fine. For supplying pivot table source data, however, the method is frustrating. Dates appear out of sequence. The formula converts the stamps into strict text which becomes alphabetized, like this:

June appears before May since the text is displayed in alphabetical order. That’s not helpful — we’d like to see May appear prior to June. My solution modifies the common formula with =RIGHT, as follows:

The pivot table results now appear as follows:

My formula places the actual date before the text-converted date, preserving the chronology. The =RIGHT addition provides for the text portion of the string to be visible, hiding the rest.

Brian Cohen
brian@briancohenconsulting.com

Named Formulas as Nested Shorthand

Brian Cohen — Sat, 21 Apr 2012 13:39:33 +0000

Utilizing Named Formulas can be a sensible alternative to confusing nesting. The following example demonstrates the technique in the context of a text extraction challenge.

Below we see a list of URL’s.

Suppose we want to extract the four-digit codes following “3D” of each URL. For instance, we’d like to pull out “3173″ from cell A1 below.

One possible formula to extract the code from cell A1 is as follows:

=LEFT(RIGHT($A1,LEN($A1)-SEARCH(“%”,$A1)-2),4).

This formulas may be used for cells A3, A4, and A6 as well since those URL’s share the same text pattern.

The pattern for cells A2, A5, and A7 is different. A new formula is required. To extract the code from those cells, I use

=RIGHT($A2,4).

Let’s name the first formula “PATTERN_1″ and the second formula “PATTERN_2.”

Open the Name Manager.

Name the formulas for Pattern 1 and Pattern 2 PATTERN_1 and PATTERN_2, respectively.

Here’s a closer look.

Write a formula in Column B to identify the pattern in Column A. When the pattern is identified, execute the corresponding Named Formula. Here’s how it all looks:

The formula’s shown above, in Column B, look for the percent sign (%) as a trigger to identify Pattern 1. If Pattern 1 is identified, the Named Formula PATTERN_1 is executed.

If the percent sign is not found, a #VALUE error would result. We use IFERROR to execute PATTERN_2 when the percent sign is not found.

Have fun!

Brian Cohen
brian@briancohenconsulting.com

Column Sort like a Pro

Brian Cohen — Fri, 09 Mar 2012 17:23:57 +0000

We typically sort rows, not columns. But sometimes a good column sort can be “in order.”

Let’s say you routinely import raw data which span twenty or more columns. Perhaps the column ordering isn’t optimal, so you start off by cutting and pasting to move columns around. Here’s another way to approach your solution.

Here are sample data spanning six columns.

We don’t like the column order. We prefer the order Name, Address, Phone, City, State, Zip, and Phone.

Start by creating a lookup table as follows. Input the desired column numbers adjacent to the column headings.

Name the range in the Name Manager. We call it “COLUMN_LOOKUP.”

Insert a row above the column headers.

In A1, lookup the desired column number corresponding to “Zip,” as shown below.

Fill the formula right.

The desired column numbers now correspond to column headers. It’s time to sort.

By default, sorting occurs by rows. To sort columns, click “Options.”

Select “Left to Right.”

Sort by “Row 1″, “Smallest to Largest.”

The columns are sorted as follows:

We can now delete Row 1 for cleanup.

Brian Cohen
brian@briancohenconsulting.com

Dynamic Pivot Table Range

Brian Cohen — Sat, 25 Feb 2012 02:01:33 +0000

Pivot tables can be nagging pains when source data change frequently. For instance, if the number of source rows increase by ten every day, then daily you may manually edit the source range to accommodate ten more rows. As an alternative, you could reference entire columns, but your pivot table results will not be clean. You will find “blank” entries. This makes people think that Excel is really in charge, not you.

The solution is to reference your source data as a dynamic range. The source data range will decrease or increase in size as necessary.

For example, here are initial raw data:

and this is your pivot table….

The pivot table’s reference to the raw data is Sheet1!$A$1:$B$5.

Now, let’s say the raw data expands. Additional data were added in rows 6 through 12.

At this point you could manually modify the source to Sheet1!$A$1:$B$12. Or you could change the source to Sheet1!$A:$B and settle for blanks, like this:

…you don’t want that. “But I can filter the rows to exclude the blank.” Stop, no more of that. Time to do it the right way.

Open the Name Manager.

Create a new name. Call it RANGE. Define RANGE as shown below.

Note that Sheet 1 contains the raw data.

As shown above, the range titled “RANGE” was defined as follows:

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),2)

The OFFSET function defined a dynamic range. In this example, the range begins zero rows and zero columns from cell A1. The COUNTA function counts the number of nonblank rows appearing in Column A. The number two listed at the end of the formula represents the number of columns to include.

Putting it all together, the named range titled “RANGE” was defined as an array starting exactly at cell A1, moving down the sheet, ending at the number of nonblank rows in Column A. The range “RANGE” expands a total of 2 columns, encompassing columns A and B.

Now I set the pivot table’s data source to =RANGE.

Refreshing the pivot table, the new data appear.

Now, each time the pivot table is refreshed, it will automatically expand or contract according the array of raw data.

Brian Cohen
brian@briancohenconsulting.com

Input-Controlled Conditional Formatting

Brian Cohen — Sat, 11 Feb 2012 19:10:22 +0000

Question: How can I use inputs to control conditional formatting?

The following example concerns advertising placements. Raw data are dumped into Columns K, L, M, N, and O.

Placements that incurred costs without producing leads are highlighted blue. The threshold triggering the conditionally formatted blue fill is controlled by an input. The purple functions similarly. Here is a view of the input controls and the corresponding formulas.

The conditional formatting rules manager:

Detail on the “Blue” conditional format:

Detail on the “Purple” conditional format:

Brian Cohen
brian@briancohenconsulting.com

Directory of Lookups

Brian Cohen — Tue, 07 Feb 2012 16:18:53 +0000

Question: What’s the best way to return lookup values from multiple tables (without going crazy from confusion)?

Answer: Create a “directory” of lookup tables. Write a nested lookup utilizing the INDIRECT formula. Here’s a generic example.

In the table below, the number of items sold per category per region were reported.

The values were returned from individual tables corresponding to regions.

The tables were named as follows:

with the named range DIRECTORY_OF_TABLES shown below.

First, we’ll return the value “23″ corresponding to the item “Sunglasses” from the “East” table:

The formula is shown below. The INDIRECT formula was nested within a VLOOKUP.

The rest of the table is completed as follows with some of the formulas shown:

Brian Cohen
brian@briancohenconsulting.com

Weighted Rankings

Brian Cohen — Sat, 04 Feb 2012 14:30:17 +0000

Question: What’s the handiest method of ranking elements based on weighted scores?

In this generic example, four employees are ranked based on four criteria: looks, accuracy, attitude, and revenue. Is that fair?

The weights are arbitrary inputs. I collectively assigned the inputs a named range: WEIGHTING_SCHEME.

Here is a view of the weighting scheme with the raw scores, weighted composite scores, and ranks.

As you can see, Katie outranked Kevin, John, and Marissa. Her looks were key.

I named other ranges, too. Take another look at the Name Manager:

Here is a view of the formulas:

I name cells and ranges often throughout my work. I find it convenient to keep track of what’s what.

An alternate method is through use of SUMPRODUCT. Here’s how:

The SUMPRODUCT formulas produce identical calculations.

Brian Cohen
brian@briancohenconsulting.com