Count and rank/sort

[xcellnoob]

Just curious as to how to perform a count operation to determine how many cells to rank. What I have is this:
{=SMALL(G18:G23,ROW(INDIRECT("1:"&ROWS(I18:I23))))}
on the range I18:I23
If there are only values in G18 and G19, the whole "I" range displays #N/A
What I want is for it to only do the operation on the cells that have values. Help? Thanks in advance. Let me know if there is any more clarification necessary.

 

[Domenic]

Is this what you mean?

=IF(ROW()-ROW(I18)+1<=COUNT(G18:G23),SMALL(G18:G23,ROW(INDIRECT("1:"&ROWS(G18:G23)))),"")

 

[xcellnoob]

That didn't work. Thanks though.

 

[Domenic]

Can you provide a sample of the data, along with the actual/expected results?

 

[xcellnoob]

Here is the spreadsheet. So basically, I want excel to rank them and then sort based on any changes in the date in Column F.

<Edit> Deleted HTML because there were stupid errors in it. See next post.

 

[xcellnoob]

Forget that one above. I saw the errors, but now I want to be able to clear data in columns E and F and not have it throw #N/As all over the place. Help? Also, disregard column G. It doesn't have anything to do with anything. It was there before I figured out how to get rid of tie rankings.
Thanks again for any help.

Book1.xls
	D	E	F	G	H	I	J	K
17		Name	Date
18	1	Patrick	10/31/2006	1		1	Patrick	10/31/2006
19	10	Doug	10/15/2006	10		2	James	10/26/2006
20	2	James	10/26/2006	2		3	Johnny	10/22/2006
21	13	Mike	10/13/2006	13		4	Buddha	10/21/2006
22	3	Johnny	10/22/2006	3		5	Confuscious	10/20/2006
23	15	Eric	10/12/2006	15		6	Descartes	10/19/2006
24	14	Phoebe	10/13/2006	13		7	Socrates	10/18/2006
25	12	Carie	10/14/2006	12		8	Plato	10/17/2006
26	11	Caroline	10/15/2006	10		9	Jesus	10/16/2006
27	9	Jesus	10/16/2006	9		10	Doug	10/15/2006
28	8	Plato	10/17/2006	8		11	Caroline	10/15/2006
29	7	Socrates	10/18/2006	7		12	Carie	10/14/2006
30	6	Descartes	10/19/2006	6		13	Mike	10/13/2006
31	5	Confuscious	10/20/2006	5		14	Phoebe	10/13/2006
32	4	Buddha	10/21/2006	4		15	Eric	10/12/2006
Sheet1

 

[xcellnoob]

Bump for any additional help besides Domenic.
Thanks.

 

[Domenic]

Try...

D18, copied down:

=IF(N(F18),RANK(F18,$F$18:$F$32)+COUNTIF($F$18:F18,F18)-1,"")

J18, copied down:

=IF(N(I18),VLOOKUP(I18,$D$18:$F$32,2,FALSE),"")

K18, copied down:

=IF(N(I18),VLOOKUP(I18,$D$18:$F$32,3,FALSE),"")

Hope this helps!

 

[xcellnoob]

ummm...That works beautifully.
So it just checks to see if it's a number?

 

[Domenic]

If it's a number (a date is stored as a serial number), it returns that number and the IF statement is evaluated as TRUE. If it's blank, it returns 0 and the IF statement is evaluated as FALSE. See the help menu for additional information...

Hope this helps!