Can anyone think of a way to rank these?

[cxc4]

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

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

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

Company Name	P/S ratio	P/S ratio ranking	P/S ratio ranking value added to overall score
company 1	1.75	15	n-15 P/S ratio's predetermined n x weight​
company 2	0.30	81	n-81 P/S ratio's predetermined n x weight​
company 3...	2.34	10	n-10 P/S ratio's predetermined n x weight​
company n	1.32	31	n-31 P/S ratio's predetermined n x weight​

<tbody>
</tbody>

Company Name	Div Yield	Div Yeild ranking	Div Yeild ranking value added to overall score
company 1	4.33	10	n-10 Div Yield predetermined n x weight​
company 2	1.69	57	n-57 Div Yield predetermined n x weight​
company 3...	2.22	30	n-30 Div Yield predetermined n x weight​
company n	0.20	71	n-71 Div Yield predetermined n x weight​

<tbody>
</tbody>

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

P/S:	Div Yeild:	Summed
(90-15)/90 = 0.833	(90-10)/90 = 0.889	1.667
0.833 * 2 = 1.667	0.889 * 3 = 2.667	+ 2.667
		4.333

<tbody>
</tbody>

Company 2

P/S:	Div Yeild:	Summed
(90-81)/90 = 0.100	(90-57)/90 = 0.367	0.367
0.100 * 2 = 0.200	0.367 * 3 = 1.100	+ 1.100
		1.467

<tbody>
</tbody>

Company 3 Summed
P/S: Div Yeild: 1.778
+ 2.000
3.778

P/S:	Div Yeild:	Summed
(90-10)/90 = 0.889	(90-30)/90 = 0.667	0.667
0.889 * 2 = 1.778	0.667 * 3 = 2.000	2.000
		3.778

<tbody>
</tbody>

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