Ranking Help

[bryan3228]

Hi There,

I am trying to get a weighted ranking that uses dollars as the highest weighting. We are looking at a list or items and evaluating their weeks spent below their intend MSRP and wanted to rank them by importance using a count of the weeks below MSRP and their YTD Sales to rank them.

I am not sure if what I am asking for is possible. In the attached example, the list is small. In the data set i am working with, there are hundreds of entries.




[TABLE="width: 957"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 9, align: center"]Weeks[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]MSRP[/TD]
[TD] YTD Volume[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]
[TD]Count below MSRP[/TD]
[TD]Desired Rank[/TD]
[/TR]
[TR]
[TD]Item1[/TD]
[TD="align: right"]1.99[/TD]
[TD] $ 100,000[/TD]
[TD] $ 1.39[/TD]
[TD] $ 1.39[/TD]
[TD] $ 1.39[/TD]
[TD] $ 1.39[/TD]
[TD] $ 1.39[/TD]
[TD] $ 1.39[/TD]
[TD] $ 1.39[/TD]
[TD] $ 1.39[/TD]
[TD] $ 1.39[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Item2[/TD]
[TD="align: right"]2.99[/TD]
[TD] $ 150,000[/TD]
[TD] $ 2.99[/TD]
[TD] $ 2.99[/TD]
[TD] $ 2.99[/TD]
[TD] $ 2.99[/TD]
[TD] $ 2.99[/TD]
[TD] $ 2.99[/TD]
[TD] $ 2.84[/TD]
[TD] $ 2.84[/TD]
[TD] $ 2.84[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]Item3[/TD]
[TD="align: right"]3.99[/TD]
[TD] $ 190,000[/TD]
[TD] $ 3.99[/TD]
[TD] $ 3.99[/TD]
[TD] $ 3.99[/TD]
[TD] $ 3.99[/TD]
[TD] $ 3.99[/TD]
[TD] $ 3.99[/TD]
[TD] $ 3.99[/TD]
[TD] $ 3.99[/TD]
[TD] $ 3.99[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]Item4[/TD]
[TD="align: right"]4.99[/TD]
[TD] $ 50,000[/TD]
[TD] $ 3.99[/TD]
[TD] $ 3.99[/TD]
[TD] $ 3.99[/TD]
[TD] $ 3.99[/TD]
[TD] $ 3.99[/TD]
[TD] $ 3.99[/TD]
[TD] $ 3.99[/TD]
[TD] $ 3.99[/TD]
[TD] $ 3.99[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Item5[/TD]
[TD="align: right"]5.99[/TD]
[TD] $ 250,000[/TD]
[TD] $ 3.59[/TD]
[TD] $ 3.59[/TD]
[TD] $ 3.59[/TD]
[TD] $ 3.59[/TD]
[TD] $ 3.59[/TD]
[TD] $ 3.59[/TD]
[TD] $ 3.59[/TD]
[TD] $ 3.59[/TD]
[TD] $ 3.59[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Item6[/TD]
[TD="align: right"]6.99[/TD]
[TD] $ 1,000,000[/TD]
[TD] $ 3.50[/TD]
[TD] $ 3.50[/TD]
[TD] $ 3.50[/TD]
[TD] $ 3.50[/TD]
[TD] $ 3.50[/TD]
[TD] $ 3.50[/TD]
[TD] $ 3.50[/TD]
[TD] $ 3.50[/TD]
[TD] $ 3.50[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Item7[/TD]
[TD="align: right"]7.99[/TD]
[TD] $ 75,000[/TD]
[TD] $ 7.99[/TD]
[TD] $ 7.99[/TD]
[TD] $ 7.99[/TD]
[TD] $ 7.99[/TD]
[TD] $ 7.99[/TD]
[TD] $ 7.99[/TD]
[TD] $ 7.99[/TD]
[TD] $ 7.99[/TD]
[TD] $ 7.99[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]Item8[/TD]
[TD="align: right"]8.99[/TD]
[TD] $ 5,000[/TD]
[TD] $ 4.50[/TD]
[TD] $ 4.50[/TD]
[TD] $ 4.50[/TD]
[TD] $ 4.50[/TD]
[TD] $ 4.50[/TD]
[TD] $ 4.50[/TD]
[TD] $ 4.50[/TD]
[TD] $ 4.50[/TD]
[TD] $ 4.50[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]6[/TD]
[/TR]
</tbody>[/TABLE]

 

[steve the fish]

Im not clear why your rank 5 is higher than your rank 6?

 

[steve the fish]

Try this:

=SUMPRODUCT(--(M3*10^12+C3<$M$3:$M$10*10^12+$C$3:$C$10))+1

 

[bryan3228]

Hi Steve,

5 was higher than 6 because the YTD volume was so much higher

 

[steve the fish]

Sure but 9 is larger than 3 and that takes priority yes? What i gave you will not produce same figures as yours.