Rank Based On Earliest Date and Score 100

muhammad susanto

Well-known Member
Joined
Jan 8, 2013
Messages
1,810
Office Version
  1. 2013
Platform
  1. Windows
hi all..

how to make rank formula based on earliest date and score.
the score can given rank is always 100.
date format in dd/mm/yyyy
here this table:
namescoredaterank
jhon9010/10/2020
mike10031/01/2020rank-1
dylan8026/07/2020
ish10012/12/2020rank-2
stec10017/31/2021rank-3


any help, great appreciated..
.sst
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

muhammad susanto

Well-known Member
Joined
Jan 8, 2013
Messages
1,810
Office Version
  1. 2013
Platform
  1. Windows
sorry i missing in last row should be :
namescoredaterank
jhon9010/10/2020
mike10031/01/2020rank-1
dylan8026/07/2020
ish10012/12/2020rank-2
stec10017/03/2021rank-3correction
 

muhammad susanto

Well-known Member
Joined
Jan 8, 2013
Messages
1,810
Office Version
  1. 2013
Platform
  1. Windows
across posting
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,520
Office Version
  1. 365
Platform
  1. Windows
Like this?

20 12 08.xlsm
ABCD
1namescoredaterank
2jhon9010/10/2020 
3mike10031/01/20201
4dylan8026/07/2020 
5ish10012/12/20202
6stec10017/03/20213
Rank
Cell Formulas
RangeFormula
D2:D6D2=IF(B2=100,SUMPRODUCT((B$2:B$6=100)*(C2>C$2:C$6))+1,"")
 

muhammad susanto

Well-known Member
Joined
Jan 8, 2013
Messages
1,810
Office Version
  1. 2013
Platform
  1. Windows
hi Peter..
thank you so much!! Working not fully.
if date same the rank same too..should be the rank not same
like this :
namescoredate
jhon9010/10/2020
mike10031/01/20201
dylan8026/07/2020
ish10012/12/20202
stec10012/12/20202should be 3
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,129,713
Messages
5,637,927
Members
416,993
Latest member
ant8989

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