=RANK(1,5)

[CMLET]

Hi All. I've search through the posted threads for a RANK formula. I'm trying to Rank the values in Column D, 1 through 5. The desired results examples are listed in Column E. I've sorted Column B lowest to highest. Would it help to combine =B&C first? Thanks for any help.

 

[Hui]

Firstly you need to better define the problem

1. There is no apparent connection between columns B&C and column D?

2. When you say Rank, do you mean Score?
that is the minimum value in column D is 7, The maximum value 30

If you want to assign them 5 scores in categories of size 4.6
like this:



You can then use that table to assign the values in Column K to the

see the attached file where this is implemented

If you want the values sorted 5 to 1 simply change the values in Column K

3. If you simply want to choose a random number between 1 and 5 in column E use: =RANDBETWEEN(1,5)

 

[bosco_yip]

Maybe...............................

Conditional rank to Column B and Column D, result in Column E



1] Column C is not required

2] In E1, copied down :

=COUNTIFS(B$1:B$621,B1,D$1:D$621,">"&D1)+COUNTIFS(B$1:B1,B1,D$1:D1,D1)

Regards
Bosco

 

[CMLET]

Firstly you need to better define the problem

1. There is no apparent connection between columns B&C and column D?

2. When you say Rank, do you mean Score?
that is the minimum value in column D is 7, The maximum value 30

If you want to assign them 5 scores in categories of size 4.6
like this:

View attachment 58748

You can then use that table to assign the values in Column K to the

see the attached file where this is implemented

If you want the values sorted 5 to 1 simply change the values in Column K

3. If you simply want to choose a random number between 1 and 5 in column E use: =RANDBETWEEN(1,5)

Hi Hui. I like the 5 scores in categories suggestion! This is a work in progress project, and I'm trying out a variety of scenarios.

 

[CMLET]

Maybe...............................

Conditional rank to Column B and Column D, result in Column E

View attachment 58751

1] Column C is not required

2] In E1, copied down :

=COUNTIFS(B$1:B$621,B1,D$1:D$621,">"&D1)+COUNTIFS(B$1:B1,B1,D$1:D1,D1)

Regards
Bosco

Hi Bosco. Simply put...it works awesome! Thank You.