CSAT Score Predictor

[kiki burgh]

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):

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Formula[/TD]
[TD][/TD]
[TD]Formula[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Total of # Surveys[/TD]
[TD]:[/TD]
[TD]12[/TD]
[TD]=SUM(C5:C10)[/TD]
[TD]1200[/TD]
[TD]=$C$2*100[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Target[/TD]
[TD]:[/TD]
[TD]93%[/TD]
[TD][/TD]
[TD]1116[/TD]
[TD]=C3*$E$2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Variance[/TD]
[TD]:[/TD]
[TD][/TD]
[TD][/TD]
[TD]84[/TD]
[TD]=$E$2-$E$3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]100[/TD]
[TD]:[/TD]
[TD]7[/TD]
[TD][/TD]
[TD]700[/TD]
[TD]=A5*C5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]80[/TD]
[TD]:[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]80[/TD]
[TD]=A6*C6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]60[/TD]
[TD]:[/TD]
[TD]2[/TD]
[TD][/TD]
[TD]120[/TD]
[TD]=A7*C7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]40[/TD]
[TD]:[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]40[/TD]
[TD]=A8*C8[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]20[/TD]
[TD]:[/TD]
[TD]0[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]=A9*C9[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]0[/TD]
[TD]:[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]=A10*C10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Total[/TD]
[TD]:[/TD]
[TD][/TD]
[TD][/TD]
[TD]940[/TD]
[TD]=SUM(E5:E10)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Variance[/TD]
[TD]:[/TD]
[TD][/TD]
[TD][/TD]
[TD]176[/TD]
[TD]=E3-E11[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]Target[/TD]
[TD]:[/TD]
[TD][/TD]
[TD][/TD]
[TD]93%[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]Average[/TD]
[TD]:[/TD]
[TD][/TD]
[TD][/TD]
[TD]78.33%[/TD]
[TD]=(E11/$C$2)*0.01[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]Variance[/TD]
[TD]:[/TD]
[TD][/TD]
[TD][/TD]
[TD]14.67%[/TD]
[TD]=E13-E14[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]Needed[/TD]
[TD]:[/TD]
[TD][/TD]
[TD][/TD]
[TD]26[/TD]
[TD]=CEILING(C2*(C3-E14)/(1-C3),1)[/TD]
[TD]Formula here only works if all remaining surveys are 100%[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]New Total # of Surveys[/TD]
[TD]:[/TD]
[TD][/TD]
[TD][/TD]
[TD]38[/TD]
[TD]=C2+E17[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]New Total[/TD]
[TD]:[/TD]
[TD][/TD]
[TD][/TD]
[TD]3540[/TD]
[TD]=E11+(E17*100)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]New Total[/TD]
[TD]:[/TD]
[TD][/TD]
[TD][/TD]
[TD]93.16%[/TD]
[TD]=(E19/E18)*.01[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

​thank you for your help.

 

[wideboydixon]

I added a new variable on row 16 to specify the average score for the new surveys which is then used to determine how many more you need to achieve the target:

Book1
	A	B	C	D	E
2	Total # of surveys	:	12		1200
3	Target	:	93.00%		1116
4	Variance	:			84
5	100	:	7		700
6	80	:	1		80
7	60	:	2		120
8	40	:	1		40
9	20	:	0		0
10	0	:	1		0
11	Total	:			940
12	Variance	:			176
13	Target	:			93.00%
14	Average	:			78.33%
15	Variance	:			14.67%
16	Average score for new surveys	:			98
17	Needed	:			36
18	New total # of surveys	:			48
Sheet1

Cell Formulas
Range	Formula
E17	=CEILING((C3*100*C2-E11)/(E16-C3*100),1)

All other formulas are the same as your original example.

WBD

 

[kiki burgh]

hello WBD, thank you for your edit. is this meant to replace the older formula in E17: =CEILING(C2*(C3-D14)/(1-c3),1)? when i paste it in E17, it results to circular reference. also, may i know where the value 98 came from?

if i may trouble you further, with survey results varying from 100, 80, 60, 40, 20 or 0, how i represent these as formula? samples i have seen always assume that remaining survey results will be 100s, which are not realistic.

appreciate the help.