Using RANK in a calculated cell not working if reference to a range in pivot

[ricomonster]

Hello Everyone

Just new in the community and been encountering issue using rank. In a nutshell I have cell that computes a certain score in which I want to ranked to a pivot. My formula would go like
rank(A1,(A1,Sheet2!D6:D38))

My speculation is that A1 yields to a result formatted in text which the rank function cannot interpret. Tried to reformat but still not joy. The formula by the way works perfectly if I have values formatted in number

 

[cyrilbrd]

Hi there,
Based on Excel help, =rank() works as follows: RANK(number,ref,[order])
hence you need a number , a reference and and order (ascending or descending).
1- Do you want your formula to sort in descending order the values in A1 as compared with the values in the range D6 to D38?
2- Is A1 found within such range?
3- are you using values (integer)?

thanks.

Excel 2010
	A	B	C	D
1	values	rank
2	1	8
3	3	6
4	4	5
5				range
6				1
7				3
8				4
9				6
10				7
11				8
12				11
13				2
ricomonster

 

[ricomonster]

Thank you for the reply yes i want to sort a1 using d6:d38.
And a1 is not found in the range hence the inclusion of a1 in the reference. Integers? - i think so

 

[cyrilbrd]

A1 is not in the list to be sorted? Please post a small sample or base it on the one I posted earlier.

 

[ricomonster]

Hi Im not sure how to do it just like yours, format is taken out when I paste my sample to reply box. Anyways A1 cannot be found on (D6:D38) that is why in the reference i wrote (A1,D6:D38) from the formula of =rank(A1,(A1,D6:D38))

In your example above, let say values "1", "3" and "4" are not in the range of D6:D13


Thank you very much for your help

 

[cyrilbrd]

Then use the formula =RANK(A2,(A2,$D$6:$D$13)) in B2:

Excel 2010
	A	B	C	D
1	values	rank
2	1	9
3
4
5				range
6				9
7				3
8				4
9				6
10				7
11				8
12				11
13				2
ricomonster