Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Get number of a cell in a list.
Hi there im new and been using excel for a week or two now. What I wanted to know is this, say I had a list of dates all in a column and beside it I had another column and in this one I want a formula to say were abouts in the list the cell next to it is for example in cell D2 i want a formula that tells me what position in the list the date in cell C2 is. Sorry Im not very good at explaining this its for a group thing im doing and if i put in the date of when the person joined it would tell me what member they would be like the 93rd member or something. If you want me to explain better i'll try, Thanks for any help. -- Keno ------------------------------------------------------------------------ Keno's Profile: http://www.excelforum.com/member.php...o&userid=30832 View this thread: http://www.excelforum.com/showthread...hreadid=504956 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Get number of a cell in a list.
Try something like this: For a list of dates in Cells A1:A100 C2: (some date) D2: =MATCH(C2,$A$1:$A$100,0) Does that help? Regards, Ron -- Ron Coderre ------------------------------------------------------------------------ Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419 View this thread: http://www.excelforum.com/showthread...hreadid=504956 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Get number of a cell in a list.
When I try that I get #NAME? in the cell. -- Keno ------------------------------------------------------------------------ Keno's Profile: http://www.excelforum.com/member.php...o&userid=30832 View this thread: http://www.excelforum.com/showthread...hreadid=504956 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Get number of a cell in a list.
The #NAME! error regarding a function usually indicates a spelling error. Try copying the formula from this window and pasting it into cell D2. =MATCH(C2,$A$1:$A$100,0) Does that help? Regards, Ron -- Ron Coderre ------------------------------------------------------------------------ Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419 View this thread: http://www.excelforum.com/showthread...hreadid=504956 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Get number of a cell in a list.
Well its working now but not the way I want, When I mean the cells positions in a list I mean as if the dates were arranged in accending order but because there not I wanted a quick way of seeing who joined first and such. So like from this list of dates this would be there order. 19/05/02 9 23/04/02 4 09/05/02 7 12/05/02 8 13/04/02 2 02/05/02 6 04/04/02 1 17/04/02 3 26/04/02 5 23/05/02 10 But I dont want to manually work out there number by looking at the dates I want a formula to do it for me. -- Keno ------------------------------------------------------------------------ Keno's Profile: http://www.excelforum.com/member.php...o&userid=30832 View this thread: http://www.excelforum.com/showthread...hreadid=504956 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Get number of a cell in a list.
OK...got it, now.... How about this: =RANK(A1,$A$1:$A$10) Returns the relative position in the list. Note though, the largest value ranks:1 To get what you posted, this may work for you: =COUNT($A$1:$A$10)+1-RANK(A1,$A$1:$A$10) Is that something you can work with? Regards, Ron -- Ron Coderre ------------------------------------------------------------------------ Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419 View this thread: http://www.excelforum.com/showthread...hreadid=504956 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Get number of a cell in a list.
Ah!! Thank you so much, This works Great!! :) I cant tell you how much this helped Iv been trying to work it out for a few days now, Thanks Again. -- Keno ------------------------------------------------------------------------ Keno's Profile: http://www.excelforum.com/member.php...o&userid=30832 View this thread: http://www.excelforum.com/showthread...hreadid=504956 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Get number of a cell in a list.
Additional Info: This version of the RANK function does exactly what you want: =RANK(A1,$A$1:$A$10,1) (I don't know why I didn't remember that in the first place!) Ron -- Ron Coderre ------------------------------------------------------------------------ Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419 View this thread: http://www.excelforum.com/showthread...hreadid=504956 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Get number of a cell in a list.
Just noticed some thing with this, used it on a list of dates with a few repeated dates (incase I get members who joined the same day). Now it gives people with the same day the same number but with the first double date it done this. 19/04/02 7 13/04/02 5 09/04/02 4 19/04/02 7 04/04/02 2 17/04/02 6 03/04/02 1 23/04/02 9 04/04/02 2 It does the two dates but then misses out the number after it, see the 3 is missing and the eight? -- Keno ------------------------------------------------------------------------ Keno's Profile: http://www.excelforum.com/member.php...o&userid=30832 View this thread: http://www.excelforum.com/showthread...hreadid=504956 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Get number of a cell in a list.
Correct....RANK displays ties, but leaves a placeholder for the "missing" value. How would you pefer to handle exact duplicates? Ron -- Ron Coderre ------------------------------------------------------------------------ Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419 View this thread: http://www.excelforum.com/showthread...hreadid=504956 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
In Cell Validation List & Linked Cell VLOOKUP | Excel Discussion (Misc queries) | |||
Instead of a negative number, I'd like to show zero... | Excel Worksheet Functions | |||
select cell based on a number in another cell | New Users to Excel | |||
Count Number of Characters in a cell? | Excel Discussion (Misc queries) | |||
GET.CELL | Excel Worksheet Functions |