Year to Date Calculation Problem!!!

[tenspeed]

Can some one help me with this Calculation problem.
the Goal is to Train 1,000 Doctors on Computers. Thats an avg.
of 83 per mth. I need to know each month after training if i'm 5% on
track of the goal,5% off track of the goal. Any one have any ideas??

thanks
tenspeed

 

[Smitty]

Welcome to the Board!

=(B2/A2)-1

Where B2 = Actual # trained & A2 = Goal.

You can Conditional Format two conditions, the first for Greater than or Equal to 0.05, the second Less than or Equal to -0.05.

Hope that helps,

Smitty

 

[tenspeed]

e.g - you are saying that b2=88 and a2=83 or is that a2=1,000?
also what is the -1 on the end of the equation? Also what if i am
within 5% of the goal?

thanks

 

[Yogi Anand]

Hi tenspeed:

If I have understood correctly about your 5% or -5% offtrack, you may find the following illustration to be of some interest ...

Book1
	A	B	C	D	E
1	NumberToBeTrained	inMonths	ToBeTrainedPerMonth
2	1000	12	83.33
3
4	Month	ActuallyTrained	ActualStatus	MoreThan5%Ahead	MoreThan5%Behind
5	1	80	-4%	No	No
6	2	88	6%	Yes	No
7	3	79	-5%	No	Yes
8
Sheet13

 

[RichardS]

If you're looking for YTD, a slight enhancement to Yogi's solution.

Book1
	A	B	C	D
1	Doctorstobetrained	InMonths	Monthy
2	1000	12	83.33333333
3
4	Month	Trained	YTDVariance
5	1	83	-0.40%
6	2	83	-0.40%
7	3	84	0.00%
8	4	96	3.80%
9	5	90	4.64%
10	6	78	2.80%
Sheet1

Formula in C5 is

=IF(B5>0,(SUM(B$5:B5)/(A5*$C$2))-1,0)

Copy down for your twelve months. When you enter the latest months results, it will tell you whether you're under or over YTD target, and by what percentage.

My two bob worth

Richard