Is there a way to sort a table without screwing up a scatter chart based on that table?

[JenniferMurphy]

Please bear with my rather long and involved intro. The question is at the end.

In my effort to convert Amazon-type ratings to meaningful z scores, I came up with the table below. Columns F-J are used to calculate columns L-P, which are used to calculate the mean and std dev, which are used to calculate the confidence intervals, which are used to adjust the ratings, which can then be converted to z scores.

Amazon Ratings.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
2																	Alpha	0.05					0.59490355	Coefficient
3																							-0.5	Exponent
4	SKU	Rtg	#Reviews	Rank1	5%	4%	3%	2%	1%	%Sum	#5	#4	#3	#2	#1	Mean	Std Dev	Conf Int	Rtg Adj	Rank2	RtgΔ	RankΔ	Conf Int2	CIΔ%	RtgZ
5	393081087	4.9	9,651	7	91%	6%	2%	0%	1%	100%	8,782	579	193	0	97	4.8600	0.5295	0.0061	4.894	4	0.006	+3	0.0061	0.0000%	+0.56
6	B0BS8G1ZB6	4.9	3,259	8	93%	5%	1%	0%	1%	100%	3,031	163	33	0	33	4.8900	0.4877	0.0104	4.890	5	0.010	+3	0.0104	0.0000%	+0.53
7	B08QTLYPVT	4.9	1,518	9	90%	8%	1%	0%	1%	100%	1,366	121	15	0	15	4.8600	0.5103	0.0153	4.885	6	0.015	+3	0.0153	0.0000%	+0.49
8	B0C2H44ZDY	4.9	604	10	91%	7%	2%	1%	0%	101%	550	42	12	6	0	4.9100	0.4722	0.0242	4.876	7	0.024	+3	0.0242	0.0000%	+0.42
9	B0BTSFVMLV	4.9	286	11	97%	1%	0%	1%	1%	100%	277	3	0	3	3	4.9200	0.5036	0.0352	4.865	8	0.035	+3	0.0352	0.0000%	+0.34
10	B0C2BMNHW2	5.0	221	1	95%	5%	0%	0%	0%	100%	210	11	0	0	0	4.9500	0.2179	0.0400	4.960	1	0.040	=0	0.0400	0.0000%	+1.04
11	B0BWH7788Y	5.0	193	2	97%	1%	2%	0%	0%	100%	187	2	4	0	0	4.9500	0.2958	0.0428	4.957	2	0.043	=0	0.0428	0.0000%	+1.02
12	B00KJ0CDUQ	4.9	151	12	91%	6%	2%	0%	1%	100%	137	9	3	0	2	4.8600	0.5295	0.0484	4.852	9	0.048	+3	0.0484	0.0000%	+0.25
13	B0BKRSD6HN	4.9	88	13	86%	14%	0%	0%	0%	100%	76	12	0	0	0	4.8600	0.3470	0.0634	4.837	10	0.063	+3	0.0634	0.0000%	+0.14
14	B0C1NWLRHM	5.0	85	3	100%	0%	0%	0%	0%	100%	85	0	0	0	0	5.0000	0.0000	0.0645	4.935	3	0.065	=0	0.0645	0.0000%	+0.86
15	B07WX53GCK	4.9	16	14	88%	12%	0%	0%	0%	100%	14	2	0	0	0	4.8800	0.3250	0.1487	4.751	12	0.149	+2	0.1487	0.0000%	-0.49
16	B09C8YYY2T	4.9	15	15	89%	11%	0%	0%	0%	100%	13	2	0	0	0	4.8900	0.3129	0.1536	4.746	13	0.154	+2	0.1536	0.0000%	-0.53
17	B0BN4GPZ14	4.9	11	16	88%	12%	0%	0%	0%	100%	10	1	0	0	0	4.8800	0.3250	0.1794	4.721	14	0.179	+2	0.1794	0.0000%	-0.71
18	B0BJ14XC5Q	5.0	10	4	100%	0%	0%	0%	0%	100%	10	0	0	0	0	5.0000	0.0000	0.1881	4.812	11	0.188	-7	0.1881	0.0000%	-0.04
19	B0BJNZ8TGX	5.0	4	5	100%	0%	0%	0%	0%	100%	4	0	0	0	0	5.0000	0.0000	0.2975	4.703	15	0.297	-10	0.2975	0.0000%	-0.85
20	B0BRMLQBG7	5.0	1	6	100%	0%	0%	0%	0%	100%	1	0	0	0	0	5.0000	0.0000	0.5949	4.405	16	0.595	-10	0.5949	0.0000%	-3.03
21	Averages	4.9	1,007		94%	6%	1%	0%	0%		922	59	16	1	9	4.9194	0.3035	0.1195	4.818				0.1195		-0.00
22	Standard Deviations																		0.136						+1.00
Chart & Sort

