Contextures

Contextures News

Excel RANK IF Formula

March 7, 2017

RANK IF formula, and more, in this week's Excel news. Visit my Excel website for many more tips, tutorials and videos -- let me know if you're looking for something and can't find it there.

Sheet Lister

If you have Excel workbooks with lots of sheets, it can be a pain to find the sheet that you need. You might not remember what sheets are in the file, or exactly where they are.

I have that problem with a couple of my workbooks, so I built a free add-in that stays open and shows a list of sheets in the active workbook. List the sheets in their workbook order, or A-Z.

Get a copy on the Excel Sheet Lister Add-in page on my Contextures website.

custom tab on ribbon

RANK IF Formula

The RANK function can compare any number to a list of numbers, and show its rank. But how could you rank each day's sales, compared to other sales in the same week.

Excel has SUMIFS, and a few other "IFS" functions, but no RANKIF. However, you can use COUNTIFS to do the ranking. (In older versions of Excel, use SUMPRODUCT.)

This formula checks for other sales with the same week number (Wk), and gets a count of items with a larger amount in the Sales column. Then, 1 is added to that number, to get the ranking. So, if there are 2 rows with a larger amount in the same week, the rank would be 3.

=COUNTIFS([Wk], [@Wk], [Sales], ">"&[@Sales])+1

To download the sample file, click here. Also, check out the Count Functions and RANK Function pages.

RANK IF with COUNTIFS

Excel Articles

Here are a couple of recent Excel articles that you might find useful.

Data Analysis - Paul Overberg, an experienced data journalist, leads this pre-recorded webinar on Excel Basics for Analyzing Health Data. There are links below the video, so check those too. (Level - Intro)

Excel for Engineers - Doug Jenkins posted his blog report from the past year. There's an interactive online Excel file with links to all his articles. Even if you're not an engineer, you'll find topics of interest, such as "Nearest Lookup Function". (Level - Intermediate/Advanced)

Also see: My Excel Products || Excel Events || Weekly Excel Humour

Tired of Winter

The snow is gone, at least for now, and the garden is looking dreary -- the only colour is from the garden tools. They should have gone into the shed for the winter, but this way I'll be able to get right to work, as soon as planting season arrives! The time changes in Canada this weekend, so that's a sign that spring is just around the corner.

weekly photo

That's it for this week! If there are topics that you'd like to see covered in future emails, please let me know.

Debra
ddalgleish @ contextures.com

P.S. You can choose to get the full Contextures news package (news and occasional special announcements), or the basic news package (news only). Click either link to change your news option.

Note: I am an affiliate for some of the products mentioned in this email, and earn a commission on the sales.

 

Search Contextures Sites

Excel Data Entry Popup List

 

 

 

 

Last updated: March 15, 2017 11:57 AM