Hi Guys
Am having slit trouble with this formula in column L 13 and M13
what i am trying to get is to Rank the Name according to highest value
My List name is in Column I13
My Sum Value is in Colum J13
My Ranks Value is in H13
but after applying the formula in Column L13 for the Name i need it to list accordingly without a gap but mine is having a gap after applying down the formula
Appreciate with a support
Regards
Am having slit trouble with this formula in column L 13 and M13
what i am trying to get is to Rank the Name according to highest value
My List name is in Column I13
My Sum Value is in Colum J13
My Ranks Value is in H13
but after applying the formula in Column L13 for the Name i need it to list accordingly without a gap but mine is having a gap after applying down the formula
Appreciate with a support
Regards
ILSP KPI Performance Tracking Master Data-1.xlsx | ||||||||
---|---|---|---|---|---|---|---|---|
H | I | J | K | L | M | |||
11 | 01-May-21 | |||||||
12 | Rank | Drilling Rig/Barges | 28 | |||||
13 | 6 | Rig Shuweihat | 5.35 | Rig Hudairiyat | 6.64 | |||
14 | 10 | Deep Driller-6 | 3.88 | |||||
15 | Rig SMS Mariam | Rig Butinah | 6.52 | |||||
16 | 3 | Rig Butinah | 6.52 | Rig Makasib | 5.48 | |||
17 | Rig SMS Mariam | Rig Qarnin | 5.45 | |||||
18 | 3 | Rig Butinah | 6.52 | Rig Shuweihat | 5.35 | |||
19 | 14 | Rig Junana | 2.27 | Rig Yemillah | 5.21 | |||
20 | 19 | Compact Driller | 1.34 | Rig Al Gharbia | 4.56 | |||
21 | 9 | Rig Al Bzoom | 4.55 | Rig Al Bzoom | 4.55 | |||
22 | 22 | Rig Al Yasat | 0.76 | Deep Driller-6 | 3.88 | |||
23 | Rig Al Ghallan | Rig Artabhatt-1 | 3.83 | |||||
24 | Barge Pride | Rig Marawwah | 3.02 | |||||
25 | 28 | Barge Bahia | 0.00 | Rig Essa | 2.61 | |||
26 | Barge Shahama | Rig Junana | 2.27 | |||||
27 | 26 | Barge Supporter | 0.16 | Rig Al Lulu | 2.06 | |||
28 | 16 | Rig SMS Faith | 2.04 | Rig SMS Faith | 2.04 | |||
29 | 11 | Rig Artabhatt-1 | 3.83 | Rig High Island-7 | 1.74 | |||
30 | 27 | Rig Al Ittihad | 0.14 | Rig Al Hail | 1.55 | |||
31 | 25 | Rig Diyina | 0.18 | Compact Driller | 1.34 | |||
32 | 12 | Rig Marawwah | 3.02 | Barge Shamal | 0.85 | |||
33 | 8 | Rig Al Gharbia | 4.56 | Rig Muhaiyimat | 0.76 | |||
34 | 7 | Rig Yemillah | 5.21 | Rig Al Yasat | 0.76 | |||
35 | Barge Lulwa | Deep Driller-3 | 0.53 | |||||
36 | 24 | Barge Scirocco | 0.52 | Barge Scirocco | 0.52 | |||
37 | 17 | Rig High Island-7 | 1.74 | Rig Diyina | 0.18 | |||
38 | 15 | Rig Al Lulu | 2.06 | Barge Supporter | 0.16 | |||
39 | 21 | Rig Muhaiyimat | 0.76 | Rig Al Ittihad | 0.14 | |||
40 | 13 | Rig Essa | 2.61 | Barge Bahia | 0.00 | |||
41 | 23 | Deep Driller-3 | 0.53 | |||||
42 | 20 | Barge Shamal | 0.85 | |||||
43 | 18 | Rig Al Hail | 1.55 | |||||
44 | 4 | Rig Makasib | 5.48 | |||||
45 | Rig VKN-3 | |||||||
46 | 5 | Rig Qarnin | 5.45 | |||||
47 | 1 | Rig Hudairiyat | 6.64 | |||||
48 | ||||||||
Summary ILSP Exceed Hours |
Cell Formulas | ||
---|---|---|
Range | Formula | |
L13:L48 | L13 | =IFERROR(IF(ROW()-ROW($L$12)<=$J$12,INDEX($I$13:$I$47,MATCH(ROW()-ROW($L$12),$H$13:$H$47,0)),""),"") |
M13:M48 | M13 | =IFERROR(IF(L13<>"",INDEX($J$13:$J$48,MATCH(ROW()-ROW($L$12),$H$13:$H$48,0)),""),"") |
H13:H48 | H13 | =IFERROR(IF(J13=0,"",RANK(J13,IF($J$13:$J$48>0,$J$13:$J$48,0))+COUNTIF($J$13:$J48,$J13)-1),"") |
J12 | J12 | =MAX(H13:H48) |
J13:J48 | J13 | =IFERROR(1/(1/SUMIFS(ILSP_KPI_Tracking_Data_Entry!$Q$18:$Q$60000,ILSP_KPI_Tracking_Data_Entry!$O$18:$O$60000,'Summary ILSP Exceed Hours'!I13,ILSP_KPI_Tracking_Data_Entry!$Q$18:$Q$60000,">0",ILSP_KPI_Tracking_Data_Entry!$B$18:$B$60000,'Summary ILSP Exceed Hours'!$I$11)),"") |
Cells with Data Validation | ||
---|---|---|
Cell | Allow | Criteria |
I11 | List | =ILSP_KPI_Tracking_Data_Entry!$ACT$18:$ACT$209 |