Variable Weight Question

[clcrabtr]

Hi,

I am building out a ranking of companies to try to establish their interest in donating to my nonprofit. The variables include Strong/Average/Weak connections to my organization (30%), the amount they have donated to us in the past (30%), the amount of giving to our general field (20%), and whether or not they've given to groups with our specific cause (20%). I want to formalize these into a template so that my coworkers can use it in the future, but I can't figure out how to fully automate the results.

For the moment I have just ranked the amount donated to us relative to the other companies in the chart, but then I have to divide each number by a specific amount to make the maximum value worth 3 points. Is there a way to do this so that the value automatically calculates the highest number to the specified value (in this case 3 or 2) and then adjusts the rest relative to that? Or something more elegant than that.

[dflak]

Could you provide a workbook with some sample (non-sensitive) data and perhaps an example of what you have vs. what you want? It would make it easier to see the issue and provide a solution.

[clcrabtr]

Attached is a sample spreadsheet. I'd like columns J and M to be worth up to 30% each, and columns O and Q to be worth 20% each. Is there a simpler way to do that than what I've come up with here?

[dflak]

I am not sure if this is what you want. Also I am a bit fuzzy on the two scores you assign by rank, but they are not relevant to the solution.

First of all, I converted the data into an Excel table mainly because the range is extensible - as you add data, the formulas "expand" to cover the data. This is particularly important with the RANK formulas. Also formulas can be expressed in terms of column headers which often make more sense than column letters. Here is information on Excel Tables: http://www.utteraccess.com/wiki/inde...ables_in_Excel.

Also I had to modify the formulas a bit. Instead of assigning blank when there is no data, I assigned zero so the following formula in Column E would work:
=SUM(0.3*[@Score],0.3*[@Score2],0.2*[@Score3],0.2*[@Score4])

[clcrabtr]

This looks great, and I appreciate the refresher on Tables. I'd initially started there, but got a bit bogged down in it. The only issue I still have is in the Score 2 and Score 3 columns where it divides by 4 and 8.5 respectively. Is there any way to set that to automatically divide by the amount required to make the maximum score 3? Or, failing that, is there any way to just divide it by the number of total responses so that the maximum score would be 1? Then I could just multiply it to weight each column? I tried to figure something out with a /(Count([Rank])) but couldn't get it to work.

[dflak]

"Normalizing" the Scores to 3 will result in a circular reference error unless we resort to VB. You compute these values and then you want to take the maximum of the computed values and apply them back to the values.

Explain to me the logic behind using RANK to get this Score. Perhaps an adjustment can be made there.

[clcrabtr]

It was just the best way I could think of. Because the values range from $1m+ to $100, I couldn't think of a better way to position the values relative to each other and then to base it on a 0-3 scale. I'm very open to any other suggestions you may have though!

[dflak]

I figured out a way around the circular error, but it requires a couple of helper columns (so labeled in the attachment). These helper columns can be hidden.

[clcrabtr]

This is awesome (and ingenious)! Thanks so much for your help.