Cell Formulas
Range	Formula
L5:L20	L5	=[@['#Reviews]]*[@[5%]]
M5:M20	M5	=[@['#Reviews]]*[@[4%]]
N5:N20	N5	=[@['#Reviews]]*[@[3%]]
O5:O20	O5	=[@['#Reviews]]*[@[2%]]
P5:P20	P5	=[@['#Reviews]]*[@[1%]]
Q5:Q20	Q5	=SUM([@['#5]]:[@['#1]]*{5,4,3,2,1})/[@['#Reviews]]
R5:R20	R5	=SQRT(SUM(({5,4,3,2,1}-[@Mean])^2*[@['#5]]:[@['#1]])/[@['#Reviews]])
S5:S20	S5	=CONFIDENCE.NORM(Alpha,TblAmz3[[#Totals],[Std Dev]],[@['#Reviews]])
L21	L21	=SUBTOTAL(101,['#5])
M21	M21	=SUBTOTAL(101,['#4])
N21	N21	=SUBTOTAL(101,['#3])
O21	O21	=SUBTOTAL(101,['#2])
P21	P21	=SUBTOTAL(101,['#1])
Q21	Q21	=SUBTOTAL(101,[Mean])
R21	R21	=SUBTOTAL(101,[Std Dev])
S21	S21	=SUBTOTAL(101,[Conf Int])
U5:U20	U5	=RANK.EQ([@[Rtg Adj]],[Rtg Adj])
V5:V20	V5	=[@Rtg]-[@[Rtg Adj]]
W5:W20	W5	=[@Rank1]-[@Rank2]
E5:E20	E5	=RANK([@Rtg],[Rtg])+SUMPRODUCT(--([Rtg]=[@Rtg]),--([@['#Reviews]]<['#Reviews]))
K5:K20	K5	=SUM([@[5%]]:[@[1%]])
Y5:Y20	Y5	=([@[Conf Int2]]/[@[Conf Int]])-1
C21	C21	=SUBTOTAL(101,[Rtg])
D21	D21	=SUBTOTAL(101,['#Reviews])
F21	F21	=SUBTOTAL(101,[5%])
G21	G21	=SUBTOTAL(101,[4%])
H21	H21	=SUBTOTAL(101,[3%])
I21	I21	=SUBTOTAL(101,[2%])
J21	J21	=SUBTOTAL(101,[1%])
X5:X20	X5	=Coef * ([@['#Reviews]]^Expon)
X21	X21	=SUBTOTAL(101,[Conf Int2])
T5:T20	T5	=[@Rtg]-[@[Conf Int]]
T21	T21	=SUBTOTAL(101,[Rtg Adj])
T22	T22	=STDEV.S(TblAmz3[Rtg Adj])
Z5:Z20	Z5	=([@[Rtg Adj]]-TblAmz3[[#Totals],[Rtg Adj]])/RtgAdjStdDev
Z21	Z21	=SUBTOTAL(101,[RtgZ])
Z22	Z22	=STDEV.S(TblAmz3[RtgZ])

Named Ranges
Name	Refers To	Cells
'Chart & Sort'!Alpha	='Chart & Sort'!$S$2	S5:S20
'Chart & Sort'!Coef	='Chart & Sort'!$X$2	X5:X20
'Chart & Sort'!Expon	='Chart & Sort'!$X$3	X5:X20
'Chart & Sort'!RtgAdjStdDev	='Chart & Sort'!$T$22	Z5:Z20

To see whether there is a reliable relationship between the number of reviews and the confidence intervals, I plotted them in a scatter chart and added a trendline. It turns out that a power equation is a near perfect fit. I don't see a way to post a chart using xl2bb, so here it is as a graphic.

I then use that

I then used that equation to compute a confidence interval directly from the number of reviews. There is very little difference. This allows me to bypass all of the other math above and calculate adjusted ratings directly from the number of reviews.

Here's the problem. I would like to be able to sort the table in several ways, but if I do, the chart gets all screwed up. Here's what happens if I sort the table on the Rank1 field.

Or if I sort it on the SKU column.

Is there any way that I can set it up so that I can sort the table anyway I want without affecting the chart?

I do not want to freeze the chart, unless there is also a way to unfreeze it, as I may want to add additional rows to the table and have te chart and trendline updated.

Thanks. I hope this was not too muddled.

 

[RoryA]

A scatter chart shouldn't be affected by sorting unless the values being plotted are changed by sorting (the order shouldn't affect where they plot, after all).

 

[Alex Blakenburg]

If Rory's suggestion doesn't pan out for you, how about building the chart on a dynamically sorted table.

eg if the main table you are showing is is named Table1 and the order in your chart is column 3 in descending order then have a table using the formulas below as the basis for your chart.

Note: to create the chart I just made my active cell the cell containg the "sort" function, excel seemed to figure out the rest.

Book1
	B	C	D	E	F
25	Product	Amt	Col3	Col4	Index
26	Banana	100	6000	0	5
27	Apple	30	5000	0	2
28	Kiwi	10	2000	0	1
29	Pear	500	1000	0	3
30	Orange	40	500	0	4
31	carrot	1000	100	0	6
Sheet1

Cell Formulas
Range	Formula
B25:F25	B25	=Table1[#Headers]
B26:F31	B26	=SORT(Table1,3,-1)
Dynamic array formulas.

 

[Alex Blakenburg]

Hmmm for some reason that I don't understand just using sort is not automatically increasing the size of the range in the chart.
It only increased dynamically if added a filter into the mix (used a filter that would select everything)

Book1
	B	C	D	E	F
31	Product	Amt	Col3	Col4	Index
32	Banana	100	6000	0	5
33	Apple	30	5000	0	2
34	Kiwi	10	2000	0	1
35	Pear	500	1000	0	3
36	Add an Item	1000	1000	0	0
37	Item 7	800	900	0	0
38	Orange	40	500	0	4
Sheet1

Cell Formulas
Range	Formula
B31:F31	B31	=Table1[#Headers]
B32:F38	B32	=SORT(FILTER(Table1,Table1[Product]<>"",""),3,-1)
Dynamic array formulas.

 

[JenniferMurphy]

A scatter chart shouldn't be affected by sorting unless the values being plotted are changed by sorting (the order shouldn't affect where they plot, after all).

That's what I thought, but unless I am doing something wrong, it doesn't work that way. Here's a simpler example.

I started with this table that is sorted on the #Reviews column:

Amazon Ratings.xlsx
	C	D	E	F
5	Rtg	#Reviews	Rank	Conf
6	5	9,467	1	0.0132
7	3	9,101	2	0.0130
8	4	8,499	3	0.0136
9	5	744	4	0.0445
10	4	444	5	0.0608
11	3	427	6	0.0625
12	5	88	7	0.1328
13	4	56	8	0.1673
14	3	39	9	0.2045
15	4	9	10	0.4081
16	5	4	11	0.6270
17	3	2	12	0.8727
MrExcel

Cell Formulas
Range	Formula
E6:E17	E6	=RANK.EQ([@['#Reviews]],['#Reviews])

I made this plot with the #Reviews column on the X axis and the Conf column on the Y axis.

If I sort the table on Rtg / #Reviews:

Amazon Ratings.xlsx
	C	D	E	F
5	Rtg	#Reviews	Rank	Conf
6	5	9,467	1	0.0132
7	5	744	4	0.0445
8	5	88	7	0.1328
9	5	4	11	0.6270
10	4	8,499	3	0.0136
11	4	444	5	0.0608
12	4	56	8	0.1673
13	4	9	10	0.4081
14	3	9,101	2	0.0130
15	3	427	6	0.0625
16	3	39	9	0.2045
17	3	2	12	0.8727
MrExcel

Cell Formulas
Range	Formula
E6:E17	E6	=RANK.EQ([@['#Reviews]],['#Reviews])

I get this chart:

If I resort it on the #Reviews column, the chart is also restored.

???????????????

 

[JenniferMurphy]

If Rory's suggestion doesn't pan out for you,

Rory's made a suggestion? What was it.

how about building the chart on a dynamically sorted table.

I'll have to think about that.

 

[RoryA]

That was me being daft and not looking at what your lines were. If you join the points, then it will matter what order they are in.

 

[JenniferMurphy]

That was me being daft and not looking at what your lines were. If you join the points, then it will matter what order they are in.

What do you mean by "join the lines"?

 

[RoryA]

I mean you are using lines not just plotting the individual data points.

 

[JenniferMurphy]

I mean you are using lines not just plotting the individual data points.

I'm pretty sure that I am the one being daft.

You are exactly right. The chart did not change. It was just the lines between the points that changed. If I do a plot with just dots, it works.

I can sort the table any which way and the chart remains the same.

Thank you

PS: I'll have to think about whether I think this is a design bug...