Gap to Average Calculator

[RedBerry95]

Hi All,

Hoping one of you can help me out.

We run a survey programme based on a 5* average score. Performance is paid at different average score bands of over 4.5, 4.6, 4.7, 4.8 and 4.9

I have the data for the average score at each site, sample size of each site and the individual scores for every response.

I am trying to build a calculator that shows if X dealer has an average score of 4.66, they would require another X number of 5* responses to reach the next band of 4.7 and Y number to reach top band of 4 9.

Any help appreciated!

 

[awoohaw]

Are you familiar with the goal seek tool in Data Analysis section of Data Tool Bar?

Book1
	A	B	C	D	E	F	G	H	I	J
1
2	Dealer
3	A	3		A	4.00	68.00	17.00	5	39.65616	4.699944
4	B	4		B	4.35
5	C	5		C	3.63
6	A	3
7	B	4
8	C	3
9	A	3
10	B	4
11	C	3
12	A	5
13	B	5
14	C	3
15	A	5
16	B	4
17	C	3
18	A	3
19	B	4
20	C	4
21	A	4
22	B	5
23	C	4
24	A	4
25	B	4
26	C	5
27	A	4
28	B	5
29	C	3
30	A	5
31	B	3
32	C	3
33	A	5
34	B	5
35	C	3
36	A	3
37	B	5
38	C	3
39	A	5
40	B	5
41	C	5
42	A	3
43	B	3
44	C	4
45	A	3
46	B	4
47	C	3
48	A	5
49	B	5
50	C	4
51	A	5
52	B	5
53
Sheet1

Cell Formulas
Range	Formula
F3	F3	=SUMIFS($B$3:$B$52,$A$3:$A$52,D3)
G3	G3	=COUNTIFS($A$3:$A$52,D3)
J3	J3	=(F3+(I3*H3))/(G3+I3)
E3:E5	E3	=AVERAGEIFS($B$3:$B$52,$A$3:$A$52,D3)

HEre is a formula version:

Book1
	A	B	C	D	E	F	G	H
1
2	Dealer				Current	4.7	4.9
3	A	3		A	4.00	40	154
4	B	4		B	4.35	20	94
5	C	5		C	3.63	58	205
6	A	3
7	B	4
8	C	3
9	A	3
10	B	4
11	C	3
12	A	5
13	B	5
14	C	3
15	A	5
16	B	4
17	C	3
18	A	3
19	B	4
20	C	4
21	A	4
22	B	5
23	C	4
24	A	4
25	B	4
26	C	5
27	A	4
28	B	5
29	C	3
30	A	5
31	B	3
32	C	3
33	A	5
34	B	5
35	C	3
36	A	3
37	B	5
38	C	3
39	A	5
40	B	5
41	C	5
42	A	3
43	B	3
44	C	4
45	A	3
46	B	4
47	C	3
48	A	5
49	B	5
50	C	4
51	A	5
52	B	5
Sheet1

Cell Formulas
Range	Formula
E3:E5	E3	=AVERAGEIFS($B$3:$B$52,$A$3:$A$52,D3)
F3:G5	F3	=ROUNDUP(((F$2*COUNTIFS($A$3:$A$52,$D3))- SUMIFS($B$3:$B$52,$A$3:$A$52,$D3)) /(5-F$2),0)

 

[RedBerry95]

Are you familiar with the goal seek tool in Data Analysis section of Data Tool Bar?

View attachment 101426


View attachment 101427

Book1
	A	B	C	D	E	F	G	H	I	J
1
2	Dealer
3	A	3		A	4.00	68.00	17.00	5	39.65616	4.699944
4	B	4		B	4.35
5	C	5		C	3.63
6	A	3
7	B	4
8	C	3
9	A	3
10	B	4
11	C	3
12	A	5
13	B	5
14	C	3
15	A	5
16	B	4
17	C	3
18	A	3
19	B	4
20	C	4
21	A	4
22	B	5
23	C	4
24	A	4
25	B	4
26	C	5
27	A	4
28	B	5
29	C	3
30	A	5
31	B	3
32	C	3
33	A	5
34	B	5
35	C	3
36	A	3
37	B	5
38	C	3
39	A	5
40	B	5
41	C	5
42	A	3
43	B	3
44	C	4
45	A	3
46	B	4
47	C	3
48	A	5
49	B	5
50	C	4
51	A	5
52	B	5
53
Sheet1

Cell Formulas
Range	Formula
F3	F3	=SUMIFS($B$3:$B$52,$A$3:$A$52,D3)
G3	G3	=COUNTIFS($A$3:$A$52,D3)
J3	J3	=(F3+(I3*H3))/(G3+I3)
E3:E5	E3	=AVERAGEIFS($B$3:$B$52,$A$3:$A$52,D3)

HEre is a formula version:

Book1
	A	B	C	D	E	F	G	H
1
2	Dealer				Current	4.7	4.9
3	A	3		A	4.00	40	154
4	B	4		B	4.35	20	94
5	C	5		C	3.63	58	205
6	A	3
7	B	4
8	C	3
9	A	3
10	B	4
11	C	3
12	A	5
13	B	5
14	C	3
15	A	5
16	B	4
17	C	3
18	A	3
19	B	4
20	C	4
21	A	4
22	B	5
23	C	4
24	A	4
25	B	4
26	C	5
27	A	4
28	B	5
29	C	3
30	A	5
31	B	3
32	C	3
33	A	5
34	B	5
35	C	3
36	A	3
37	B	5
38	C	3
39	A	5
40	B	5
41	C	5
42	A	3
43	B	3
44	C	4
45	A	3
46	B	4
47	C	3
48	A	5
49	B	5
50	C	4
51	A	5
52	B	5
Sheet1

Cell Formulas
Range	Formula
E3:E5	E3	=AVERAGEIFS($B$3:$B$52,$A$3:$A$52,D3)
F3:G5	F3	=ROUNDUP(((F$2*COUNTIFS($A$3:$A$52,$D3))- SUMIFS($B$3:$B$52,$A$3:$A$52,$D3)) /(5-F$2),0)

Amazing, thank you very much!