Formula Sorting and nested IF statements

03856me

Active Member
Joined
Apr 4, 2008
Messages
277
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
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

C Moore

Well-known Member
Joined
Jan 17, 2014
Messages
544
Office Version
  1. 365
Platform
  1. Windows
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.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,755
Office Version
  1. 365
Platform
  1. Windows
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)
 

03856me

Active Member
Joined
Apr 4, 2008
Messages
277

ADVERTISEMENT

Fluff - that worked perfectly, thank you so much
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,755
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

RAJESH NATH

Board Regular
Joined
May 19, 2020
Messages
55
Office Version
  1. 365
Platform
  1. Windows
=(IF($D$5:$D$14="Yes",(RANK.EQ(E3,$E$5:$E$14,1))," "))
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,755
Office Version
  1. 365
Platform
  1. Windows
@RAJESH NATH
I think you will find that that formula doesn't work & even if it did, you would get the wrong result.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,207
Messages
5,594,843
Members
413,944
Latest member
3xc3ln00b

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
Top