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?

Share and Enjoy

  • Facebook
  • Twitter
  • LinkedIn
  • Google Plus
  • Pinterest

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

Share and Enjoy

  • Facebook
  • Twitter
  • LinkedIn
  • Google Plus
  • Pinterest

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

Share and Enjoy

  • Facebook
  • Twitter
  • LinkedIn
  • Google Plus
  • Pinterest

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

Share and Enjoy

  • Facebook
  • Twitter
  • LinkedIn
  • Google Plus
  • Pinterest

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

Share and Enjoy

  • Facebook
  • Twitter
  • LinkedIn
  • Google Plus
  • Pinterest

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

Related Posts Plugin for WordPress, Blogger...

Share and Enjoy

  • Facebook
  • Twitter
  • LinkedIn
  • Google Plus
  • Pinterest