# [Solved]Rank Formula

#### Jululian

##### New member
Dear All,
May I kindly request your help with the ranking formula? as I was getting decimals in a rank column
I do appreciate you assisting me this issue

Thanks and Regards

#### Attachments

• Book2.xlsx
14.2 KB · Views: 6
Dear Jululian,

I hope you are doing well. I've used the ROUNDUP and ROUND formulas to avoid the decimals in rank. Where numbers are rounded up if the decimal values are less than 0.5, or rounded and added +1 to the next integer if they are equal to or greater than 0.5. You can change the condition based on your data.

Formula : =IFERROR(IF(F5-INT(F5)>=0.8, ROUND(F5,0)+1, ROUNDUP(F5,0)),"")

The reason for using the condition greater than or equal to 0.5 is:

#### Attachments

• Solution File.xlsx
14.7 KB · Views: 2
Dear Jululian,

I hope you are doing well. I've used the ROUNDUP and ROUND formulas to avoid the decimals in rank. Where numbers are rounded up if the decimal values are less than 0.5, or rounded and added +1 to the next integer if they are equal to or greater than 0.5. You can change the condition based on your data.

Formula : =IFERROR(IF(F5-INT(F5)>=0.8, ROUND(F5,0)+1, ROUNDUP(F5,0)),"")

View attachment 1280

The reason for using the condition greater than or equal to 0.5 is:

View attachment 1281
 Many thanks for your reply and for your time​ may I kindly request that I need to fix my formula, not the helper column and thank you agian​

 Many thanks for your reply and for your time​ may I kindly request that I need to fix my formula, not the helper column and thank you agian​
You can use this formula : =IFERROR( IF(D5=0, "",IF(D5>0, IF( SUMPRODUCT((\$B\$5:\$B\$44=B5)*(\$C\$5:\$C\$44=C5),(D5<=\$D\$5:\$D\$44)/COUNTIF(\$D\$5:\$D\$44,\$D\$5:\$D\$44))-INT(SUMPRODUCT((\$B\$5:\$B\$44=B5)*(\$C\$5:\$C\$44=C5),(D5<=\$D\$5:\$D\$44)/COUNTIF(\$D\$5:\$D\$44,\$D\$5:\$D\$44)))>=0.8,ROUND(SUMPRODUCT((\$B\$5:\$B\$44=B5)*(\$C\$5:\$C\$44=C5),(D5<=\$D\$5:\$D\$44)/COUNTIF(\$D\$5:\$D\$44,\$D\$5:\$D\$44)),0)+1,ROUNDUP(SUMPRODUCT((\$B\$5:\$B\$44=B5)*(\$C\$5:\$C\$44=C5),(D5<=\$D\$5:\$D\$44)/COUNTIF(\$D\$5:\$D\$44,\$D\$5:\$D\$44)),0)), "" ) ), "")

To avoid a lengthy formula I used the previous formula.

If you are an advanced Excel user you can follow this formula too: =IFERROR(IF(D5=0,"",LET(sumProd,SUMPRODUCT((\$B\$5:\$B\$44=B5)*(\$C\$5:\$C\$44=C5),(D5<=\$D\$5:\$D\$44)/COUNTIF(\$D\$5:\$D\$44,\$D\$5:\$D\$44)),IF(sumProd-INT(sumProd)>=0.8,ROUND(sumProd,0)+1,ROUNDUP(sumProd,0)))),"")

