Need a formula to calculate target

[enterdelete123]

Hi,

Need a formula to calculate target without entering values manually.

Right now, we calculate manually.

Example : If we get 5 surveys, out of which 4 are positive surveys and one is negative survey, the score will be 80%(no. of positive surveys/total number of surveys). The target is 90%.

If we calculate manually, I need more 5 positive surveys to achieve target i.e., 9/10= 90%

I need a formula to calculate number of surveys require to achieve the target.

Please help!!!

[MrShorty]

I would suggest you make it an algebra problem before you make it a programming problem. Solve the algebra problem, and the programming problem will be easy.

current score=number of positives/total (s=p/t)
target score=(number of positives+extra surveys)/(current total+extra surveys) [s2=(p+x)/(t+x)] solve for x.

At this point, can I assume that your algebra skills are good enough to solve that algebra problem? (If you need a refresher, something like this should help: http://www.purplemath.com/modules/solvelin3.htm )

[enterdelete123]

If my target is 92 then X=p-92t/91

Is that correct?

[MrShorty]

That doesn't look right to me. I would look at your algebra again. When I do it, the target fraction shows up twice in the resulting expression.

[enterdelete123]

Thank you so much for helping me, MrShorty!!

If we have large number surveys, we cannot count the positive surveys.

Let's say: current score is 87.41% on 429 surveys.

How can we calculate the required number of surveys to achieve 90% with formula?

Can you just give me a formula?

[MrShorty]

current score is 87.41% on 429 surveys.

Doesn't that mean, without counting, that you currently have 0.8741*429=375 positive surveys?

I could just give you a formula. It concerns me that we do not understand the simple algebra behind this kind of problem. Follow along:

target score = (current positive + new surveys)/(current total + new surveys)
target score * (current total + new surveys) = current positive + new surveys
target score * current total + target score * new surveys = current positive + new surveys
target score *new surveys - new surveys = current positive - target score * current total
new surveys * (target score -1) = current positive - target score * current total
new surveys = (current positive - target score * current total)/(target score - 1)
new surveys = (current total * current score - target score * current total)/(target score -1)

Does that help with the algebra?

[enterdelete123]

Let's go with your formula :

current total = 429
current score = 87.41
target score = 90

=(429*87.41-90*429)/(90-1)

It is giving : -12.484

If we calculate manually, we should get 111 surveys to get 90.

Please check.

[MrShorty]

You are not converting your percentages to fractions. Recall that 90% is the same as 9/10 is the same as 0.9 (not the integer 90), so that should be (429*0.8741-429*0.9)/(0.9-1)=111.111 or 111 rounded to the nearest integer. Or you can do as many do/teach and make sure to write all your percentages as number/100 (429*87.41/100-429*90/100)/(90/100-1)

Refresher on fractions, decimals, and percentages -- http://www.purplemath.com/modules/percents.htm

[enterdelete123]

As requested..

[enterdelete123]

MrShorty, I've used the same formula in the attached file and it is giving me 45 surveys where it should get 5 surveys.

Can you correct the formula for me?

Thanks..

[MrShorty]

Note that, when numbers are formatted as percent, Excel already sees the underlying number as a decimal fraction (this is a time where it might be useful to format all cells as General to see what is actually stored in those cells). You don't need to divide H4 by 100.

=(I4*H4-I4*90/100)/(90/100-1)

Is there a reason you prefer to hard code the 90/100 in the formula rather than use a reference to H6

=(I4*H4-I4*H6)/(H6-1)

[enterdelete123]

Awesome MrShorty......

Finally got it!!!