Really need some formula help please .....

[Ayerighton]

Hi folks.

I’m having a major meltdown and need to get this sorted for work. Have asked around the office and our IT dept but they say they don’t have time to work this out ( read it’s too difficult!! )

I am conducting a survey at work and the answer to the questions is either a “yes” or “no”, so that’s easy enough

So I have a simple sheet that divides the number of yes answers by the total number of survey returns to give me a % of yes responses.

What I need to know is how many additional yes surveys I need without any no surveys to get to 70%, 80% and 90%

For example :-

I have received 10 survey responses and 8 were yes, therefore 80% voted yes

How many additional yes surveys do I need to get to 90% ??

The answer is 10 additional yes surveys totalling 18 yes responses out of 20 total responses, therefore 90%

Seems easy, but I can’t for the life of me work out an excel formula to calculate this as the denominator in the calculation is always changing as more surveys come in.

Please help as I’m well and truly stumped with this one.

Many thanks

Aro

 

[Eric W]

Welcome to the Board!

It's just algebra:

	A	B	C	D
Yes	No	Percent
Number of Yeses needed to reach:

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

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

[TD="align: center"]2[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]80%[/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"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]70%[/TD]
[TD="align: right"]-3.33[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]80%[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]90%[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet3​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]C2[/TH]
[TD="align: left"]=A2/(A2+B2)[/TD]
[/TR]
[TR]
[TH]B5[/TH]
[TD]=ROUND(((A5-1)*$A$2+A5*$B$2)/(1-A5),2)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


The formula for a percentage is:

Y / (N+Y) = 80%

where Y is the number of yeses, and N is the number of nos. To get the number of yeses you need to raise the percentage to 90%, call it x, and the formula becomes:

(Y+x) / (N+Y+x) = 90%,

then just solve for x, which becomes

x = [(90%-1)Y + 90%N] / (1-90%)

 

[Eric W]

A little more algebraic manipulation results in the somewhat shorter formula for B5:

=ROUND(A5/(1-A5)*$B$2-$A$2,2)

 

[Ayerighton]

Eric

You are a superstar ??

That worked a treat

All I need now is a little help to alter the formula so it doesn’t show a number less than zero

Many many thanks

Aro.

 

[Eric W]

How about:

=MAX(0,ROUND(A5/(1-A5)*$B$2-$A$2,2))

 

[Ayerighton]

Again many thanks

Final thing is that I can’t get the equation to copy to cells below in the table to calculate for other questions, presume it won’t with the $ signs ??

 

[Eric W]

Well, yes. I didn't know your layout, so I created one. If you have a different layout, change the ranges in the formula to your ranges. And if your Y and N cells are in the same columns going down, just remove the $ from those ranges.