How to show data graphically

[afdmello]

Hi Folks,
Hi folks,
I asked our team members to rank a product as 1, 2 and 3 and created a table based the ranking. 1 being the most preferred to 3 being the least preferred. You can see below how the data looks. Based on data product A is the most preferred. Is there a way to show this data graphically other than the table for quick understanding of our leaders?

Client Group	A​	B​	C​
1	1	2	3
2	1	2	3
3	1	2	3
4	1	3	2
5	2	1	3
6	1	3	2
1​	most preferred
2​	acceptable
3​	least preferred

Thanks in advance.

 

[awoohaw]

My suggestion would be to assign points to the ranking (like football and basketball ranking systems) and make a bar or column chart:

Book1
	A	B	C	D	E
1	Client Group	A	B	C
2	1	1	2	3
3	2	1	2	3
4	3	1	2	3
5	4	1	3	2
6	5	2	1	3
7	6	1	3	2
8
9
10
11	Client Group	A	B	C
12		17	11	8
13
14
Sheet1

Cell Formulas
Range	Formula
B12:D12	B12	=SUM(((($B$2:$D$7=1)*3)+(($B$2:$D$7=2)*2)+(($B$2:$D$7=3)*1))*($B$1:$D$1=B$11))

I am pretty sure there are more succinct formulas to get rating point totals than what I have above.

 

[afdmello]

My suggestion would be to assign points to the ranking (like football and basketball ranking systems) and make a bar or column chart:
View attachment 115046

Book1
	A	B	C	D	E
1	Client Group	A	B	C
2	1	1	2	3
3	2	1	2	3
4	3	1	2	3
5	4	1	3	2
6	5	2	1	3
7	6	1	3	2
8
9
10
11	Client Group	A	B	C
12		17	11	8
13
14
Sheet1

Cell Formulas
Range	Formula
B12:D12	B12	=SUM(((($B$2:$D$7=1)*3)+(($B$2:$D$7=2)*2)+(($B$2:$D$7=3)*1))*($B$1:$D$1=B$11))

I am pretty sure there are more succinct formulas to get rating point totals than what I have above.

Thank you,

My understanding of your formula is that we must have made 3 as the most preferred and 1 as the least preferred in our ranking. Am I correct?

 

[Skrej]

No, it's the opposite.

#1 (most preferred) gets 3 points for each instance. #2 gets two points, and #3 only gets one point. The option with the most points overall is then the most preferred choice. Otherwise if you just count up the vote values, your smallest number ends up being the favorite, which is sort of counter-intuitive on a chart.

You can fool around with the label options and have it show total points, or percentages, etc. There are several different chart types as well, but probably a bar graph or pie chart will work best for this kind of data. For example (using the same formulas in post #2), you can choose layouts and colors.

 

[awoohaw]

Thank you,

My understanding of your formula is that we must have made 3 as the most preferred and 1 as the least preferred in our ranking. Am I correct?

Yes, if you just use the assigned grade (and think the minimum of totals is the ideal) you must be certain that all "Letters" get a score as a 'blank' vote would skew the results. In USA college basketball there are about 300 teams, but most voters in those polls only vote on 20 or 25 teams. Therefore the number 1 team gets 25 points, number 2 gets 24, etc. all the way to number 25 getting 1 point.

And, yes you could tell your voters to assign 3 (or 25 in the basketball poll scenario above) to the most preferred and work without the conversion as well.

And, my personal preference is to avoid pie charts, and 3D anything. When the differences are very narrow the tilt of 3D as well as the geometry (area vs length) can cause confusion in the visualization (or require the clutter of labels). Plus pie charts take up a lot of space in a dashboard where other visualizations could be utilized. This is one reason why sparklines and icon sets are so elegantly informative when properly used.