Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • How to create Gauge Chart in Excel
  • How To Compare Two Lists in Excel
  • How To Create Pareto Chart in Excel
  • How to do a t-Test in Excel?
  • Everything about Charts in Excel

References

  • VLOOKUP function: Description, Usage, Syntax, Examples and Explanation
  • How to get relative column numbers in a range in Excel
  • Count rows that contain specific values in Excel
  • Multi-criteria lookup and transpose in Excel
  • How to get last row in text data in Excel

Data Validations

  • Excel Data validation no punctuation
  • Prevent invalid data entering in specific cells
  • Excel Data validation date in next 30 days
  • Excel Data validation only dates between
  • Excel Data validation must not contain

Break ties with helper column and COUNTIF in Excel

by

This tutorial shows how to Break ties with helper column and COUNTIF in Excel using the example below;

Formula

=A1+(COUNTIF(exp_rng,A1)-1)*adjustment

Explanation

To break ties, you can use a helper column and the COUNTIF function to adjust values so that they don’t contain duplicates, and therefore won’t result in ties. In the example shown, the formula in D5 is:

=C5+(COUNTIF($C$5:C5,C5)-1)*0.01

Context

Sometimes, when you use functions like SMALL, LARGE, or RANK to rank highest or lowest values, you end up with ties, because the data contains duplicates. One way to break ties like this is to add a helper column with values that have been adjusted, then rank those values instead of the originals.

Worked Example:   Example of COUNTIFS with variable table column in Excel

In this example, the logic used to adjust values is random – the first duplicate value will “win”, but you can adjust the formula to use logic that fits your particular situation and use case.

How this formula works

At the core, this formula uses the COUNTIF function and an expanding range to count occurrences of values. The expanding reference is used so that COUNTIFS returns a running count of occurrences, instead of a total count for each value:

COUNTIF($C$5:C5,C5)

Next, 1 is subtracted from the result (which makes the count of all non-duplicate values zero) and the result is multiplied by 0.01. This value is the “adjustment”, and intentionally small so as not to materially impact the original value.

Worked Example:   Count dates in current month in Excel

In the example shown, Metrolux and Diamond both have the same estimate of $5000. Since Metrolux appears first in the list, the running count of 5000 is 1 and is cancelled out by subtracting 1, so the estimate is remains unchanged in the helper column:

=C8+(COUNTIF($C$5:C8,C8)-1)*0.01
=C8+(1-1)*0.01
=C8+0
=C8

However, for Diamond, the running count of 5000 is 2, so the estimate is adjusted:

=C11+(COUNTIF($C$5:C11,C11)-1)*0.01
=C11+(2-1)*0.01
=C11+1*0.01
=C11+0.01

Finally, the adjusted values are used for ranking instead of the original values in columns G and H.  The formula in G5 is:

=SMALL($D$5:$D$12,F5)

The formula in H5:

=INDEX($B$5:$B$12,MATCH(G5,$D$5:$D$12,0))

Post navigation

Previous Post:

Weighted average in Excel

Next Post:

Win loss points calculation in Excel

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others

Logical Functions

  • SWITCH function example in Excel
  • Nested IF function example in Excel
  • IF with boolean logic in Excel
  • OR function Examples in Excel
  • Not Equal To ‘<>‘ operator in Excel

Date Time

  • Convert Excel time to Unix time in Excel
  • Get week number from date in Excel
  • Display Date is same month in Excel
  • Pad week numbers with zeros in Excel
  • Check If Two Dates are same month in Excel

Grouping

  • Categorize text with keywords in Excel
  • Map text to numbers in Excel
  • How to randomly assign data to groups in Excel
  • Group numbers with VLOOKUP in Excel
  • Group numbers at uneven intervals in Excel

General

  • How to calculate project complete percentage in Excel
  • Sum by group in Excel
  • How to count total columns in range in Excel
  • How to calculate percent sold in Excel
  • Delete Blank Rows at Once in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning