kiki burgh
New Member
- Joined
- Jun 9, 2014
- Messages
- 44
hello there! it's been a while since i last dropped by. once again, i am here seeking help in coming up with a Customer Satisfaction survey score predictor where possible scores in 1 survey are only: 100, 80, 60, 40, 20 & 0. say in order to pass, 1 must aim to have a 93% as an average score for all counts of survey. surveys may vary and as an example may range from 0 to 150.
discounting the reality that not all remaining surveys will be perfect 100s, what formula can i use to predict the number & scores of surveys i need to meet the goal?
below is a sample of what i have (that i feel is very manual):
<tbody>
</tbody>
thank you for your help.
discounting the reality that not all remaining surveys will be perfect 100s, what formula can i use to predict the number & scores of surveys i need to meet the goal?
below is a sample of what i have (that i feel is very manual):
A | B | C | D | E | F | G | |
1 | Formula | Formula | |||||
2 | Total of # Surveys | : | 12 | =SUM(C5:C10) | 1200 | =$C$2*100 | |
3 | Target | : | 93% | 1116 | =C3*$E$2 | ||
4 | Variance | : | 84 | =$E$2-$E$3 | |||
5 | 100 | : | 7 | 700 | =A5*C5 | ||
6 | 80 | : | 1 | 80 | =A6*C6 | ||
7 | 60 | : | 2 | 120 | =A7*C7 | ||
8 | 40 | : | 1 | 40 | =A8*C8 | ||
9 | 20 | : | 0 | 0 | =A9*C9 | ||
10 | 0 | : | 1 | 0 | =A10*C10 | ||
11 | Total | : | 940 | =SUM(E5:E10) | |||
12 | Variance | : | 176 | =E3-E11 | |||
13 | Target | : | 93% | ||||
14 | Average | : | 78.33% | =(E11/$C$2)*0.01 | |||
15 | Variance | : | 14.67% | =E13-E14 | |||
16 | |||||||
17 | Needed | : | 26 | =CEILING(C2*(C3-E14)/(1-C3),1) | Formula here only works if all remaining surveys are 100% | ||
18 | New Total # of Surveys | : | 38 | =C2+E17 | |||
19 | New Total | : | 3540 | =E11+(E17*100) | |||
20 | New Total | : | 93.16% | =(E19/E18)*.01 |
<tbody>
</tbody>
thank you for your help.