Can I rank my data in this unique way?

Copper Contributor

IMG_2241.jpeg

 I have a tracker (not pictured) and this picture you see is the overview of the tracker and updated in real time. I categorise the data from the tracker into these 9 slots 

 

time done 

points scored 

percentage compared to overall 

 

the question is are there any ways to make it so that as the data changes these 9 categories switch around automatically going from 1st to 9th? 

 

Obviously the conditional formatting is a piece of cake but in terms of the rest I’m not sure 

 

id like it so that they are all ranked on time done. (Most time spent means first basically) 

 

any help at all is appreciated. If I can’t do it obviously I’ll just use a simple rank formula I just would of really like to see them move around, would of been cooler haha 

9 Replies

Hi @DanT-N 

 

There might be something to do (below ranking is auto. when [Time done] is updated):

Sample.png

 

Depends on where/how your data are stored, which version of Excel you run...

Feel free to follow recommendations in Welcome to your Excel discussion space!

@L z. Hi yea thanks for trying 

 

I’m sure there’s a way to do it I’ll keep asking around aha 

 

I’ll have a look at that discussion space  too thanks 

Also I understand how u got the time to rank but how did u get the score/percentage to follow and automatically write below? Not sure on that one
What version of Excel do you run? I did it with 365 and can share it...
Yea I’m on 365 too

@DanT-N 

 

Yea I’m on 365 too So see attached file where the Rank is Dense

Very long formula not really sure how that works

Maybe this is out of my depth :grinning_face_with_sweat:

@DanT-N 

 

Decomposed this could be:

=LET(
  RoundTimeColumnOnly,      IF( SEQUENCE(,COLUMNS(Tracker)) = 1, ROUND(Tracker, 10), Tracker ),
  SortTrackerByColumnTime,  SORT(RoundTimeColumnOnly, 1, -1),
  TransposeTheArray,        TRANSPOSE(SortTrackerByColumnTime),
  TimeValues,               CHOOSEROWS(TransposeTheArray, 1),
  DenseRank,                XMATCH(TimeValues, UNIQUE(TimeValues, TRUE) ),
  RankAndDataAsOneArray,    VSTACK(DenseRank, TransposeTheArray),
  RankAndDataAsOneArray
)

 

A bit more clear?

@DanT-N Any problem with the proposed solution? Does the job?