• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

sort and number according to two criteria

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

  • Cartel1.xlsx
    12.7 KB · Views: 8
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

  • Cartel1.xlsx
    13.2 KB · Views: 8
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

  • Copy of Cartel1-2.xlsx
    16.4 KB · Views: 8
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.
 
Have a look at the attached solution. Consider previous remark, so my final rank does return a different result then your manual column.
 

Attachments

  • Cartel1-2_v2.xlsx
    15.8 KB · Views: 5
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

  • Cartel1-2_v2.xlsx
    16 KB · Views: 5
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)


you approached in the latest version.
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.
 
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

  • Cartel1-2_v2_With Helper Columns and manual sorting.xlsx
    22.3 KB · Views: 8
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 :)
 
Back
Top