Help with Forecasting function?

[me]

Help with a forecast function.

I am trying to figure out how to calculate the number value to increase my
percentage from whatever it is currently to 95%. I have 3 pieces of data,
good records, nuetral records, and bad records. In cell 1a I have the % of
cells b1, c1, d1. Here is my calc:

=(b1/SUM(b1:d1)

a1 b1 c1 d1
87% 20 2 1

I am trying to figure out how many more of B1 I need to reach a goal of 95%
in cell a1. My answer in this case is 32 more, which I want to display in
cell e1.

How can I do this?

TIA,
Bob W.

 

[Dana DeLouis]

If "T" is 0.95, then

= (B1-SUM(B11)*T)/(T - 1)

returns an answer of 37.
You need to increase B1 by 37 to get a percentage of 95%.
(32 gives an "rounded" solution of 95%)

 

[me]

Dana,
You rock. I've spent about 3-4hrs looking for the correct calc and
you've helped me a great deal.

Thanks,
Bob W.

 

[Dana DeLouis]

Glad to help. :>)
As a side note, if you are increasing B1 by 'x', then the solution is to
solve for 'x' in the following equation:
0.95 = (B1+x)/((B1+x)+C1+D1)
HTH. :>)

 

[Harlan Grove]

Dana DeLouis wrote...

As a side note, if you are increasing B1 by 'x', then the solution is to
solve for 'x' in the following equation:
0.95 = (B1+x)/((B1+x)+C1+D1)

....

A little algebra makes this simpler.

0.95 = 1 / [(B1+x) / (B1+x) + (C1+D1) / (B1+x)]
1 / 0.95 = 1 + (C1+D1) / (B1 + x)
1 / (1 / 0.95 - 1) = (B1 + x) / (C1 + D1)
(C1 + D1) / (1 / 0.95 - 1) = B1 + x

x = (C1 + D1) / (1 / 0.95 - 1) - B1

 

[Dana DeLouis]

Thanks Harlan! I totally missed that one. :>(
Your equation gives the op a nice whole number to this particular problem
also if he wishes.

=19*(C1+D1)-B1
where the '19 comes from:
0.95/(1 - 0.95)
Anyway, thanks again. :>)
--
Dana DeLouis

Dana DeLouis wrote...

As a side note, if you are increasing B1 by 'x', then the solution is to
solve for 'x' in the following equation:
0.95 = (B1+x)/((B1+x)+C1+D1)

...

A little algebra makes this simpler.

0.95 = 1 / [(B1+x) / (B1+x) + (C1+D1) / (B1+x)]
1 / 0.95 = 1 + (C1+D1) / (B1 + x)
1 / (1 / 0.95 - 1) = (B1 + x) / (C1 + D1)
(C1 + D1) / (1 / 0.95 - 1) = B1 + x

x = (C1 + D1) / (1 / 0.95 - 1) - B1