Gap to Average Calculator

RedBerry95

New Member
Joined
Nov 3, 2023
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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!
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Are you familiar with the goal seek tool in Data Analysis section of Data Tool Bar?

1699021888666.png



1699021932387.png





Book1
ABCDEFGHIJ
1
2Dealer
3A3A4.0068.0017.00539.656164.699944
4B4B4.35
5C5C3.63
6A3
7B4
8C3
9A3
10B4
11C3
12A5
13B5
14C3
15A5
16B4
17C3
18A3
19B4
20C4
21A4
22B5
23C4
24A4
25B4
26C5
27A4
28B5
29C3
30A5
31B3
32C3
33A5
34B5
35C3
36A3
37B5
38C3
39A5
40B5
41C5
42A3
43B3
44C4
45A3
46B4
47C3
48A5
49B5
50C4
51A5
52B5
53
Sheet1
Cell Formulas
RangeFormula
F3F3=SUMIFS($B$3:$B$52,$A$3:$A$52,D3)
G3G3=COUNTIFS($A$3:$A$52,D3)
J3J3=(F3+(I3*H3))/(G3+I3)
E3:E5E3=AVERAGEIFS($B$3:$B$52,$A$3:$A$52,D3)



HEre is a formula version:

Book1
ABCDEFGH
1
2DealerCurrent4.74.9
3A3A4.0040154
4B4B4.352094
5C5C3.6358205
6A3
7B4
8C3
9A3
10B4
11C3
12A5
13B5
14C3
15A5
16B4
17C3
18A3
19B4
20C4
21A4
22B5
23C4
24A4
25B4
26C5
27A4
28B5
29C3
30A5
31B3
32C3
33A5
34B5
35C3
36A3
37B5
38C3
39A5
40B5
41C5
42A3
43B3
44C4
45A3
46B4
47C3
48A5
49B5
50C4
51A5
52B5
Sheet1
Cell Formulas
RangeFormula
E3:E5E3=AVERAGEIFS($B$3:$B$52,$A$3:$A$52,D3)
F3:G5F3=ROUNDUP(((F$2*COUNTIFS($A$3:$A$52,$D3))- SUMIFS($B$3:$B$52,$A$3:$A$52,$D3)) /(5-F$2),0)
 
Last edited:
Upvote 0
Are you familiar with the goal seek tool in Data Analysis section of Data Tool Bar?

View attachment 101426


View attachment 101427




Book1
ABCDEFGHIJ
1
2Dealer
3A3A4.0068.0017.00539.656164.699944
4B4B4.35
5C5C3.63
6A3
7B4
8C3
9A3
10B4
11C3
12A5
13B5
14C3
15A5
16B4
17C3
18A3
19B4
20C4
21A4
22B5
23C4
24A4
25B4
26C5
27A4
28B5
29C3
30A5
31B3
32C3
33A5
34B5
35C3
36A3
37B5
38C3
39A5
40B5
41C5
42A3
43B3
44C4
45A3
46B4
47C3
48A5
49B5
50C4
51A5
52B5
53
Sheet1
Cell Formulas
RangeFormula
F3F3=SUMIFS($B$3:$B$52,$A$3:$A$52,D3)
G3G3=COUNTIFS($A$3:$A$52,D3)
J3J3=(F3+(I3*H3))/(G3+I3)
E3:E5E3=AVERAGEIFS($B$3:$B$52,$A$3:$A$52,D3)



HEre is a formula version:

Book1
ABCDEFGH
1
2DealerCurrent4.74.9
3A3A4.0040154
4B4B4.352094
5C5C3.6358205
6A3
7B4
8C3
9A3
10B4
11C3
12A5
13B5
14C3
15A5
16B4
17C3
18A3
19B4
20C4
21A4
22B5
23C4
24A4
25B4
26C5
27A4
28B5
29C3
30A5
31B3
32C3
33A5
34B5
35C3
36A3
37B5
38C3
39A5
40B5
41C5
42A3
43B3
44C4
45A3
46B4
47C3
48A5
49B5
50C4
51A5
52B5
Sheet1
Cell Formulas
RangeFormula
E3:E5E3=AVERAGEIFS($B$3:$B$52,$A$3:$A$52,D3)
F3:G5F3=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!
 
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top