Year to Date Calculation Problem!!!

tenspeed

New Member
Joined
Jan 26, 2004
Messages
3
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
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
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

New Member
Joined
Jan 26, 2004
Messages
3
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

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
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
ABCDE
1NumberToBeTrainedinMonthsToBeTrainedPerMonth
210001283.33
3
4MonthActuallyTrainedActualStatusMoreThan5%AheadMoreThan5%Behind
5180-4%NoNo
62886%YesNo
7379-5%NoYes
8
Sheet13
 

RichardS

Well-known Member
Joined
Feb 16, 2002
Messages
761
If you're looking for YTD, a slight enhancement to Yogi's solution.
Book1
ABCD
1DoctorstobetrainedInMonthsMonthy
210001283.33333333
3
4MonthTrainedYTDVariance
5183-0.40%
6283-0.40%
73840.00%
84963.80%
95904.64%
106782.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
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,566
Messages
5,765,150
Members
425,264
Latest member
Towervibe

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top