Ranking and creating a graph from it.

[DaniDo]

Hello everyone,

I had tried to post this before but I thought my answer might be found elsewhere. I think a lot of the answers are specific to each person's data set, however, so this is my question:

I am trying to rank values for States in order. I need a top 9 and then the 10th ranking is every other state combined. Some states have the same value.

Does anyone know how I can rank them in a top 9 and then somehow get the pie chart to pull from that?

I've tried ranking the top nine but I need it to rank in alphabetical order when it comes across states with the same value. I've tried conditional formatting to show me the top 9 values but, again, it gives me more than 9 because of the states with the same values and then I don't know how to tell the pie chart to find the cells with the conditional formatting.

Can anyone help me with this? I will post an example if you need it.

Thanks,
Danielle

 

[GlennUK]

Hi Danielle,

extract the values you want into another area, and chart that. Here's an example of formulas to do that. Note that for the ranking of 10 the formulas are different, as per your requirements:

Excel Workbook
	A	B	C	D	E	F	G
1	State ( in alphabetical order )	Value	Unique rank		Top 9 area
2	Alabama	89	16		Rank	State	Value
3	Alaska	138	1		1	Alaska	138
4	Arizona	68	29		2	Arkansas	138
5	Arkansas	138	2		3	Connecticut	122
6	California	55	40		4	Minnesota	121
7	Colorado	68	30		5	South Dakota	117
8	Connecticut	122	3		6	Delaware	112
9	Delaware	112	6		7	Pennsylvania	110
10	Florida	62	36		8	Nevada	109
11	Georgia	72	27		9	Wisconsin	108
12	Hawaii	64	34		10	( other )	2748
13	Idaho	64	35
14	Illinois	36	47
15	Indiana	60	37
16	Iowa	76	22
17	Kansas	76	23
Sheet33

 

[DaniDo]

Glenn,

Wow- you're a genius! I'm not sure I can wrap my head around why those formulas work but they certainly do! I appreciate you taking all that extra time to show me an example. This forum is a fantastic place- I only wish I could help you with something in return.

Thanks again!

-Danielle

 

[GlennUK]

Glenn,

Wow- you're a genius! I'm not sure I can wrap my head around why those formulas work but they certainly do! I appreciate you taking all that extra time to show me an example. This forum is a fantastic place- I only wish I could help you with something in return.

Thanks again!

-Danielle

Hi Danielle, thanks for the lovely feedback, I'm glad you like the example.