Can anyone think of a way to rank these?

[cxc4]

[TABLE="width: 1571"]
<colgroup><col><col><col><col span="3"><col span="2"><col><col span="3"><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Ticker[/TD]
[TD]Company[/TD]
[TD]Current Price[/TD]
[TD]12 Month Risk/Reward[/TD]
[TD]Curr Price/ 52Wk High[/TD]
[TD]Curr Price/ 52Wk Low[/TD]
[TD]% Ch Price Year-to-Date[/TD]
[TD]Div Yield[/TD]
[TD]Anl EPS before NRI[/TD]
[TD]Current Fiscal Yr Cons Est[/TD]
[TD]Next Fiscal Yr Cons Est[/TD]
[TD]P/E using 12 mo EPS[/TD]
[TD]P/E using 12 mo Est[/TD]
[TD]Div 5yr Growth[/TD]
[TD]5 Yr Hist EPS Gr[/TD]
[TD]Next 3-5 Yr Est EPS Gr rate[/TD]
[TD]Price/ Sales[/TD]
[TD]Price/ Book[/TD]
[TD]Zacks Rank[/TD]
[TD]Avg Broker Rating[/TD]
[TD]Fiscal Month End[/TD]
[/TR]
[TR]
[TD]MCD[/TD]
[TD]Mcdonalds Corp[/TD]
[TD="align: right"]90.25[/TD]
[TD] [/TD]
[TD="align: right"]0.89[/TD]
[TD="align: right"]1.10[/TD]
[TD="align: right"]-10.05[/TD]
[TD="align: right"]3.10[/TD]
[TD="align: right"]5.27[/TD]
[TD="align: right"]5.55[/TD]
[TD="align: right"]6.15[/TD]
[TD="align: right"]16.84[/TD]
[TD="align: right"]15.38[/TD]
[TD="align: right"]18.04[/TD]
[TD="align: right"]15.54[/TD]
[TD="align: right"]9.86[/TD]
[TD="align: right"]3.34[/TD]
[TD="align: right"]6.27[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1.83[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD]CMCSA[/TD]
[TD]Comcast Corp A[/TD]
[TD="align: right"]31.35[/TD]
[TD] [/TD]
[TD="align: right"]0.97[/TD]
[TD="align: right"]1.59[/TD]
[TD="align: right"]32.22[/TD]
[TD="align: right"]2.07[/TD]
[TD="align: right"]1.58[/TD]
[TD="align: right"]1.90[/TD]
[TD="align: right"]2.22[/TD]
[TD="align: right"]18.77[/TD]
[TD="align: right"]15.14[/TD]
[TD="align: right"]22.84[/TD]
[TD="align: right"]21.03[/TD]
[TD="align: right"]14.66[/TD]
[TD="align: right"]1.44[/TD]
[TD="align: right"]1.77[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1.35[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD]DIS[/TD]
[TD]Disney Walt[/TD]
[TD="align: right"]47.36[/TD]
[TD] [/TD]
[TD="align: right"]0.97[/TD]
[TD="align: right"]1.63[/TD]
[TD="align: right"]26.29[/TD]
[TD="align: right"]1.27[/TD]
[TD="align: right"]2.54[/TD]
[TD="align: right"]3.01[/TD]
[TD="align: right"]3.46[/TD]
[TD="align: right"]17.22[/TD]
[TD="align: right"]14.11[/TD]
[TD="align: right"]11.15[/TD]
[TD="align: right"]5.63[/TD]
[TD="align: right"]12.60[/TD]
[TD="align: right"]2.04[/TD]
[TD="align: right"]2.13[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1.92[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD]OMC[/TD]
[TD]Omnicom Grp[/TD]
[TD="align: right"]47.61[/TD]
[TD] [/TD]
[TD="align: right"]0.92[/TD]
[TD="align: right"]1.32[/TD]
[TD="align: right"]6.80[/TD]
[TD="align: right"]2.52[/TD]
[TD="align: right"]3.33[/TD]
[TD="align: right"]3.68[/TD]
[TD="align: right"]4.10[/TD]
[TD="align: right"]14.17[/TD]
[TD="align: right"]12.21[/TD]
[TD="align: right"]17.66[/TD]
[TD="align: right"]0.75[/TD]
[TD="align: right"]11.55[/TD]
[TD="align: right"]0.93[/TD]
[TD="align: right"]3.20[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2.31[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD]TWX[/TD]
[TD]Time Warner Inc[/TD]
[TD="align: right"]37.73[/TD]
[TD] [/TD]
[TD="align: right"]0.97[/TD]
[TD="align: right"]1.36[/TD]
[TD="align: right"]4.40[/TD]
[TD="align: right"]2.76[/TD]
[TD="align: right"]2.89[/TD]
[TD="align: right"]3.21[/TD]
[TD="align: right"]3.64[/TD]
[TD="align: right"]12.58[/TD]
[TD="align: right"]10.98[/TD]
[TD="align: right"]7.68[/TD]
[TD="align: right"]-2.71[/TD]
[TD="align: right"]11.27[/TD]
[TD="align: right"]1.24[/TD]
[TD="align: right"]1.23[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1.81[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD]HD[/TD]
[TD]Home Depot[/TD]
[TD="align: right"]52.22[/TD]
[TD] [/TD]
[TD="align: right"]0.99[/TD]
[TD="align: right"]1.83[/TD]
[TD="align: right"]24.22[/TD]
[TD="align: right"]2.22[/TD]
[TD="align: right"]2.47[/TD]
[TD="align: right"]2.90[/TD]
[TD="align: right"]3.31[/TD]
[TD="align: right"]20.01[/TD]
[TD="align: right"]16.93[/TD]
[TD="align: right"]4.43[/TD]
[TD="align: right"]0.91[/TD]
[TD="align: right"]13.48[/TD]
[TD="align: right"]1.12[/TD]
[TD="align: right"]4.43[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1.74[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]TGT[/TD]
[TD]Target Corp[/TD]
[TD="align: right"]59.23[/TD]
[TD] [/TD]
[TD="align: right"]1.00[/TD]
[TD="align: right"]1.27[/TD]
[TD="align: right"]15.64[/TD]
[TD="align: right"]2.03[/TD]
[TD="align: right"]4.32[/TD]
[TD="align: right"]4.29[/TD]
[TD="align: right"]4.84[/TD]
[TD="align: right"]13.52[/TD]
[TD="align: right"]13.06[/TD]
[TD="align: right"]20.99[/TD]
[TD="align: right"]7.39[/TD]
[TD="align: right"]11.06[/TD]
[TD="align: right"]0.56[/TD]
[TD="align: right"]2.50[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1.76[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]SYY[/TD]
[TD]Sysco Corp[/TD]
[TD="align: right"]29.21[/TD]
[TD] [/TD]
[TD="align: right"]0.93[/TD]
[TD="align: right"]1.15[/TD]
[TD="align: right"]-0.41[/TD]
[TD="align: right"]3.70[/TD]
[TD="align: right"]1.98[/TD]
[TD="align: right"]2.02[/TD]
[TD="align: right"]2.10[/TD]
[TD="align: right"]14.11[/TD]
[TD="align: right"]14.44[/TD]
[TD="align: right"]6.36[/TD]
[TD="align: right"]4.01[/TD]
[TD="align: right"]6.33[/TD]
[TD="align: right"]0.55[/TD]
[TD="align: right"]3.50[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2.80[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]WMT[/TD]
[TD]Wal-Mart Stores[/TD]
[TD="align: right"]72.11[/TD]
[TD] [/TD]
[TD="align: right"]1.00[/TD]
[TD="align: right"]1.49[/TD]
[TD="align: right"]20.67[/TD]
[TD="align: right"]2.20[/TD]
[TD="align: right"]4.49[/TD]
[TD="align: right"]4.90[/TD]
[TD="align: right"]5.36[/TD]
[TD="align: right"]15.71[/TD]
[TD="align: right"]14.12[/TD]
[TD="align: right"]13.62[/TD]
[TD="align: right"]9.40[/TD]
[TD="align: right"]9.65[/TD]
[TD="align: right"]0.54[/TD]
[TD="align: right"]3.32[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2.18[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]GIS[/TD]
[TD]Genl Mills[/TD]
[TD="align: right"]38.65[/TD]
[TD] [/TD]
[TD="align: right"]0.94[/TD]
[TD="align: right"]1.11[/TD]
[TD="align: right"]-4.36[/TD]
[TD="align: right"]3.41[/TD]
[TD="align: right"]2.56[/TD]
[TD="align: right"]2.67[/TD]
[TD="align: right"]2.90[/TD]
[TD="align: right"]15.16[/TD]
[TD="align: right"]14.35[/TD]
[TD="align: right"]11.74[/TD]
[TD="align: right"]10.02[/TD]
[TD="align: right"]7.72[/TD]
[TD="align: right"]1.50[/TD]
[TD="align: right"]3.63[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1.71[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]KO[/TD]
[TD]Coca Cola Co[/TD]
[TD="align: right"]77.98[/TD]
[TD] [/TD]
[TD="align: right"]0.99[/TD]
[TD="align: right"]1.22[/TD]
[TD="align: right"]11.45[/TD]
[TD="align: right"]2.62[/TD]
[TD="align: right"]3.84[/TD]
[TD="align: right"]4.07[/TD]
[TD="align: right"]4.46[/TD]
[TD="align: right"]20.10[/TD]
[TD="align: right"]18.25[/TD]
[TD="align: right"]8.11[/TD]
[TD="align: right"]8.50[/TD]
[TD="align: right"]7.79[/TD]
[TD="align: right"]3.73[/TD]
[TD="align: right"]5.32[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1.69[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD]PEP[/TD]
[TD]Pepsico Inc[/TD]
[TD="align: right"]69.87[/TD]
[TD] [/TD]
[TD="align: right"]0.99[/TD]
[TD="align: right"]1.16[/TD]
[TD="align: right"]5.30[/TD]
[TD="align: right"]3.08[/TD]
[TD="align: right"]4.40[/TD]
[TD="align: right"]4.05[/TD]
[TD="align: right"]4.43[/TD]
[TD="align: right"]16.02[/TD]
[TD="align: right"]16.44[/TD]
[TD="align: right"]7.51[/TD]
[TD="align: right"]6.47[/TD]
[TD="align: right"]4.19[/TD]
[TD="align: right"]1.63[/TD]
[TD="align: right"]4.81[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1.67[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD]AVP[/TD]
[TD]Avon Prods Inc[/TD]
[TD="align: right"]15.81[/TD]
[TD] [/TD]
[TD="align: right"]0.55[/TD]
[TD="align: right"]1.05[/TD]
[TD="align: right"]-9.50[/TD]
[TD="align: right"]5.82[/TD]
[TD="align: right"]1.64[/TD]
[TD="align: right"]0.94[/TD]
[TD="align: right"]1.17[/TD]
[TD="align: right"]11.63[/TD]
[TD="align: right"]14.88[/TD]
[TD="align: right"]4.71[/TD]
[TD="align: right"]0.90[/TD]
[TD="align: right"]-1.80[/TD]
[TD="align: right"]0.61[/TD]
[TD="align: right"]4.13[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]2.85[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD]CL[/TD]
[TD]Colgate Palmoli[/TD]
[TD="align: right"]103.90[/TD]
[TD] [/TD]
[TD="align: right"]0.99[/TD]
[TD="align: right"]1.30[/TD]
[TD="align: right"]12.46[/TD]
[TD="align: right"]2.39[/TD]
[TD="align: right"]5.03[/TD]
[TD="align: right"]5.37[/TD]
[TD="align: right"]5.88[/TD]
[TD="align: right"]20.33[/TD]
[TD="align: right"]18.42[/TD]
[TD="align: right"]12.95[/TD]
[TD="align: right"]11.06[/TD]
[TD="align: right"]8.20[/TD]
[TD="align: right"]2.93[/TD]
[TD="align: right"]19.96[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2.48[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD]CLX[/TD]
[TD]Clorox Co[/TD]
[TD="align: right"]72.33[/TD]
[TD] [/TD]
[TD="align: right"]0.97[/TD]
[TD="align: right"]1.13[/TD]
[TD="align: right"]8.67[/TD]
[TD="align: right"]3.32[/TD]
[TD="align: right"]4.16[/TD]
[TD="align: right"]4.27[/TD]
[TD="align: right"]4.55[/TD]
[TD="align: right"]17.39[/TD]
[TD="align: right"]16.92[/TD]
[TD="align: right"]9.99[/TD]
[TD="align: right"]5.10[/TD]
[TD="align: right"]6.86[/TD]
[TD="align: right"]1.74[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3.07[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]PG[/TD]
[TD]Procter & Gambl[/TD]
[TD="align: right"]61.73[/TD]
[TD] [/TD]
[TD="align: right"]0.91[/TD]
[TD="align: right"]1.05[/TD]
[TD="align: right"]-7.47[/TD]
[TD="align: right"]3.64[/TD]
[TD="align: right"]3.93[/TD]
[TD="align: right"]3.91[/TD]
[TD="align: right"]4.26[/TD]
[TD="align: right"]15.79[/TD]
[TD="align: right"]15.76[/TD]
[TD="align: right"]10.17[/TD]
[TD="align: right"]4.67[/TD]
[TD="align: right"]7.01[/TD]
[TD="align: right"]1.99[/TD]
[TD="align: right"]2.63[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]2.22[/TD]
[TD="align: right"]6[/TD]
[/TR]
</tbody>[/TABLE]








Hey guys. I'm new here so I am sorry if I break any forum rules.

My question is as follows: I have an add-in that automatically generates the above data for me. There are many more columns and rows in the actual spread sheet, but it contains some proprietary data. What I would like to do is create a ranking system for these companies using the data in the columns. I would like to give certain columns more weight than others and in the end have useful data(i.e. a flat 1-100 ranking, or maybe they each have a score out of 20). The problem is that in some columns a low number is good while in another a high number is good, or a low absolute value is the best.

I am relatively proficient in excel so even if you just point me to a good formula or outline a possible way I would appreciate it.

 

[pplstuff]

rank() large() small()

You can set rank to look in ascending or descending order.

As for assigning a weight, it would just depend on your specific needs.

 

[cxc4]

rank() large() small()

You can set rank to look in ascending or descending order.

As for assigning a weight, it would just depend on your specific needs.

Okay, Thank you. How would you rank based on cell value's distance from 0?

 

[pplstuff]

Create an absolute value column.

=abs()

 

[cxc4]

Create an absolute value column.

=abs()

Thanks.

Now I only have to wonder how I will be doing the math for the ranking. If I do it so that all your rankings are summed (then the lower the number the better the ranking) the only way to weight one column more than another is to multiply it by a factor of whatever you please. The problem is if you're ranked 2 in a column it won't affect your bottom line the way being in 20th will.

Any other ideas on how to do the math for the ranking.
Info:
n = # of companies
1.) I have all of the data columns ranked 1-n in a separate worksheet
2.) I want to generate an all encompassing ranking system so I can rate my portfolio's different holdings.
3.) I would like to be able to weight the ranking columns one more than another.




Another idea:
I think that maybe a score where your ranking is multiplied like so

[TABLE="width: 500"]
<tbody>[TR]
[TD]Company Name[/TD]
[TD]P/S ratio[/TD]
[TD="align: center"]P/S ratio
ranking[/TD]
[TD="align: center"]P/S ratio ranking value
added to overall score[/TD]
[/TR]
[TR]
[TD]company 1[/TD]
[TD]1.75[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]

n-15 P/S ratio's predetermined
n x weight​

[/TD]
[/TR]
[TR]
[TD]company 2[/TD]
[TD]0.30[/TD]
[TD="align: center"]81[/TD]
[TD="align: center"]

n-81 P/S ratio's predetermined
n x weight​

[/TD]
[/TR]
[TR]
[TD]company 3...[/TD]
[TD]2.34[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]

n-10 P/S ratio's predetermined
n x weight​

[/TD]
[/TR]
[TR]
[TD]company n[/TD]
[TD]1.32[/TD]
[TD="align: center"]31[/TD]
[TD="align: center"]

n-31 P/S ratio's predetermined
n x weight​

[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 500"]
<tbody>[TR]
[TD]Company Name[/TD]
[TD]Div
Yield[/TD]
[TD="align: center"]Div Yeild
ranking[/TD]
[TD="align: center"]Div Yeild ranking value
added to overall score[/TD]
[/TR]
[TR]
[TD]company 1[/TD]
[TD]4.33[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]

n-10 Div Yield predetermined
n x weight​

[/TD]
[/TR]
[TR]
[TD]company 2[/TD]
[TD]1.69[/TD]
[TD="align: center"]57[/TD]
[TD="align: center"]

n-57 Div Yield predetermined
n x weight​

[/TD]
[/TR]
[TR]
[TD]company 3...[/TD]
[TD]2.22[/TD]
[TD="align: center"]30[/TD]
[TD="align: center"]

n-30 Div Yield predetermined
n x weight​

[/TD]
[/TR]
[TR]
[TD]company n[/TD]
[TD]0.20[/TD]
[TD="align: center"]71[/TD]
[TD="align: center"]

n-71 Div Yield predetermined
n x weight​

[/TD]
[/TR]
</tbody>[/TABLE]

Some imaginary numbers:
n = 90
P/S predetermined weight = 2
Div Yeild predetermined weight = 3

In as much the best ranking you can have is 5

Company 1
[TABLE="width: 500"]
<tbody>[TR]
[TD]P/S:[/TD]
[TD]Div Yeild:[/TD]
[TD="align: center"]Summed[/TD]
[/TR]
[TR]
[TD](90-15)/90 = 0.833[/TD]
[TD](90-10)/90 = 0.889 [/TD]
[TD="align: right"] 1.667[/TD]
[/TR]
[TR]
[TD]0.833 * 2 = 1.667[/TD]
[TD]0.889 * 3 = 2.667[/TD]
[TD="align: right"] + 2.667[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"] 4.333[/TD]
[/TR]
</tbody>[/TABLE]


Company 2
[TABLE="width: 500"]
<tbody>[TR]
[TD]P/S:[/TD]
[TD]Div Yeild:[/TD]
[TD="align: center"]Summed[/TD]
[/TR]
[TR]
[TD](90-81)/90 = 0.100[/TD]
[TD](90-57)/90 = 0.367[/TD]
[TD="align: right"]0.367[/TD]
[/TR]
[TR]
[TD]0.100 * 2 = 0.200[/TD]
[TD] 0.367 * 3 = 1.100[/TD]
[TD="align: right"]+ 1.100[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1.467[/TD]
[/TR]
</tbody>[/TABLE]

Company 3 Summed
P/S: Div Yeild: 1.778
+ 2.000
3.778
[TABLE="width: 500"]
<tbody>[TR]
[TD]P/S:[/TD]
[TD]Div Yeild:[/TD]
[TD="align: center"]Summed[/TD]
[/TR]
[TR]
[TD](90-10)/90 = 0.889[/TD]
[TD](90-30)/90 = 0.667[/TD]
[TD="align: right"] 0.667[/TD]
[/TR]
[TR]
[TD]0.889 * 2 = 1.778[/TD]
[TD] 0.667 * 3 = 2.000[/TD]
[TD="align: right"]2.000[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3.778[/TD]
[/TR]
</tbody>[/TABLE]

RANKINGS:
1.) Company 1 with a score of 4.333
2.) Company 3 with a score of 3.778
3.) Company 2 with a score of 1.467