Year to Date Calculation Problem!!!

tenspeed

New Member
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 date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Smitty

Legend
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
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
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
5180-4%NoNo
62886%YesNo
7379-5%NoYes
8
Sheet13

RichardS

Well-known Member
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

Replies
1
Views
103
Replies
0
Views
114
Replies
6
Views
120
Replies
5
Views
78
Replies
9
Views
239

1,186,169
Messages
5,956,342
Members
438,247
Latest member
UZev

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.

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

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