Problems Breaking Ties In Rank Function

Hi

Cell Q2

Formula:

Please Login or Register  to view this content.

copy down

Cell Q14

Formula:

Please Login or Register  to view this content.

copy down

Regard
micope21

Hi again

If you want (#VALUE) remove?

Cell R2

Formula:

Please Login or Register  to view this content.

copy down

Cell R14

Formula:

Please Login or Register  to view this content.

copy down

This will give you 0 instead show up #VALUE!

Regard
micope21

Thank you micope21 and kvsrinivasamurthy for you help. Update:
I changed the digits in F2:M2 and F3:M3 to show that I am not getting correct rank order. Digit 4 and digit 5 have the correct rank but
for the third rank it's showing digit 6, based on the position for June 14th, when in fact digit 8 showed up twice on June 13th and therefore
reach the total of 2 first and should be ranked 3rd, not 5th. Not sure how to get that into a formula, I think the POS column is set up wrong,
not sure if I need more helper columns or not. I know this is complicated but hoping someone can see what result I am looking for.
I appreciate everyone's help.

Hi

Okay

If Digit 8 is higher than 7 or 6 and all on totals 2?
Then formula need point to total then point to higher Digit if I'm correct??

Try this
Cell Q2

Formula:

Please Login or Register  to view this content.

copy down

Cell Q14

Formula:

Please Login or Register  to view this content.

copy down

Regard
micope21

Hi again

See the file!

Regard
micope21

Now ...I may have completely misunderstood this. If so, apologies..

If not in case of a tie, I believe the rank should be based on which number got there first, thereby getting the higher rank.

With the running total this becomes complex. My reading would be that positions in first row would be 1-8, in second row 9-16, third row 17-24 and so on which would then aid in determining the rank in case tie break...

See the attached..
The array A1:H1 and A1:H2 only aid in determining the position number

Hope this helps..

The following are the totals for each digit with the date and the position they reached it.

Digit 5, total 3, reached it on June 13, position 2, should be ranked 1st
Digit 4, total 3, reached it on June 14, position 1, should be ranked 2nd

Digit 8, total 2, reached it on June 13, position 8, should be ranked 3rd
Digit 6, total 2, reached it on June 14, position 4, should be ranked 4th
Digit 7, total 2, reached it on June 14, position 8, should be ranked 5th

Digit 2, total 1, reached it on June 13, position 5, should be ranked 6th
Digit 3, total 1, reached it on June 13, position 6, should be ranked 7th
Digit 1, total 1, reached it on June 14, position 2, should be ranked 8th
Digit 9, total 1, reached it on June 14, position 3, should be ranked 9th

So the running totals are based on the total, then the first date it reach it and then the position.

Hi

Thank you explain this? Now I got it what you trying to explain?
Ace_XL just best me lol.
Ace_XL file is correct Ranking.

Regard
micope21

Ignore me. I missed a post earlier in the thread...

via PM

I am copying down each day for 50 total days. Would the formula change under POS? it seems like ROW($A$1:$H$1)-1)*8+1*COLUMN($A$1:$A$1)),0),0) would change to
$H$2, the next day $H$3, etc. but on day 4 i'm receiving N/A error.

I noticed that under pos (P2) the fourmula $F$2:$M2 = $N14, which is the start of the second day. To add up to 50 days do I adjust the formula in P2 to the start of the next day $P$2?

The formula for the third batch would be
=MAX(INDEX(($F$2:$M$3=$N14)*((ROW($A$1:$H$2)-1)*8+1*COLUMN($A$1:$H$2)),0),0)

The blue highlighted portion should reference the digit i.e col N of your file

Adapt accordingly for other running totals