Formula Sorting and nested IF statements

03856me

Active Member
Joined
Apr 4, 2008
Messages
297
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:
1597165790292.png
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
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.
 
Upvote 0
How about
+Fluff New.xlsm
ABCDEFG
1
2
3
4
5Yes15.2631
6No16.72 
7Yes17.4642
8Yes17.9383
9Yes18.6484
10No21.626 
11No22.159 
12Yes22.8735
13Yes25.4156
14Yes28.4047
Data
Cell Formulas
RangeFormula
F5:F14F5=IF(D5="no","",COUNTIFS($D$5:$D$14,"Yes",$E$5:$E$14,"<"&E5)+1)
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0
@RAJESH NATH
I think you will find that that formula doesn't work & even if it did, you would get the wrong result.
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,194
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top