For the past couple of weeks I’ve had lots of trouble with this blog. It’s been up and down, and I’m sorry if you’ve come to visit, and found the doors closed! The sturdy-looking door shown below is at Brock’s Monument, in Niagara-on-the-Lake, which I visited earlier this month.
So, I’ve turned off a few things on the blog, like subscribing to comments, to see if that helps. Maybe Dick Kusleika, at Daily Dose of Excel, has some tips for me, if he’s sorted out his blog problems.
Continue reading Auditing Excel Formulas Quick Tips
If you want to embed a YouTube video on your website, YouTube will create some IFRAME code for you. If you want to embed content from somewhere else, you might have to create your own IFRAME code, or find a free generator somewhere in the interwebs.
But now you won’t have to wander the Internet hallways, looking for a solution, dodging scam artists at every intersection. Instead, you can download my sample file, and create your code in the safety of your own Excel file.
Continue reading Create IFRAME Code in Excel
I’ve been experimenting with the new SHEET and SHEETS functions in Excel 2013, to see how they could be used.
The SHEETS function counts all the sheets in a reference The SHEET function returns the sheet number for a reference
You can see a demo of these functions in the video at the end of this post.
How Do You Use These Functions?
Have you found any practical uses for these functions? I came up with a few formulas that let you do some troubleshooting.
Continue reading SHEET and SHEETS Functions in Excel 2013
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?
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
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.
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
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
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
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.
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
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], …)
Continue reading Change Functions with AGGREGATE in Excel