• 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

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

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
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

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.
 

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

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 :)
 
Top