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.
The generic formula to break ties with helper column and COUNTIF function is given below:
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.
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.
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
Conversely, for Heater, the running total of $300 is 2; therefore, the adjusted estimate would be:
Therefore, instead of original values, G5 and H5 contain adjusted values.