Complex excel calculation with a small problem

[tmman]

Hi All,

I have, at least what I consider, a complex calculation that I have discovered a small error in... problem is, I can't figure out how to solve it. It's driving me crazy!

I have the following table:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]COL B[/TD]
[TD]COL C[/TD]
[TD]COL D[/TD]
[TD]COL E[/TD]
[TD]COL F[/TD]
[/TR]
[TR]
[TD]No.[/TD]
[TD]Name[/TD]
[TD]Views[/TD]
[TD]Fails[/TD]
[TD]Success Rate[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Name1[/TD]
[TD]3[/TD]
[TD]6[/TD]
[TD]-100%[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Name1[/TD]
[TD]3[/TD]
[TD]6[/TD]
[TD]-100%[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Name3[/TD]
[TD]2678[/TD]
[TD]2572[/TD]
[TD]3.96%[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Name4[/TD]
[TD]14[/TD]
[TD]8[/TD]
[TD]42.86%[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Name5[/TD]
[TD]9[/TD]
[TD]2[/TD]
[TD]77.78%[/TD]
[/TR]
</tbody>[/TABLE]


The calculations are as such, for the first row:

Name column:
=INDEX($N$5:$N$812,AGGREGATE(14,6,(ROW($AF$5:$AF$812)-ROW($AE$5)+1)/($AF$5:$AF$812=F14),COUNTIF($F$14:F14,F14)))

This same column for the 2nd row would look something like:
=INDEX($N$5:$N$812,AGGREGATE(14,6,(ROW($AF$5:$AF$812)-ROW($AE$5)+1)/($AF$5:$AF$812=F15),COUNTIF($F$14:F15,F15)))

etc...

Views:
=VLOOKUP($C14,$N$5:$AD$812,16,FALSE)

Fails:
=VLOOKUP($C14,$N$5:$AD$812,17,FALSE)

Success Rate:
{=SMALL(IF(($P$5:$P$812="JSP")*($O$5:$O$812=MAX($O$5:$O$812)),$AF$5:$AF$812,""),B14)}

These equations should search a table of items and list the top 5 with the lowest success rates. For the most part, it works. However, when 2 items have the exact same success rate, it fails. As you see in the table above, #1 and #2 are both the same. Column N contains the item name, Column O contains the date (2 dates per item), P contains the item type, AC is total views, AD is total fails, and AF is the success rate for that item.

The actual data for #1 (or #2), should be:
Name2, 1, 2, -100%

But because the percentage is the exact same as #1, it is pulling that item again and skipping over #2 completely. I am hoping someone can First, make sense of this madness and Second, be able to provide some type of tweak to my equation to help alleviate this issue.

Any help is greatly appreciated!

 

[mikerickson]

Rather than sorting on SuccessRate, try sorting on SuccessRate+(ROW/100000). This will turn duplicate SuccessRates into unique values.

Note that SUMPRODUCT(--(A1:A100=A1)) returns the same as COUNTIF(A1:A100, A1), so
SUMRPODUCT(--((A1:A100+ROW(A1:A100)/1000)=(A1+ROW(A1)/1000)) would be used as a replacement.

 

[tmman]

So, the item name (COL C) equation would turn into:...?

=INDEX($N$5:$N$812,AGGREGATE(14,6,(ROW($AF$5:$AF$812)-ROW($AE$5)+1)/($AF$5:$AF$812=F15),SUMPRODUCT(--(($AF$5:$AF$812+ROW($AE$5:$AF$812)/1000)=($AF$5+ROW($AE$5)/1000))))

Does that sound about right?

 

[mikerickson]

I don't understand your formula and can't comment on it.

I was passing along the Value >> Value+ROW/1000 trick to separate equal integer values. Looking at your data again, you'll have to use ROW/100000 to get below the .1% "grainy-ness" of your data.

 

[tmman]

Thank you for the advice. I will see if I can't make it work somehow. Again, appreciate you taking the time!