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.
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.
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
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)
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.
That's it for this week! If there are topics that you'd like to see covered in future emails,
please let me know.
ddalgleish @ contextures.com
Note: I am an affiliate for some of the products mentioned in this email, and earn a commission on the sales.
Last updated: March 15, 2017 11:57 AM