< Go Back

Break ties with helper column and COUNTIF

To break ties with helper column and COUNTIF function, you can adjust similar values that result in duplication while using excel. In order to rank highest or lowest values in excel, we use SMALL, LARGE and RANK functions. But the problem occurs when we encounter ties due to duplicates in the data. To solve this problem, one method is to break ties with helper column and COUNTIF that contains adjusted values. Then rank adjusted values rather than the original values.

Formula

The generic formula to break ties with helper column and COUNTIF function is given below:

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

Explanation

COUNTIF function, as well as expanding range, are used to count incidences of values. So, the expanding range ensures that COUNTIF gives a running calculation of incidences rather than the overall count of each value.

COUNTIF($C$5:C5,C5)

After that, 1 is subtracted from the COUNTIF result (given above) and the resulting value is multiplied by0.01. The resultant value is the ‘Adjustment’ which is so small that does not affect the actual value.

Example

In this example, both Stove and Heater have a similar estimate of $300 as shown in the figure. As Stove appears first in the product list, the running total of $300 is 1 that result in zero after subtracting 1. Thus, the helper column estimate remains similar.

Figure 1 Example of break ties with helper column and COUNTIF function in Excel

 

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

=C8+(1-1)*0.01

=C8

Conversely, for Heater, the running total of $300 is 2; therefore, the adjusted estimate would be:

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

=C11+(2-1)*0.01

=C11+0.01

Therefore, instead of original values, G5 and H5 contain adjusted values.

G5 formula:

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

H5 formula:

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

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California
This blog is one way to figure it out. A 20 minute chat session with an expert is a better way.Get step-by-step guidance on your question

Leave a Comment

avatar