rank negative, ignore positive

[sabine]

Hello
hope someone can help,I need to rank negative $ amounts but ignor any positive amounts.

here is a sample of the data

<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=128><COLGROUP><COL style="WIDTH: 48pt; mso-width-source: userset; mso-width-alt: 2340" width=64><COL style="WIDTH: 48pt; mso-width-source: userset; mso-width-alt: 2340" width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #8db4e3; WIDTH: 48pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl471 height=17 width=64>-1873.90</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #8db4e3; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl468 width=64>183</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #8db4e3; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl472 height=17>-1721.26</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #8db4e3; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl469>182</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #8db4e3; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl472 height=17>-1528.18</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #8db4e3; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl469>181</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #8db4e3; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl472 height=17>-1492.68</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #8db4e3; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl469>180</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #8db4e3; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl472 height=17>-1478.89</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #8db4e3; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl469>179</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #8db4e3; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl472 height=17>-1329.69</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #8db4e3; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl469>178</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #8db4e3; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl472 height=17>-1245.07</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #8db4e3; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl469>177</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #8db4e3; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl472 height=17>-1230.36</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #8db4e3; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl469>176</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #8db4e3; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl472 height=17>-1174.22</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #8db4e3; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl469>175</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #8db4e3; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl472 height=17>-1153.98</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #8db4e3; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl469>174</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #8db4e3; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl472 height=17>-1143.24</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #8db4e3; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl469>173</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #8db4e3; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl472 height=17>-1131.38</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #8db4e3; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl469>172</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #8db4e3; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl472 height=17>-1104.88</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #8db4e3; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl469>171</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #8db4e3; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl472 height=17>-1097.25</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #8db4e3; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl469>170</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #8db4e3; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl472 height=17>350.00 </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #8db4e3; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl469>1
</TD></TR></TBODY></TABLE>

This is the formula that I use for the rank

=MATCH(P8,UNIQUEVALUES($P$8:$P$213),0)

An thoughts?
Thanks

 

[tushiroda]

using rank, you can get there pretty quick

Excel Workbook
	A	B	C
1	value	rank down	rank up
2	-1873.9	13	1
3	-1721.26	12	2
4	-1528.18	11	3
5	-1492.68	10	4
6	-1478.89	9	5
7	-1329.69	8	6
8	-1245.07	7	7
9	-1230.36	6	8
10	400	0	0
11	-1153.98	6	9
12	-1143.24	5	10
13	-1131.38	4	11
14	-1104.88	3	12
15	-1097.25	2	13
16	350	0	0
Sheet2

Excel 2007

Cell Formulas
Range	Formula
B2	=IF($A$2:$A$16<0,RANK(A2,$A$2:$A$16,0)-COUNTIF(A2:A16,">=0"),0)
C2	=IF($A$2:$A$16<0,RANK(A2,$A$2:$A$16,1),0)

 

[sabine]

Yes that works but I get one additional problem, as I have mulitbe cells that will return a 0 the first 'ranked' cell starts at 25

<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=128><COLGROUP><COL style="WIDTH: 48pt; mso-width-source: userset; mso-width-alt: 2340" width=64><COL style="WIDTH: 48pt; mso-width-source: userset; mso-width-alt: 2340" width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #8db4e3; WIDTH: 48pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl471 height=17 width=64>0.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #8db4e3; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl469 width=64>0</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #8db4e3; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl471 height=17>0.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #8db4e3; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl469>0</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #8db4e3; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl471 height=17>0.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #8db4e3; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl469>0</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #8db4e3; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl471 height=17>347.76</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #8db4e3; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl469>0</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #8db4e3; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl471 height=17>-3.75</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #8db4e3; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl469>25</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #8db4e3; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl471 height=17>-6.71</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #8db4e3; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl469>26</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #8db4e3; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl471 height=17>-10.89</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #8db4e3; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl469>27</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #8db4e3; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl471 height=17>-13.42</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #8db4e3; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl469>28</TD></TR></TBODY></TABLE>

 

[tushiroda]

doh, forgot to anchor one of the ranges. this should do it

Excel Workbook
	A	B	C
2	0	0	0
3	0	0	0
4	0	0	0
5	347.76	0	0
6	-3.75	1	4
7	-6.71	2	3
8	-10.89	3	2
9	-13.42	4	1
Sheet1

Excel 2007

Cell Formulas
Range	Formula
B2	=IF($A$2:$A$9<0,RANK(A2,$A$2:$A$9,0)-COUNTIF($A$2:$A$9,">=0"),0)
C2	=IF($A$2:$A$9<0,RANK(A2,$A$2:$A$9,1),0)

 

[sabine]

Thanks, you are my hero