Break ties with helper COUNTIF and column

Suppose you got a task to adjust the values that contain the ties; what would be your first attempt to break the ties of the given value? If you are wondering about doing this task manually, let me add that you will end up exhausted, and there is a probability that you will not complete this task on time. Moreover, besides consuming a lot of time and energy, it also seems to be a cumbersome task to break the ties in the values manually.

But don’t worry about it because you have just landed on the right article, where you will get concise information to break the ties in just a matter of seconds.

3 Break ties with helper column and COUNTIF1

So without any further ado, let’s get started.

General formulation

The formula for breaking ties in values using column and COUNTIF is as follows.

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

 Explanation of Syntax

You must be aware of the syntax used in the above formula to use it to complete your task.

CountIF: In Excel, the COUNTIF function counts the number of cells that fulfill a specific condition or criteria. Learning how to utilize the COUNTIF Excel function is helpful because we frequently want to know how many objects in a huge collection are alike in a specific manner. The COUNTIF function is commonly used as a fast fix.

  • The plus operator (+): This operator is used to add the values.
  • Minus Operator (-): Use this symbol to subtract any two numbers.
  • Multiplication (*): In this symbol, any two values or numbers will be multiplied.
  • The comma symbol (,): This symbol is a separator that aids in the separation of a list of values.
  • Parenthesis (): This symbol’s primary function is to group the elements.

Summary

To break ties, use a helper column and the COUNTIF function to alter values so that they don’t have duplicates and don’t result in ties. The formula in D5 in the example is:

= (COUNTIF($B$3: B3, B3)-1)*0.01 +B3
3 Break ties with helper column and COUNTIF1

 When you employ SMALL, LARGE, or RANK functions to rank highest and lowest values, you may encounter ties because the data contains duplicates. In this situation, one method for breaking ties is to create a helper column with altered values, then rank those values instead of the originals.

The reasoning used to alter values in this example is random – the first duplicate value will “win,” but you may modify the formula to employ appropriate logic for your specific scenario and use case.

Explanation

This formula relies on the COUNTIF function and an increasing range to count occurrences of values. COUNTIFS uses the expanding reference to deliver a running count of occurrences rather than a total count for each value:

=COUNTIF($B$3: B3, B3)
3 Break ties with helper column and COUNTIF1
= (COUNTIF($B$3: B3, B3)-1)*0.01
3 Break ties with helper column and COUNTIF1

The result is then multiplied by 0.01 after being removed by 1 (which makes the count of all non-duplicate values zero). This is the “adjustment,” It is purposefully minimal to not significantly influence the original value.

“excel” and “ppt” both have the same sales of 500 in the scenario. Because excel is first in the list, the running count of 500 equals 1 and is canceled out by removing 1; thus, the estimate in the helper column remains unchanged:

However, the running count of 500 for ppt product is 2, therefore the estimate is adjusted:

=B7+0.01

Finally, instead of the original values in columns G and H, the corrected values are utilized for ranking. The formula in H3.

=LARGE($C$3:$C$8,F3)
3 Break ties with helper column and COUNTIF1

G3’s formula is as follows:

=INDEX($A$3:$A$8,MATCH(H3,$C$3:$C$8,0))
3 Break ties with helper column and COUNTIF1

Column of temporary assistance

Suppose you don’t want to use a helper column in the final solution. In that case, you can use it temporarily to get calculated values, then use Paste Special to convert values into the original cell range.

3 Break ties with helper column and COUNTIF1

Then you can delete the helper column.

3 Break ties with helper column and COUNTIF1

Related Functions


  • Excel INDEX function
    The Excel INDEX function returns a value from a table based on the index (row number and column number)The INDEX function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the INDEX function is as below:= INDEX (array, row_num,[column_num])…
  • Excel MATCH  function
    The Excel MATCH function search a value in an array and returns the position of that item.The MATCH function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the MATCH function is as below:= MATCH  (lookup_value, lookup_array, [match_type])….
  • Excel COUNTIF function
    The Excel COUNTIF function will count the number of cells in a range that meet a given criteria. This function can be used to count the different kinds of cells with number, date, text values, blank, non-blanks, or containing specific characters.etc.= COUNTIF (range, criteria)…
  • Excel LARGE function
    The Excel LARGE function returns the largest numeric value from the numbers that you provided. Or returns the largest value in the array. The syntax of the LARGE function is as below:= LARGE (array,nth)…
Related Posts

Match Single Criteria in Excel

No repeat statistic based on a single condition is used wildly in daily work, for example, count type of products, the duplicate types are not encountered. This article describes the way of no repeats statistics for data in a single ...

Excel Array Operation

We have all heard of arrays. Depending on the dimensionality, they are divided into one-dimensional arrays and two-dimensional arrays. Depending on the data type, they can also be divided into numeric arrays, logical arrays, and so on. The concatenation and ...

Find the Closest Data to the Data Provided in Excel

In our daily work, we may encounter such an issue that to find the closest value to a certain value. In fact, Excel internal functions can help us solve this problem. In today’s article, we will show you how to ...

Abbreviate Names Or Words in Excel

As an MS Excel user, you might have come across a task where you need to abbreviate different names or words, and there are also possibilities that you might have done this task manually by assuming that there isn't any ...

Convert State Names To Abbreviations

Assume that you got a task to convert the full state's name into the abbreviations in MS Excel, and for doing this task, you might choose to do it manually, which is an acceptable way only if you don't have ...

Sidebar