Formula help needed for calculating number to reach a specific goal

[JPG963]

Hello All,

I am hoping you can assist me in finding a formula to automatically calculate how many additional positive surveys are needed to achieve a pre determined goal located in another cell, let's say 90%. Currently I have the following formula to calculate the current feedback score (61.54%). Thanks in advance for your assistance

=IFERROR(B9/B12-B10/B12,)

 

[StephenCrump]

Assuming you mean that extra surveys will increase the total beyond 13 (i.e. rather than what if some of the existing Passive/Negative had been Positive instead?) try:

C5: =ROUNDUP((Target*Total+Passive-Positive)/(1-Target),0)

Excel 2010

	A	B	C
Positive
Passive
Negative

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"]Target[/TD]
[TD="align: right"]x[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"]90%[/TD]
[TD="align: right"]37[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"]61.54%[/TD]
[TD="align: right"]90.00%[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]47[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]50[/TD]

</tbody>

1
​

Incidentally, wouldn't your rating formula make more sense if it measured (Positive-Negative)/Total rather than (Positive-Passive)/Total?

 

[JPG963]

Thank you Stephan...
That formula worked perfectly.. Now I have another question..
I would like to know if it is possible to calculate if the only info known was the total survey count and the current result/score. I am not very good in thinking through the logic behind these formulas so any assistance is greatly appreciated..

Example:
I have a report that shows we have a total of 100 surveys for a score of 70%. I am looking for a formula to calculate how many more Positive surveys would be needed to reach the goal located in another cell (let's say 90% in A1)

The reason the rating formula is calculated this way is due to the how we weight each. A passive survey = -100 and a detractor is -200.

 

[StephenCrump]

I would like to know if it is possible to calculate if the only info known was the total survey count and the current result/score.

Yes, just change the formula to:

=ROUNDUP(Total*(Target-CurrentScore)/(1-Target),0)

But I'm still not clear on what your rating formula should be:

The reason the rating formula is calculated this way is due to the how we weight each. A passive survey = -100 and a detractor is -200.

At the moment, your formula is ignoring the number of negative surveys. If you wanted to weight, say, +100 for positive, -100 for passive and -200 for negative, your original formula should be:

=IFERROR((B9-B10-2*B11)/B12,)
= 46.15%?

 

[Juneau07]

How about if I need to get the minimum target for a running data. I'm at wits end in figuring this out:

[TABLE="width: 652"]
<tbody>[TR]
[TD]Manager[/TD]
[TD]Count Surveys[/TD]
[TD]% Score[/TD]
[TD]Target Survey Count[/TD]
[TD]Target[/TD]
[TD]Surveys Needed[/TD]
[TD]Min % Needed[/TD]
[/TR]
[TR]
[TD]Manager A[/TD]
[TD]12[/TD]
[TD]95.00%[/TD]
[TD]108[/TD]
[TD]93.50%[/TD]
[TD]96[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Manager B[/TD]
[TD]10[/TD]
[TD]96.80%[/TD]
[TD]110[/TD]
[TD]93.50%[/TD]
[TD]100[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Manager C[/TD]
[TD]12[/TD]
[TD]89.50%[/TD]
[TD]154[/TD]
[TD]93.50%[/TD]
[TD]142[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Manager D[/TD]
[TD]18[/TD]
[TD]91.00%[/TD]
[TD]212[/TD]
[TD]93.50%[/TD]
[TD]194[/TD]
[TD] [/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col><col><col></colgroup>[/TABLE]

 

[StephenCrump]

Juneau07, is this what you're looking for?

G2: =(D2*E2-B2*C2)/F2

Excel 2010
	A	B	C	D	E	F	G
1	Manager	Count Surveys	% Score	Target Survey Count	Target	Surveys Needed	Min % Needed
2	Manager A	12	95.00%	108	93.50%	96	93.31%
3	Manager B	10	96.80%	110	93.50%	100	93.17%
4	Manager C	12	89.50%	154	93.50%	142	93.84%
5	Manager D	18	91.00%	212	93.50%	194	93.73%
Sheet1