I am working on a workbook that lists possible features for a product. The goal is to display all relevant data and allow multiple people to rank their most desirable features based on the same raw data. I am looking to create a single formula that will give a weighted composite 'score' for a given row. Each row will contain a number of scores (which can be multiplied by the weight and added easily) and some values that must be ranked. It is probably easier to explain with a similar example table:
N N N A D A
Col1 Col2 Col3 Rank1 Rank2 Rank3 ScoreA ScoreB ScoreC Col1 Col2 Col3 Rank1 Rank2 Rank3
1 5 1 03/20/2014 178 20% ? ? ? ScoreA 1 2 3 4 5 6
2 4 2 01/07/2010 50 25% ? ? ? ScoreB 0 0 1 5 0 3
3 3 3 03/17/2014 300 99% ? ? ? ScoreC 1 1 1 1 1 1
4 2 4 10/04/2011 10 15% ? ? ?
5 1 5 05/24/2013 3 70% ? ? ?

The portion on the left represents the raw data, which will in reality be much larger (more rows and columns). The portion on the right is the weighting system that I am hoping to use. It will be on a different sheet in the same workbook. Each person that uses this workbook will have a different weight (ScoreA, ScoreB, ScoreC) that they want to give to the various columns (marketing will favor low cost, development will favor easy implementation, etc.). The 'N', 'D', and 'A' values indicate the type of ranking needed for the data that is stored in the column. N means no ranking, A means higher values are better, D means lower values are better.

The first part of the formula somewhat straightforward use of Sumproduct. The columns labelled Col1, Col2, and Col3 can be multiplied by the corresponding weight directly and added. For the first row, this portion of ScoreA would be (1*1)+(5*2)+(1*3), ScoreB would be (1*0)+(5*0)+(1*1), ScoreC would be (1*1)+(5*1)+(1*1). This formula is working fine, I am just providing it in case the extra background helps in some way. The formula that I have for this is:
Please Login or Register  to view this content.
The second part of the formula is where I am running into trouble. I want to take the value in the Rank1 column, find the percent rank of that value, and multiply it by the corresponding weight. Then repeat for Rank2 and Rank3 and add them together. I am able to do this if I enter a long formula for each Rank... column that I have, but I would really prefer a formula that is similar to the one I entered above. Entering a large formula for each individual column makes it much harder to maintain and understand. The formula that works for a single column is:
Please Login or Register  to view this content.
I have a slightly more complicated formula that will check the N/A/D value before multiplying, but I left it off because I don't think it is necessary to understand the problem and it makes the formula much harder to read.

I am not sure that what I am trying to do is even possible, but I have been frequently surprised by things that excel has hidden away so I am hoping there is a solution that I have missed.

EDIT: Forgot to say: Thank you in advance for any help you are able to provide. Most of the time I am able to google my excel issues/questions and find a solution pretty quick, but this one has been bothering my for a while. Since so many of the solutions for my previous problems come from threads on this site I figured I would give it a shot.