Which Excel Function Are You?

If you’ve been anywhere online in the past couple of years, you’ve probably seen those quizzes, such as Which Star Wars Character Are You? 

Now, it’s time to play a new game – Which Excel Function Are You?

We don’t need an online service to create our quiz – we can build it in Excel.

Pick the Five Functions

Most of these games have 5 or 6 possible outcomes, so I’ll pick 5 Excel functions that have distinctive personalities.

ROUND VLOOKUP MATCH EXACT COUNT

On a worksheet named Admin_Data, I created a table with a list of the

Continue reading Which Excel Function Are You?

Find Last Item in Group With Index Match

How can you use a formula to find the last item in any category, in a sorted list? Someone asked me that question last week, and I used a combination of INDEX and MATCH to find the solution.

To show how I solved it, I’ve set up a small table, with food products, sorted by category and product code. The product codes have two letters, followed by three numbers. The table is named tblProducts.

NOTE: If the product codes aren’t sorted, this solution won’t work.

Step 1: Find the Category Start

Cell B2 contains the category name

Continue reading Find Last Item in Group With Index Match

Using MAX IF With Multiple Criteria

Excel doesn’t have a MAXIF function, but we’re able to create our own version, by combining the MAX and IF functions. But how about a bigger dream? Could we create our own MAXIFS function too, with multiple IFs?

Using MAX and IF

Last week, we used MAX and IF in an array-entered formula, to find the latest date that a product price was changed. Remember to press Ctrl + Shift + Enter after typing this formula, instead of just pressing Enter.

=MAX(IF($A$2:$A$9=A12,$B$2:$B$9))

Note: For more information on array formulas, I recommend Mike Girvin’s book, Ctrl+Shift+Enter: Mastering Excel Array Formulas.

Continue reading Using MAX IF With Multiple Criteria

Get Latest Price for Specific Product

In a previous article, we combined the MAX and IF functions, to find the highest price for a specific product.

Today we’ll use that technique to find the latest date that a product’s price was changed. Then, using the product name and that date, we can find the latest price for the product – even if it isn’t the highest price.

Product Pricing Table

In this example, there is a price list in cells A1:C9. There are three columns: Product, Date and Price.

Find the Latest Date

If we want to find the latest price for Pens,

Continue reading Get Latest Price for Specific Product

Count Items in a Date Range in Excel

If you have a list on your worksheet, and a date in each row, you can get Excel to count how many dates are in a specific date range. For example, how many Pencil shipments went out in September, based on the Ship Date column?

Use COUNTIFS and Cell References

The easiest way to find the number of shipments is to use the COUNTIFS function (Excel 2007 and later) and enter the product name, and the start and end dates on the worksheet.

In this example, there is a drop down list of products in cell A2. The

Continue reading Count Items in a Date Range in Excel

Check Winning Numbers with COUNTIF Function

Today, we’ll see a little magic from the COUNTIF function – it can do the work of seven formulas in one!

The COUNTIF function can check a range of cells, and see if it contains a specific value. For example, type your lucky number in cell D2, and use a COUNTIF formula to see if that number is found in the list of winning numbers in cells I2:N2.

=COUNTIF(I2:N2,D2)

The number, 26, is found once in the range I2:N2, so the COUNTIF result is 1. Your lucky number is a winner!

Check Multiple Numbers

What if we have multiple

Continue reading Check Winning Numbers with COUNTIF Function

Change Functions with AGGREGATE in Excel

A couple of years ago, we looked at the Excel SUBTOTAL function, and saw how you could allow users to select the function they want. In the example shown below, the total formulas are controlled by the drop down list at the top of the sheet.

In Excel 2010 the AGGREGATE function was introduced, and it’s like a supercharged version of SUBTOTAL. There is a longer list of functions, plus options on what data, if any, to ignore.

For some of the functions, you can use the Reference form, with these arguments:

AGGREGATE(function_num, options, ref1, [ref2], …)

For a

Continue reading Change Functions with AGGREGATE in Excel

Switch Languages in Excel Workbook

Our two official languages in Canada are English and French, and I’m currently working on an Excel project in which you can choose either language.

The data in my client’s file is confidential, so I’ll show you how this works using some text from a Honey Nut Cheerios box. Actually, this is how many of us learned some French – reading the back of the cereal box at the breakfast table!

Set up the Translation Table

In the sample Excel file, I set up a table with 3 columns – ID, English and French. I entered a few English and

Continue reading Switch Languages in Excel Workbook

Array Formulas Book Giveaway

It’s August already! How did that happen? Did you know that August is not only a month name, it’s an adjective that means “inspiring reverence and admiration; venerable, impressive”.

One way to build admirable Excel skills is to attend Chandoo’s online PowerPivot course, and enrolment opens today. In addition to the basic course, there is a new advanced level course, taught by Rob Collie, who used to work on Microsoft’s PowerPivot team.

In Excel, it’s also impressive to know how to use array formulas. If you’d like to develop august array skills, here’s your chance to win an e-book

Continue reading Array Formulas Book Giveaway

See Formula Results in an Excel Data Table

Do you ever use the Data Table feature in Excel? It’s one of the “What-if Analysis” tools, found on the Ribbon’s Data tab, along with Scenario Manager, and Goal Seek.

A data table lets you experiment with one or two variables in a formula, and see the results, in a compact layout.

Get Ready to Build a Data Table

Before you can build a data table, you need a formula on your worksheet. It should use cell references in its arguments, like this PMT function, in cell C7.

The variables are in cells C2, C3, and C4, and

Continue reading See Formula Results in an Excel Data Table

Related Posts Plugin for WordPress, Blogger...