You can use this formula : =IFERROR( IF(D5=0, "",IF(D5>0, IF( SUMPRODUCT((\$B\$5:\$B\$44=B5)*(\$C\$5:\$C\$44=C5),(D5<=\$D\$5:\$D\$44)/COUNTIF(\$D\$5:\$D\$44,\$D\$5:\$D\$44))-INT(SUMPRODUCT((\$B\$5:\$B\$44=B5)*(\$C\$5:\$C\$44=C5),(D5<=\$D\$5:\$D\$44)/COUNTIF(\$D\$5:\$D\$44,\$D\$5:\$D\$44)))>=0.8,ROUND(SUMPRODUCT((\$B\$5:\$B\$44=B5)*(\$C\$5:\$C\$44=C5),(D5<=\$D\$5:\$D\$44)/COUNTIF(\$D\$5:\$D\$44,\$D\$5:\$D\$44)),0)+1,ROUNDUP(SUMPRODUCT((\$B\$5:\$B\$44=B5)*(\$C\$5:\$C\$44=C5),(D5<=\$D\$5:\$D\$44)/COUNTIF(\$D\$5:\$D\$44,\$D\$5:\$D\$44)),0)), "" ) ), "")

To avoid a lengthy formula I used the previous formula.

If you are an advanced Excel user you can follow this formula too: =IFERROR(IF(D5=0,"",LET(sumProd,SUMPRODUCT((\$B\$5:\$B\$44=B5)*(\$C\$5:\$C\$44=C5),(D5<=\$D\$5:\$D\$44)/COUNTIF(\$D\$5:\$D\$44,\$D\$5:\$D\$44)),IF(sumProd-INT(sumProd)>=0.8,ROUND(sumProd,0)+1,ROUNDUP(sumProd,0)))),"")

#### Attachments

• Book3.xlsx
32.3 KB · Views: 2
You can use this formula : =IFERROR( IF(D5=0, "",IF(D5>0, IF( SUMPRODUCT((\$B\$5:\$B\$44=B5)*(\$C\$5:\$C\$44=C5),(D5<=\$D\$5:\$D\$44)/COUNTIF(\$D\$5:\$D\$44,\$D\$5:\$D\$44))-INT(SUMPRODUCT((\$B\$5:\$B\$44=B5)*(\$C\$5:\$C\$44=C5),(D5<=\$D\$5:\$D\$44)/COUNTIF(\$D\$5:\$D\$44,\$D\$5:\$D\$44)))>=0.8,ROUND(SUMPRODUCT((\$B\$5:\$B\$44=B5)*(\$C\$5:\$C\$44=C5),(D5<=\$D\$5:\$D\$44)/COUNTIF(\$D\$5:\$D\$44,\$D\$5:\$D\$44)),0)+1,ROUNDUP(SUMPRODUCT((\$B\$5:\$B\$44=B5)*(\$C\$5:\$C\$44=C5),(D5<=\$D\$5:\$D\$44)/COUNTIF(\$D\$5:\$D\$44,\$D\$5:\$D\$44)),0)), "" ) ), "")

To avoid a lengthy formula I used the previous formula.

If you are an advanced Excel user you can follow this formula too: =IFERROR(IF(D5=0,"",LET(sumProd,SUMPRODUCT((\$B\$5:\$B\$44=B5)*(\$C\$5:\$C\$44=C5),(D5<=\$D\$5:\$D\$44)/COUNTIF(\$D\$5:\$D\$44,\$D\$5:\$D\$44)),IF(sumProd-INT(sumProd)>=0.8,ROUND(sumProd,0)+1,ROUNDUP(sumProd,0)))),"")
i am very sorry the formula is not working properly I attached the sheet for your kind review

i am very sorry the formula is not working properly I attached the sheet for your kind review
Hello Jululian

We have reviewed your dataset and found that developing a formula using Excel's built-in functions to find sales rank based on department and section would be time-consuming.

So, we recommend using an Excel VBA User-defined function to meet your expectations when calculating sales rank.

1. Press Alt+F11 to open the VBE.
2. Click on Insert followed by Module.
3. Paste the following code in the Module and save it:
Code:
``````Function SalesRank(rngDepartment As Range, rngSection As Range, rngSales As Range, rngCellRange As Range) As Variant

Dim lastRow As Long
Dim currentDept As String
Dim currentSection As String
Dim currentSales As Long
Dim currentRank As Variant
Dim i As Long

