The function RANK Failed i need formula

mole999

Well-known Member

Joined

Oct 23, 2004

Messages

10,524

Office Version

1. 2019
2. 2016
3. 2013

Platform

1. Windows

• Nov 9, 2014

• #2

did it fail, or did it do what is actually a normal ranking anywho

Ranking - retain Duplicate values without skipping sequence | Chandoo.org Excel Forums - Become Awesome in Excel

 

Upvote 0

GerryZ

Well-known Member

Joined

Jul 4, 2014

Messages

1,224

Office Version

1. 365

Platform

1. Windows

• Nov 9, 2014

• #3

Hello mole999
RANK did't failed but it gives me back not the right result
in column B is the right result
in colum C is the wrng result made with RANK
I need a formul that give colum B
thnk you

	A	B	C
1	score	I want this	Rank (wrong)
2	200	1	1
3	120	2	2
4	120	2	2
5	50	3	4
6	50	3	4
7	48	4	6
8	45	5	7
9	45	5	7
10	45	5	7
11	42	6	10
12	13	7	11
13	12	8	12
14	12	8	12

<TBODY>
</TBODY>

Excel tables to the web >> Excel Jeanie HTML 4

 

Upvote 0

GerryZ

Well-known Member

Joined

Jul 4, 2014

Messages

1,224

Office Version

1. 365

Platform

1. Windows

• Nov 9, 2014

• #4

	A	B	C
1	score	I want this	Rank (wrong)
2	200	1	1
3	120	2	2
4	120	2	2
5	50	3	4
6	50	3	4
7	48	4	6
8	45	5	7
9	45	5	7
10	45	5	7
11	42	6	10
12	13	7	11
13	12	8	12
14	12	8	12

<COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 148px"><COL style="WIDTH: 147px"><COL style="WIDTH: 116px"></COLGROUP><TBODY>
</TBODY>

Excel tables to the web >> Excel Jeanie HTML 4

 

Upvote 0

mole999

Well-known Member

Joined

Oct 23, 2004

Messages

10,524

Office Version

1. 2019
2. 2016
3. 2013

Platform

1. Windows

• Nov 9, 2014

• #5

224 is not actually wrong, but not what you wanted

DID YOU follow the link with my post

 

Upvote 0

GerryZ

Well-known Member

Joined

Jul 4, 2014

Messages

1,224

Office Version

1. 365

Platform

1. Windows

• Nov 9, 2014

• #6

Hi yes I tried some of them but they don't work
I also tried the one with frequency
I'm doing something wrong
do you mind write it down me?

 

Upvote 0

Matt Rogers

Well-known Member

Joined

Sep 23, 2011

Messages

1,030

• Nov 9, 2014

• #7

Hi Gerry.

Array-Formula in B2=

Code:

{=ISNUMBER(A2)*SUM(IF(IF(ISNUMBER(A$2:A$1000)*(COUNTIF(INDIRECT("A1:A"&ROW($2:$1000)),A$2:A$1000)=1),A$2:A$1000,MIN(A$2:A$1000)-1)>A2,1),1)}

 

Upvote 0

GerryZ

Well-known Member

Joined

Jul 4, 2014

Messages

1,224

Office Version

1. 365

Platform

1. Windows

• Nov 9, 2014

• #8

Hi Matt! i tried your beautiful formula and works prfectly
I was waiting for your answer but didn't arrive so earlly as usual so I find in the net something little different that give me the same result
any way you are magic and still I'm waiting to know your secrets hahah!!!
Matt! I send you friendship here in mr.excel.com I hope you except it
this is the formula i found
and thank you again!!

	A	B
3	200	1
4	120	2
5	120	2
6	50	3
7	50	3
8	48	4
9	45	5
10	45	5
11	45	5
12	42	6
13	13	7
14	12	8
15	12	8

<COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 76px"></COLGROUP><TBODY>
</TBODY>

Spreadsheet Formulas

Cell Formula B3 {=SUM(IF(A3<$A$3:$A$16,1/COUNTIF($A$3:$A$16,$A$3:$A$16)))+1} B4 {=SUM(IF(A4<$A$3:$A$16,1/COUNTIF($A$3:$A$16,$A$3:$A$16)))+1} B5 {=SUM(IF(A5<$A$3:$A$16,1/COUNTIF($A$3:$A$16,$A$3:$A$16)))+1} B6 {=SUM(IF(A6<$A$3:$A$16,1/COUNTIF($A$3:$A$16,$A$3:$A$16)))+1} B7 {=SUM(IF(A7<$A$3:$A$16,1/COUNTIF($A$3:$A$16,$A$3:$A$16)))+1} B8 {=SUM(IF(A8<$A$3:$A$16,1/COUNTIF($A$3:$A$16,$A$3:$A$16)))+1} B9 {=SUM(IF(A9<$A$3:$A$16,1/COUNTIF($A$3:$A$16,$A$3:$A$16)))+1} B10 {=SUM(IF(A10<$A$3:$A$16,1/COUNTIF($A$3:$A$16,$A$3:$A$16)))+1} B11 {=SUM(IF(A11<$A$3:$A$16,1/COUNTIF($A$3:$A$16,$A$3:$A$16)))+1} B12 {=SUM(IF(A12<$A$3:$A$16,1/COUNTIF($A$3:$A$16,$A$3:$A$16)))+1} B13 {=SUM(IF(A13<$A$3:$A$16,1/COUNTIF($A$3:$A$16,$A$3:$A$16)))+1} B14 {=SUM(IF(A14<$A$3:$A$16,1/COUNTIF($A$3:$A$16,$A$3:$A$16)))+1} B15 {=SUM(IF(A15<$A$3:$A$16,1/COUNTIF($A$3:$A$16,$A$3:$A$16)))+1} <TBODY> </TBODY>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

Matrix verstehen

<TBODY>
</TBODY>

Excel tables to the web >> Excel Jeanie HTML 4

 

Upvote 0

Matt Rogers

Well-known Member

Joined

Sep 23, 2011

Messages

1,030

• Nov 9, 2014

• #9

Sorry Gerry,
I've done crap ...

 

Upvote 0

GerryZ

Well-known Member

Joined

Jul 4, 2014

Messages

1,224

Office Version

1. 365

Platform

1. Windows

• Nov 9, 2014

• #10

this means that in Excel there is not only one way risolve a problem
thank you very much for your help!!

 

Upvote 0