I am trying to have a RANK formula that does not skip any whole numbers (e.g. 1,2,3,4,5,6,7,8,9,10)
Screenshot showing the current rank
I have this formula that is dividing the number down into decimals which seems to work for the most part, and then a separate formula using RANK.EQ to rank them. However, while it has worked for 95% of the data, there is one bit of my dashboard where it is skipping 6 (1,2,3,4,5,7,8,9,10)
Formula used to creat decimals
=IFERROR((COLUMN(INDIRECT(LEFT([@[Client Name]])&"1"))/1000000),0)+(SUM(SWITCH([@YTD],CurrentFY,(SWITCH(COUNTIFS($AG$2:$AG2,[@[Client Name]],$AJ$2:$AJ2,CurrentFY),1,COUNTIFS([Client Name],[@[Client Name]],[YTD],CurrentFY,[Proposal Type],{"Quote Re-Work","Amendment Re-Work"}),0)),0)))
Formula used to get ranking list
=RANK.EQ([@[Total Customer (FY YTD) - RE-WORKS '#]],[Total Customer (FY YTD) - RE-WORKS '#])
Any ideas why it is skipping out 6? The rest of the columns work and there is a large dataset so not sure how this is a problem - any help would be greatly appreciated