All articles with 'rank()' Tag

Which two teams qualify to next stage? [Excel Homework]

Published on Jun 15, 2018 in Excel Challenges
Which two teams qualify to next stage? [Excel Homework]

Time for some soccer fun in spreadsheets. Given a team points table like above,

How would you figure out in Excel, which teams qualify for next stage. The rules for this exercise are,

  • Pick the top two teams by points
  • If there is a tie, use Goal Difference (GD) to break ties (more GD is good)
  • If there is a tie, use Goals For (GF) to break ties
Continue »

Conditional Rank, the easy way [quick tip]

Published on Oct 20, 2017 in Pivot Tables & Charts
Conditional Rank, the easy way [quick tip]

Yesterday, my mate from across the ditch, Hui posted about conditional rank formula (RANKIFS) using awesome SUMPRODUCT

Of course, not everyone can whip up a sumproduct formula like that. On a scale of One to Hui of Excel awesomeness, you would need to be at least an H to write sumproduct or countifs formulas shown in that post. So does it mean, you can’t conditional rank if you don’t know your X from L?

Don’t worry. We got you covered. You can still get your conditional ranks, without inception level array formulas. Simple, use pivot tables instead.

Continue »

Formula Forensics 043: Rankifs or Conditional Rank

Published on Oct 19, 2017 in Formula Forensics, Huis, Posts by Hui
Formula Forensics 043: Rankifs or Conditional Rank

Learn how to develop a Conditional Rank or Rankifs formula in Excel

Continue »

Mix VLOOKUP with Data Validation for some magic! [VLOOKUP Week]

Published on Nov 1, 2010 in Learn Excel
Mix VLOOKUP with Data Validation for some magic! [VLOOKUP Week]

Situation

Sometimes we don’t know what we want. If this happens when I am in a bar, I usually order a cocktail. Just a mix of everything. The same will work in Excel too.

For eg. If you have lots of data, but the value you want to look up needs to change based on whims and fancies of your users, then you can resort to a cocktail. A mix of VLOOKUP with Drop down lists (Data validation)

Read more to find how to solve this.

Continue »