lastRow = rngDepartment.Parent.Cells(rngDepartment.Parent.Rows.Count, rngDepartment.Column).End(xlUp).Row

currentDept = rngDepartment.Cells(1).Value
currentSection = rngSection.Cells(1).Value
currentSales = rngSales.Cells(1).Value
currentRank = 1

For i = 1 To lastRow
If rngDepartment.Cells(i).Value <> currentDept Or rngSection.Cells(i).Value <> currentSection Then
currentRank = 1
currentDept = rngDepartment.Cells(i).Value
currentSection = rngSection.Cells(i).Value
currentSales = rngSales.Cells(i).Value
End If

If rngSales.Cells(i).Value = 0 Then
currentRank = ""
Else
If rngSales.Cells(i).Value < currentSales Then
currentRank = currentRank + 1
End If
End If

currentSales = rngSales.Cells(i).Value

If rngCellRange.Cells(1).Row = rngSales.Cells(i).Row And rngCellRange.Cells(1).Column = rngSales.Cells(i).Column Then
SalesRank = currentRank
Exit Function
End If
Next i

SalesRank = currentRank

End Function``````
5. Apply the following formula:
=SalesRank(\$B\$2:\$B\$162,\$C\$2:\$C\$162,\$D\$2:\$D\$162,D2)
6. Use the Fill Handle icon to copy the formula down.
I am attaching the solution workbook for better understanding. I hope the User-defined function will help you; good luck.

Regards
Lutfor Rahman Shimanto
Excel & VBA Developer
ExcelDemy

#### Attachments

• Jululian (SOLVED).xlsm
41.3 KB · Views: 1
Hello Jululian

We have reviewed your dataset and found that developing a formula using Excel's built-in functions to find sales rank based on department and section would be time-consuming.

So, we recommend using an Excel VBA User-defined function to meet your expectations when calculating sales rank.

1. Press Alt+F11 to open the VBE.
2. Click on Insert followed by Module.
3. Paste the following code in the Module and save it:
Code:
``````Function SalesRank(rngDepartment As Range, rngSection As Range, rngSales As Range, rngCellRange As Range) As Variant

Dim lastRow As Long
Dim currentDept As String
Dim currentSection As String
Dim currentSales As Long
Dim currentRank As Variant
Dim i As Long

lastRow = rngDepartment.Parent.Cells(rngDepartment.Parent.Rows.Count, rngDepartment.Column).End(xlUp).Row

currentDept = rngDepartment.Cells(1).Value
currentSection = rngSection.Cells(1).Value
currentSales = rngSales.Cells(1).Value
currentRank = 1

For i = 1 To lastRow
If rngDepartment.Cells(i).Value <> currentDept Or rngSection.Cells(i).Value <> currentSection Then
currentRank = 1
currentDept = rngDepartment.Cells(i).Value
currentSection = rngSection.Cells(i).Value
currentSales = rngSales.Cells(i).Value
End If

If rngSales.Cells(i).Value = 0 Then
currentRank = ""
Else
If rngSales.Cells(i).Value < currentSales Then
currentRank = currentRank + 1
End If
End If

currentSales = rngSales.Cells(i).Value

If rngCellRange.Cells(1).Row = rngSales.Cells(i).Row And rngCellRange.Cells(1).Column = rngSales.Cells(i).Column Then
SalesRank = currentRank
Exit Function
End If
Next i

SalesRank = currentRank

End Function``````
View attachment 1289
5. Apply the following formula:
6. Use the Fill Handle icon to copy the formula down.
View attachment 1288
I am attaching the solution workbook for better understanding. I hope the User-defined function will help you; good luck.

Regards
Lutfor Rahman Shimanto
Excel & VBA Developer
ExcelDemy
many many thanks for a great solution and your response many thanks again

many many thanks for a great solution and your response many thanks again
Dear Jululian, thanks for your kind words. You are most welcome.

Members online
1
Guests online
13
Total visitors
14