Overall Ranking by other rankings

[Giggzz]

Payroll Rhonda Paydate 8 25 06.xls
	B	C	D	E	F	G	H
2	Avg Hourly Rate	Rank	Avg Hours	Rank	Avg Revenue	Rank	Overall Rank
3	$18.77	2	55.75	14	$1,038.52	1	13
4	$14.27	14	48.80	7	$350.67	11	11
5	$18.85	1	48.60	6	$914.98	2	3
6	$16.75	6	50.00	8	$836.39	3	6
7	$17.29	5	40.05	2	$230.03	12	6
8	$14.76	12	50.80	9	$387.08	10	10
9	$15.00	9	52.15	12	$0.00	13	No Ranking
10	$15.63	8	41.50	3	$653.98	8	6
11	$17.39	4	42.60	5	$459.67	9	6
12	$14.50	13	51.25	11	$741.64	6	10
13	$15.00	9	52.15	12	$696.98	7	9
14	$16.10	7	51.13	10	$820.86	4	7
15	$17.68	3	42.00	4	$755.50	5	4
16	$15.00	9	0.00	1	$0.00	13	No Ranking
Ranking

Good day...

Trying to come up with a overall ranking in column H bsed on the rankings in G,E,C. Any idea? Im dividing the 3 totals but that doesnt seem to be the right answer.

 

[Fergus]

Does the following, which uses an extra column, do what you want? Note that I have changed all your ranking columns to use the RANK function.

Overall Rank.xls
	B	C	D	E	F	G	H	I
2	AvgHourlyRate	Rank	AvgHours	Rank	AvgRevenue	Rank	Total of Ranks	OverallRank
3	$18.77	2	55.75	14	$1,038.52	1	17	3
4	$14.27	14	48.80	7	$350.67	11	32	12
5	$18.85	1	48.60	6	$914.98	2	9	1
6	$16.75	6	50.00	8	$836.39	3	17	4
7	$17.29	5	40.05	2	$230.03	12	19	6
8	$14.76	12	50.80	9	$387.08	10	31	11
9	$15.00	9	52.15	12	$0.00	13		NoRanking
10	$15.63	8	41.50	3	$653.98	8	19	7
11	$17.39	4	42.60	5	$459.67	9	18	5
12	$14.50	13	51.25	11	$741.64	6	30	9
13	$15.00	10	52.15	13	$696.98	7	30	10
14	$16.10	7	51.13	10	$820.86	4	21	8
15	$17.68	3	42.00	4	$755.50	5	12	2
16	$15.00	11	0.00	1	$0.00	14		NoRanking
Sheet2

Formula in I3, copied down, is: =IF(ISNUMBER(H3),RANK(H3,$H$3:$H$16,1)+COUNTIF($H$3:H3,H3)-1,"No Ranking")

 

[Giggzz]

The ranking calculation is producing a #DIV/0! error in column C when column B has no value to display. How can I get around that?

Thanks

 

[Fergus]

Mmm, interesting. I get a #N/A if a cell in column B contains nothing, but it works OK if there is a zero there. Columns E and G do not display #N/A even if the corresponding cells in D and F are blank.

The difference seems to be that both columns D and F already contain a zero, whereas column B does not. If you delete the zero(s) in columns D and F the same error occurs, but I don't know why. I've looked in Excel Help for RANK but can't find anything helpful there. Maybe some Formula Guru can explain why.

Meanwhile, as a workround you could make sure that the formula in column B does not return a blank but returns a zero, otherwise resort to using =IF(ISNUMBER() at the beginning of the RANK formula in column C