Archive for October, 2017

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 »

How to Distribute Players Between Teams – Evenly

Published on Oct 12, 2017 in Excel Howtos, Huis, Posts by Hui, Solver
How to Distribute Players Between Teams – Evenly

Learn how to use Solver to allocate players evenly to Teams.
A Solver Tutorial.

Continue »

Conditional Formatting – Chart Data Labels

Published on Oct 9, 2017 in Charts and Graphs, Excel Howtos, hacks, Huis, Posts by Hui
Conditional Formatting – Chart Data Labels

Learn how to conditionally format Chart Data Labels without VBA

Continue »

Histograms & Pareto charts in Excel – tutorial, tips and downloadable template

Published on Oct 5, 2017 in Charts and Graphs
Histograms & Pareto charts in Excel – tutorial, tips and downloadable template

Time for some statistics and charting fun. Let’s learn all about histograms and Pareto charts in Excel 2016. You will learn

  • What, why and when?
  • How to set up and customize histograms
  • How to use Pareto charts?
  • How to create dynamic histograms?
  • Creating histograms in old Excel (2013 or prior versions)

Sounds interesting? Let’s get started then.

Continue »

Find out if a number has repetitive digits [formula homework]

Published on Oct 3, 2017 in Formula Challenges
Find out if a number has repetitive digits [formula homework]

Time for a quick formula finesse check. Let’s say you have a number in A1. What formula can you use to find out if it has duplicate digits.

For example, if A1 has 123405, then answer should be FALSE

and if A1 has 123455, then answer should be TRUE

Go ahead and post your answers (formulas, VBA or M script) in the comments section.

Continue »