LARGE with Criteria without using Array?

[danhenshy23]

Hi,

I have a a column containing a list of scores (Column C) and another containing the list of checks completed (column B).

I have a SMALL formula which ranks Column C from lowest to highest, but I am wanting to have a formula in Column B which lists the number of completed checks from highest to lowest.

The purpose is so that someone who has scored 0% and has had 7 checks completed would appear higher than someone who has scored 0% but only had 2 checks completed.

I am trying to achieve this without the use of arrays if possible.

Thanks
Dan

 

[jimrward]

You can combine score and checks into a decimal so your first score in your example would be 0.7 and the second 0.2 or 0.07 and 0.02 by score + (checks/100) and then carry on as you are

 

[Aladin Akyurek]

Care to post here directly a small sample of 5 rows along with the expected results?

 

[danhenshy23]

I tried using jimrward's suggestion, but I may have misunderstood.

Sample data as follows: (hope this works)

A |B |C
Name |Checks |Score (%)
Bob | 4 | 0%
Chris | 1 | 0%
Tom | 3 | 0%
Alex | 1 | 16%
Neil | 3 | 16%

From the above data, the result I would want would be for Bob to appear at the top, as he has had the most checks completed but scored the lowest, followed by Tom, Chris, Neil and Alex.

Thanks
Dan

 

[Marcelo Branco]

Maybe...


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Name​

[/td][td]

Checks​

[/td][td]

Score (%)​

[/td][td]

Rank​

[/td][td]

List​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

Bob​

[/td][td]

4​

[/td][td]

0%​

[/td][td]

1​

[/td][td]

Bob​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

Chris​

[/td][td]

1​

[/td][td]

0%​

[/td][td]

3​

[/td][td]

Tom​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

Tom​

[/td][td]

3​

[/td][td]

0%​

[/td][td]

2​

[/td][td]

Chris​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

Alex​

[/td][td]

1​

[/td][td]

16%​

[/td][td]

5​

[/td][td]

Neil​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

Neil​

[/td][td]

3​

[/td][td]

16%​

[/td][td]

4​

[/td][td]

Alex​

[/td][/tr]
[/table]

Formula in D2 copied down
=COUNTIF($C$2:$C$6,"<"&C2)+1+COUNTIFS($C$2:$C$6,C2,$B$2:$B$6,">"&B2)

Formula in E2 copied down
=INDEX($A$2:$A$6,MATCH(ROWS(E$2:E2),$D$2:$D$6,0))

M.