# sort and number according to two criteria

#### stefanoste78

##### Member
Good evening.
The formula in the column sorts a column based on the interest rate (largest to smallest).
It is possible to add a second criterion to be followed to the first which takes into account the longer maturity between rates considered for the whole part.
I entered the numbering starting from the largest rate up to 4% to make you understand.
Thanks

#### Attachments

• 12.7 KB Views: 7

#### stefanoste78

##### Member
To better explain the result that I would like to obtain I created the column of the integer value of the rate and then I made sort by rate (from the largest to the smallest) as the first parameter and then by expiry (from the largest to the smallest expiry) as second parameter.

With formulas, is it possible to extrapolate the whole value of the percentages and then order the rows according to the indications given before?

#### Attachments

• 13.2 KB Views: 8

#### GraH - Guido

##### Well-Known Member
Hi stefanoste78,

Would the attached make sense to you?
- I made a Combined Index [G2]= RANK.EQ(C2,C\$2:C\$100,0)*10^6+RANK.EQ(B2,B\$2:B\$100,0)
- Followed by the global ranking on this index [H2]= RANK(G2,\$G\$2:\$G\$100,1)

#### Attachments

• 16.4 KB Views: 8

#### stefanoste78

##### Member
Hi.
Thanks for your intervention, only now I have been able to examine it for the covid problem.
the result that the formula must obtain must be equal to column "E".
In my file there is no column "D" of which I would also like to know how to extrapolate the integer value only (if possible).
Column "E" is obtained first by ordering column "D" from largest to smallest (9,8,7 ...). Then for the same number there is an order that with reference to the class of value considered (the first value that is repeated is 6) orders from largest to smallest based on the parameter of column "B".
So for the first value that repeats (the 6 in column "D") there will first be the percentage value with 4.044 because it is greater than 2.767. the same criterion will be followed each time there are integer values of the percentage that will repeat itself.

#### GraH - Guido

##### Well-Known Member
Hi Stefano,

row 5, the percentage is 5,999999...54%. So your integer should be 5 not 6?

#### GraH - Guido

##### Well-Known Member
Have a look at the attached solution. Consider previous remark, so my final rank does return a different result then your manual column.

#### Attachments

• 15.8 KB Views: 5

#### GraH - Guido

##### Well-Known Member
To have it your way, I included a rounding first
[F2]=(SUMPRODUCT((--(INT(ROUND(\$C\$2:\$C\$100,3)*100)=INT(ROUND(\$C2,3)*100))),(--(B2<\$B\$2:\$B\$100)))+1)-(INT(ROUND(\$C2,3)*100)*1000)
[G2]=RANK.EQ(F2,\$F\$2:\$F\$100,1)

#### Attachments

• 16 KB Views: 5

#### stefanoste78

##### Member
To have it your way, I included a rounding first
[F2]=(SUMPRODUCT((--(INT(ROUND(\$C\$2:\$C\$100,3)*100)=INT(ROUND(\$C2,3)*100))),(--(B2<\$B\$2:\$B\$100)))+1)-(INT(ROUND(\$C2,3)*100)*1000)
[G2]=RANK.EQ(F2,\$F\$2:\$F\$100,1)

Logically, however, the government bond market from a different assessment.
Basically, with the same integer value (example: 2.3 - 2.5 - 2.9, the integer value preceding the comma is 2) from a higher price to securities with a longer maturity.
So in your formula you don't have to do the rounding but you have to consider only the figure that precedes the comma not conderating the decimal values at all.

#### GraH - Guido

##### Well-Known Member
Then #6 will do, no?

#### stefanoste78

##### Member
if you compare the columns you will see that there is no match for any values

#### GraH - Guido

##### Well-Known Member
Hi Stefano,

Have you seen my remark in #5. You have a rank based on integer 6, where the value is actually 5,999999...54% thus the integer is 5.
Then indeed the final rank will also shift.

To show it, I've added some helper columns which I then sorted Rank1 then Rank 2. This way the values are sorted according to final rank. It's exactly the same as my formula.

Either I'm still not getting what you are after, or there were some errors in the manual ranking provided in the file. Please have a closer look and come back.

#### Attachments

• 22.3 KB Views: 7

#### stefanoste78

##### Member
Hi Stefano,

Have you seen my remark in #5. You have a rank based on integer 6, where the value is actually 5,999999...54% thus the integer is 5.
Then indeed the final rank will also shift.

To show it, I've added some helper columns which I then sorted Rank1 then Rank 2. This way the values are sorted according to final rank. It's exactly the same as my formula.

Either I'm still not getting what you are after, or there were some errors in the manual ranking provided in the file. Please have a closer look and come back.

I just noticed that in the interest rate column the difference is due to the fact that the rate is obtained by rounding.

So you've been perfect in the evaluation and just use the formula in the last column.
Thanks 