Formula Sorting and nested IF statements

[03856me]

I am working on a project for an event where contestants time is recorded real time and depending on all the other contestants scores (as they are entered) a "place" and a "score" are awarded. 1st, 2nd, 3rd, etc places are awarded points if they are members, based on the contestant with the lowest time.

The request is to keep the list sorted by Name (sometimes this list can have a hundred contestants so alpha sorting is a must). The user is having to manually sort by time then enter the place, then sort by place and enter the points. With up to 5 or so events per age group that is a lot of manual manipulation. There has to be a better way.

What formula can I write for columns F (Place), sorting by E (Time), ignoring D (Member) with "No". I know how to write nested IF statements but not how to rank/sort by the lowest time. Any help is greatly appreciated. Here is a snapshot with actual "Place" manually populated.

Layout sample for one event:

 

[C Moore]

Are you running Office 365? If so, the SORT/FILTER function should handle it. Allows you to tabulate data then display elsewhere. Here is a link for a RANKIFS if its an earlier version.

Rank based on Multiple criteria (Like a RankIFS)

Hi, I've read a lot of the forums on who to use rank based on multiple criteria, but I need to rank criteria based on multiple criteria. I tried using something like this: =COUNTIF($B26:$T26,">"&N26)+1+SUMPRODUCT(--($B26:$T26=N26),--($B27:$T27>N27)) but it doesnt work for what I'm looking...

www.mrexcel.com

 

[03856me]

I'll give that a try

 

[Fluff]

How about

+Fluff New.xlsm
	A	B	C	D	E	F	G
1
2
3
4
5				Yes	15.263	1
6				No	16.72
7				Yes	17.464	2
8				Yes	17.938	3
9				Yes	18.648	4
10				No	21.626
11				No	22.159
12				Yes	22.873	5
13				Yes	25.415	6
14				Yes	28.404	7
Data

Cell Formulas
Range	Formula
F5:F14	F5	=IF(D5="no","",COUNTIFS($D$5:$D$14,"Yes",$E$5:$E$14,"<"&E5)+1)

 

[03856me]

Fluff - that worked perfectly, thank you so much

 

[Fluff]

You're welcome & thanks for the feedback.

 

[RAJESH NATH]

=(IF($D$5:$D$14="Yes",(RANK.EQ(E3,$E$5:$E$14,1))," "))

 

[Fluff]

@RAJESH NATH
I think you will find that that formula doesn't work & even if it did, you would get the wrong result.