Football Fun in Excel

This Sunday is Super Bowl XLIX – the last one that will start with "XL".  Next year, it will be Super Bowl L – that doesn't exactly roll off the tongue!

In 2011, I showed how you can use the ROMAN function in Excel, to change a number into a Roman numeral. That year, they were playing for the 45th time, which was XLV.

=ROMAN(A2)

Other Football Functions

There are other Excel functions that sound like they could be used in a football game. Here are a few, and I'm sure you can think of others:

CONVERT RECEIVE YIELD

Continue reading Football Fun in Excel

Count Activities in a Date Range

guests date range http://blog.contextures.com/

Today's challenge is to count how many guests stayed at a hotel, in a specific date range, based on the guest arrival and departure dates.

(adsbygoogle = window.adsbygoogle || []).push({});

Continue reading Count Activities in a Date Range

Create Random Text in Excel

create random text in Excel http://blog.contextures.com

Last week, I was creating an Excel file with sample data, to use for a few experiments. But don’t worry, they weren’t mad-scientist-type experiments – I was doing Power Pivot experiments, and needed some data to play with.

I needed 2 types of data:

Numbers: sample test scores in one column Text: random Region names and Gender in other columns.

Continue reading Create Random Text in Excel

Set Row Height With REPT Function

set minimum row height

Thanks to AlexJ for suggesting a great use for the REPT function – setting a minimum row height. He uses this technique to add a bit of spacing in his tables, so they’re easier to read.

You can watch the steps in this video (or watch it on YouTube), and the step-by-step instructions are below the video.

Add Space in an Excel List

For example, here is my To Do list, with a few items to work on, around the house. Most of the Task Descriptions are short, and fit in a single line.

Continue reading Set Row Height With REPT Function

Auditing Excel Formulas Quick Tips

closed door brock's monument

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

Create IFRAME Code in Excel

iframe code in Excel

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

SHEET and SHEETS Functions in Excel 2013

sheets function

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

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