Help with Forecasting function?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

me - 23 May 2006 13:05 GMT

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.

Reply to this Message

Dana DeLouis - 23 May 2006 15:41 GMT

If "T" is 0.95, then

= (B1-SUM(B1:D1)*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%)

Signature

HTH. :>)
Dana DeLouis
Windows XP, Office 2003

> Help with a forecast function.
>
[quoted text clipped - 16 lines]
> TIA,
> Bob W.

Reply to this Message

me - 24 May 2006 00:47 GMT

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.

> If "T" is 0.95, then
>
[quoted text clipped - 23 lines]
>> TIA,
>> Bob W.

Reply to this Message

Dana DeLouis - 24 May 2006 04:31 GMT

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

Signature

Dana DeLouis

> Dana,
> You rock. I've spent about 3-4hrs looking for the correct calc and
[quoted text clipped - 30 lines]
>>> TIA,
>>> Bob W.

Reply to this Message

Harlan Grove - 24 May 2006 05:27 GMT

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

Reply to this Message

Dana DeLouis - 25 May 2006 05:27 GMT

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

Signature

Dana DeLouis

> Dana DeLouis wrote...
>>As a side note, if you are increasing B1 by 'x', then the solution is to
[quoted text clipped - 10 lines]
>
> x = (C1 + D1) / (1 / 0.95 - 1) - B1

Reply to this Message