Formula to project target % needed by month to achieve goal based on current % achievement

[dana072002]

Hi there,
I am new here an hoping someone can help me!
I originally posted in another forum but no one answered?
Maybe I am ding something wrong?
I need this formula for a presentation I have to do next week, please help!!!!

I am in need f a formula to help me project the target % I would need to hit for each remaining month of the year in order to reach my goal of 100%ACH/92%ADH.

[TABLE="width: 790"]
<tbody>[TR]
[TD="width: 145, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]Category
[/TD]
[TD="width: 64, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]Jan
[/TD]
[TD="width: 64, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]Feb
[/TD]
[TD="width: 64, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]Mar
[/TD]
[TD="width: 64, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]Apr
[/TD]
[TD="width: 64, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]May
[/TD]
[TD="width: 64, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]Jun
[/TD]
[TD="width: 64, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]Jul
[/TD]
[TD="width: 64, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]Aug
[/TD]
[TD="width: 64, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]Sep
[/TD]
[TD="width: 64, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]Oct
[/TD]
[TD="width: 64, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]Nov
[/TD]
[TD="width: 64, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]Dec
[/TD]
[TD="width: 64, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]'18 YE
[/TD]
[TD="width: 76, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]'19 YTD
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]YTD ACH %
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] , align: right"]102.7%
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] , align: right"]102.1%
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]YTD ADH %
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] , align: right"]83.7%
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] , align: right"]85.8%
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]ACH %
[/TD]
[TD="bgcolor: transparent, align: right"]102.3%
[/TD]
[TD="bgcolor: transparent, align: right"]87.5%
[/TD]
[TD="bgcolor: transparent, align: right"]90.0%
[/TD]
[TD="bgcolor: transparent, align: right"]107.0%
[/TD]
[TD="bgcolor: transparent, align: right"]110.3%
[/TD]
[TD="bgcolor: transparent, align: right"]101.1%
[/TD]
[TD="bgcolor: transparent, align: right"]94.7%
[/TD]
[TD="bgcolor: transparent, align: right"]117.0%
[/TD]
[TD="bgcolor: transparent"] ?
[/TD]
[TD="bgcolor: transparent"] ?
[/TD]
[TD="bgcolor: transparent"]?
[/TD]
[TD="bgcolor: transparent"] ?
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]ADH %
[/TD]
[TD="bgcolor: transparent, align: right"]87.3%
[/TD]
[TD="bgcolor: transparent, align: right"]78.3%
[/TD]
[TD="bgcolor: transparent, align: right"]81.3%
[/TD]
[TD="bgcolor: transparent, align: right"]83.7%
[/TD]
[TD="bgcolor: transparent, align: right"]92.5%
[/TD]
[TD="bgcolor: transparent, align: right"]86.0%
[/TD]
[TD="bgcolor: transparent, align: right"]86.7%
[/TD]
[TD="bgcolor: transparent, align: right"]85.7%
[/TD]
[TD="bgcolor: transparent"] ?
[/TD]
[TD="bgcolor: transparent"] ?
[/TD]
[TD="bgcolor: transparent"] ?
[/TD]
[TD="bgcolor: transparent"] ?
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]


Thanks!

 

[Toadstool]

Hi dana072002,

Can you give the formula for your '19 YTD values as I don't get the same 102.1% and 85.8% as you do?

 

[Eric W]

Welcome to the Board!

How are your percentages calculated? Generally speaking, percentages are based on some underlying data, like this year's sales divided by last year's sales. So in order to calculate a percentage, you need the sales values. As a rule, you can't just average percentages together and get a meaningful value, you need the underlying data. How are your YTD percentages calculated in column O? Also, if we calculate a percentage for Sep-Dec, they will all be the same, unless you have some reason to think that each of those months might have some quantifiable difference.

 

[dana072002]

Hi Toadstool,
it was calculated based on the pivot table results below. Is this what you need?
Thanks!

[TABLE="width: 591"]
<colgroup><col width="112" style="width: 84pt; mso-width-source: userset; mso-width-alt: 3982;"> <col width="184" style="width: 138pt; mso-width-source: userset; mso-width-alt: 6542;"> <col width="153" style="width: 115pt; mso-width-source: userset; mso-width-alt: 5432;"> <col width="161" style="width: 121pt; mso-width-source: userset; mso-width-alt: 5717;"> <col width="88" style="width: 66pt; mso-width-source: userset; mso-width-alt: 3128;"> <col width="90" style="width: 67pt; mso-width-source: userset; mso-width-alt: 3185;"> <tbody>[TR]
[TD="width: 112"]Row Labels[/TD]
[TD="width: 184"]Sum of Planned Units (MPS)[/TD]
[TD="width: 153"]Sum of Delivered Units[/TD]
[TD="width: 161"]Sum of Adherence Units[/TD]
[TD="width: 88"]Sum of Ach%[/TD]
[TD="width: 90"]Sum of Adh%[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Jan[/TD]
[TD="bgcolor: transparent"] 3,441,930 [/TD]
[TD="bgcolor: transparent"] 3,519,511 [/TD]
[TD="bgcolor: transparent"] 3,006,368 [/TD]
[TD="bgcolor: transparent, align: right"]102.3%[/TD]
[TD="bgcolor: transparent, align: right"]87.3%[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Feb[/TD]
[TD="bgcolor: transparent"] 3,053,006 [/TD]
[TD="bgcolor: transparent"] 2,672,664 [/TD]
[TD="bgcolor: transparent"] 2,390,647 [/TD]
[TD="bgcolor: transparent, align: right"]87.5%[/TD]
[TD="bgcolor: transparent, align: right"]78.3%[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Mar[/TD]
[TD="bgcolor: transparent"] 2,421,805 [/TD]
[TD="bgcolor: transparent"] 2,178,589 [/TD]
[TD="bgcolor: transparent"] 1,970,033 [/TD]
[TD="bgcolor: transparent, align: right"]90.0%[/TD]
[TD="bgcolor: transparent, align: right"]81.3%[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Apr[/TD]
[TD="bgcolor: transparent"] 3,413,658 [/TD]
[TD="bgcolor: transparent"] 3,650,964 [/TD]
[TD="bgcolor: transparent"] 2,856,131 [/TD]
[TD="bgcolor: transparent, align: right"]107.0%[/TD]
[TD="bgcolor: transparent, align: right"]83.7%[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]May[/TD]
[TD="bgcolor: transparent"] 4,888,165 [/TD]
[TD="bgcolor: transparent"] 5,392,865 [/TD]
[TD="bgcolor: transparent"] 4,520,021 [/TD]
[TD="bgcolor: transparent, align: right"]110.3%[/TD]
[TD="bgcolor: transparent, align: right"]92.5%[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Jun[/TD]
[TD="bgcolor: transparent"] 3,471,940 [/TD]
[TD="bgcolor: transparent"] 3,510,888 [/TD]
[TD="bgcolor: transparent"] 2,984,728 [/TD]
[TD="bgcolor: transparent, align: right"]101.1%[/TD]
[TD="bgcolor: transparent, align: right"]86.0%[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Jul[/TD]
[TD="bgcolor: transparent"] 3,607,004 [/TD]
[TD="bgcolor: transparent"] 3,414,082 [/TD]
[TD="bgcolor: transparent"] 3,129,008 [/TD]
[TD="bgcolor: transparent, align: right"]94.7%[/TD]
[TD="bgcolor: transparent, align: right"]86.7%[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Aug[/TD]
[TD="bgcolor: transparent"] 3,133,893 [/TD]
[TD="bgcolor: transparent"] 3,665,720 [/TD]
[TD="bgcolor: transparent"] 2,685,498 [/TD]
[TD="bgcolor: transparent, align: right"]117.0%[/TD]
[TD="bgcolor: transparent, align: right"]85.7%[/TD]
[/TR]
[TR]
[TD]Grand Total[/TD]
[TD] 27,431,401 [/TD]
[TD] 28,005,283 [/TD]
[TD] 23,542,434 [/TD]
[TD="align: right"]102.1%[/TD]
[TD="align: right"]85.8%[/TD]
[/TR]
</tbody>[/TABLE]

 

[dana072002]

Hi Eric,
the % are calculated based on units planned vs units atoll received, which is the ACH%, then we have how many of the received units are received in accordance to the plan.
Does this make sense?
Thanks1!


[TABLE="width: 591"]
<colgroup><col width="112" style="width: 84pt; mso-width-source: userset; mso-width-alt: 3982;"> <col width="184" style="width: 138pt; mso-width-source: userset; mso-width-alt: 6542;"> <col width="153" style="width: 115pt; mso-width-source: userset; mso-width-alt: 5432;"> <col width="161" style="width: 121pt; mso-width-source: userset; mso-width-alt: 5717;"> <col width="88" style="width: 66pt; mso-width-source: userset; mso-width-alt: 3128;"> <col width="90" style="width: 67pt; mso-width-source: userset; mso-width-alt: 3185;"> <tbody>[TR]
[TD="width: 112"]Row Labels[/TD]
[TD="width: 184"]Sum of Planned Units (MPS)[/TD]
[TD="width: 153"]Sum of Delivered Units[/TD]
[TD="width: 161"]Sum of Adherence Units[/TD]
[TD="width: 88"]Sum of Ach%[/TD]
[TD="width: 90"]Sum of Adh%[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Jan[/TD]
[TD="bgcolor: transparent"] 3,441,930 [/TD]
[TD="bgcolor: transparent"] 3,519,511 [/TD]
[TD="bgcolor: transparent"] 3,006,368 [/TD]
[TD="bgcolor: transparent, align: right"]102.3%[/TD]
[TD="bgcolor: transparent, align: right"]87.3%[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Feb[/TD]
[TD="bgcolor: transparent"] 3,053,006 [/TD]
[TD="bgcolor: transparent"] 2,672,664 [/TD]
[TD="bgcolor: transparent"] 2,390,647 [/TD]
[TD="bgcolor: transparent, align: right"]87.5%[/TD]
[TD="bgcolor: transparent, align: right"]78.3%[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Mar[/TD]
[TD="bgcolor: transparent"] 2,421,805 [/TD]
[TD="bgcolor: transparent"] 2,178,589 [/TD]
[TD="bgcolor: transparent"] 1,970,033 [/TD]
[TD="bgcolor: transparent, align: right"]90.0%[/TD]
[TD="bgcolor: transparent, align: right"]81.3%[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Apr[/TD]
[TD="bgcolor: transparent"] 3,413,658 [/TD]
[TD="bgcolor: transparent"] 3,650,964 [/TD]
[TD="bgcolor: transparent"] 2,856,131 [/TD]
[TD="bgcolor: transparent, align: right"]107.0%[/TD]
[TD="bgcolor: transparent, align: right"]83.7%[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]May[/TD]
[TD="bgcolor: transparent"] 4,888,165 [/TD]
[TD="bgcolor: transparent"] 5,392,865 [/TD]
[TD="bgcolor: transparent"] 4,520,021 [/TD]
[TD="bgcolor: transparent, align: right"]110.3%[/TD]
[TD="bgcolor: transparent, align: right"]92.5%[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Jun[/TD]
[TD="bgcolor: transparent"] 3,471,940 [/TD]
[TD="bgcolor: transparent"] 3,510,888 [/TD]
[TD="bgcolor: transparent"] 2,984,728 [/TD]
[TD="bgcolor: transparent, align: right"]101.1%[/TD]
[TD="bgcolor: transparent, align: right"]86.0%[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Jul[/TD]
[TD="bgcolor: transparent"] 3,607,004 [/TD]
[TD="bgcolor: transparent"] 3,414,082 [/TD]
[TD="bgcolor: transparent"] 3,129,008 [/TD]
[TD="bgcolor: transparent, align: right"]94.7%[/TD]
[TD="bgcolor: transparent, align: right"]86.7%[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Aug[/TD]
[TD="bgcolor: transparent"] 3,133,893 [/TD]
[TD="bgcolor: transparent"] 3,665,720 [/TD]
[TD="bgcolor: transparent"] 2,685,498 [/TD]
[TD="bgcolor: transparent, align: right"]117.0%[/TD]
[TD="bgcolor: transparent, align: right"]85.7%[/TD]
[/TR]
[TR]
[TD]Grand Total[/TD]
[TD] 27,431,401 [/TD]
[TD] 28,005,283 [/TD]
[TD] 23,542,434 [/TD]
[TD="align: right"]102.1%[/TD]
[TD="align: right"]85.8%[/TD]
[/TR]
</tbody>[/TABLE]

 

[Eric W]

Do you have the values for Sum of Planned Units for Sep-Dec?

 

[dana072002]

No, they won't be done until the start of each new month. we are getting ready to do sept. now. I am trying to do a calculation to estimate what % I would need to achieve each of the remaining months based on the previous months in order to end with a yearly average of 100% ACH and 92% ADH. So, what % would I need to hit Sep-Dec in order to end the year with 100% ACH/92% ADH. I hope I am this properly.

 

[Eric W]

You can't look for a target percentage without knowing (or estimating) the Sum of Planned Units. For example, let's assume that your August Sum of Delivered Units is 3,000,000 instead of 3,665,720. Then your Grand Total (YTD) Sum of ACH% is 99.67%. So you wouldn't expect the target percentage you're looking for would be very much higher, right? Maybe 102%? Now let's say that your Sum of Planned Units for Sep-Dec is all 1 and the Sum of Delivered Units for Sep-Dec is all 2. 200% for all 4 months! But the YTD percentage total? Exactly the same. There will be some difference in decimal places far down the line, but nothing significant. This is an extreme example, but it shows the issue.

This shows you need to estimate the Sum of Planned Units before you can estimate an ACH% needed to hit 100% for the year, which will incidentally give you an estimate for the Sum of Delivered Units. (And the same for Sum of Adherence Units.) I suppose you could estimate the Sum of Planned Units for Sep-Dec by averaging the Jan-Aug values, but there's such a high variability in these numbers (May is twice as high as March), I wouldn't have much confidence in the results.

 

[dana072002]

I understand what you are saying about needing planned, delivered units, etc, but I am only trying to create a formula for projecting what % I would need to achieve for the remaining months in the year to achieve an average of my targets, regardless of my what the planned units are.

For example, I plugged in random % for Sept-Dec. and even if I hit 100% every month Sept-Dec I can only end up with 90.1% ADH, but for my ACH%, I plugged in 99% every month and I am getting 100.5% ACH. I am just using he basic AVG formula for that, but I am trying get a formula to exactly project what % I would need Sep-Dec for ACH order to end the year with 100%. I don't know if it would be an IF statement or what other formula I could use to do this?

 

[Eric W]

I understand what you are saying about needing planned, delivered units, etc, but I am only trying to create a formula for projecting what % I would need to achieve for the remaining months in the year to achieve an average of my targets, regardless of my what the planned units are.

Somehow I'm not getting through. Let's try this.

IT. CAN'T. BE. DONE.

I showed you an example of using 200% as a projection, and it didn't work. You yourself tried 100% every month and still failed to meet your 92% goal. Let me show you an example of how to project a percentage when you start with an estimate of the MPS. I just used the average as I proposed earlier.

Excel 2012

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
Category	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	'18 YE	'19 YTD
YTD ACH %
YTD ADH %
ACH %	?	?	?	?
ADH %	?	?	?	?
Row Labels	Sum of Planned Units (MPS)	Sum of Delivered Units	Sum of Adherence Units	Sum of Ach%	Sum of Adh%
Jan	# to achieve 100% ACH	# to achieve 92% ADH
Feb
Mar
Apr	amount per month	amount per month
May
Jun
Jul	required % per month	required % per month
Aug
Sep
Oct
Nov
Dec
Grand Total

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]102.70%[/TD]
[TD="align: right"]102.10%[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]83.70%[/TD]
[TD="align: right"]85.80%[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]102.30%[/TD]
[TD="align: right"]87.50%[/TD]
[TD="align: right"]90.00%[/TD]
[TD="align: right"]107.00%[/TD]
[TD="align: right"]110.30%[/TD]
[TD="align: right"]101.10%[/TD]
[TD="align: right"]94.70%[/TD]
[TD="align: right"]117.00%[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]87.30%[/TD]
[TD="align: right"]78.30%[/TD]
[TD="align: right"]81.30%[/TD]
[TD="align: right"]83.70%[/TD]
[TD="align: right"]92.50%[/TD]
[TD="align: right"]86.00%[/TD]
[TD="align: right"]86.70%[/TD]
[TD="align: right"]85.70%[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]3,441,930[/TD]
[TD="align: right"]3,519,511[/TD]
[TD="align: right"]3,006,368[/TD]
[TD="align: right"]102.25%[/TD]
[TD="align: right"]87.35%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]3,053,006[/TD]
[TD="align: right"]2,672,664[/TD]
[TD="align: right"]2,390,647[/TD]
[TD="align: right"]87.54%[/TD]
[TD="align: right"]78.30%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]13,141,819[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]12090473[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]2,421,805[/TD]
[TD="align: right"]2,178,589[/TD]
[TD="align: right"]1,970,033[/TD]
[TD="align: right"]89.96%[/TD]
[TD="align: right"]81.35%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]

[TD="align: right"]3,413,658[/TD]
[TD="align: right"]3,650,964[/TD]
[TD="align: right"]2,856,131[/TD]
[TD="align: right"]106.95%[/TD]
[TD="align: right"]83.67%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]

[TD="align: right"]4,888,165[/TD]
[TD="align: right"]5,392,865[/TD]
[TD="align: right"]4,520,021[/TD]
[TD="align: right"]110.32%[/TD]
[TD="align: right"]92.47%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]3285454.6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]3022618[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]

[TD="align: right"]3,471,940[/TD]
[TD="align: right"]3,510,888[/TD]
[TD="align: right"]2,984,728[/TD]
[TD="align: right"]101.12%[/TD]
[TD="align: right"]85.97%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]

[TD="align: right"]3,607,004[/TD]
[TD="align: right"]3,414,082[/TD]
[TD="align: right"]3,129,008[/TD]
[TD="align: right"]94.65%[/TD]
[TD="align: right"]86.75%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]

[TD="align: right"]3,133,893[/TD]
[TD="align: right"]3,665,720[/TD]
[TD="align: right"]2,685,498[/TD]
[TD="align: right"]116.97%[/TD]
[TD="align: right"]85.69%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]95.82%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]88.15%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]

[TD="align: right"]3,428,925[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.00%[/TD]
[TD="align: right"]0.00%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]

[TD="align: right"]3,428,925[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.00%[/TD]
[TD="align: right"]0.00%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]

[TD="align: right"]3,428,925[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.00%[/TD]
[TD="align: right"]0.00%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]22[/TD]

[TD="align: right"]3,428,925[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.00%[/TD]
[TD="align: right"]0.00%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]23[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]24[/TD]

[TD="align: right"]41,147,102[/TD]
[TD="align: right"]28,005,283[/TD]
[TD="align: right"]23,542,434[/TD]
[TD="align: right"]68.06%[/TD]
[TD="align: right"]57.22%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet4​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]E11[/TH]
[TD="align: left"]=C11/B11[/TD]
[/TR]
[TR]
[TH]F11[/TH]
[TD="align: left"]=D11/B11[/TD]
[/TR]
[TR]
[TH]B19[/TH]
[TD="align: left"]=AVERAGE(B$11:B18)[/TD]
[/TR]
[TR]
[TH]B24[/TH]
[TD="align: left"]=SUM(B11:B22)[/TD]
[/TR]
[TR]
[TH]C24[/TH]
[TD="align: left"]=SUM(C11:C22)[/TD]
[/TR]
[TR]
[TH]D24[/TH]
[TD="align: left"]=SUM(D11:D22)[/TD]
[/TR]
[TR]
[TH]E24[/TH]
[TD="align: left"]=C24/B24[/TD]
[/TR]
[TR]
[TH]F24[/TH]
[TD="align: left"]=D24/B24[/TD]
[/TR]
[TR]
[TH]I12[/TH]
[TD="align: left"]=B24-C24[/TD]
[/TR]
[TR]
[TH]I15[/TH]
[TD="align: left"]=I12/4[/TD]
[/TR]
[TR]
[TH]I18[/TH]
[TD="align: left"]=I15/B19[/TD]
[/TR]
[TR]
[TH]L12[/TH]
[TD="align: left"]=(SUM(B11:B22)-SUM(C11:C18))*0.92[/TD]
[/TR]
[TR]
[TH]L15[/TH]
[TD="align: left"]=L12/4[/TD]
[/TR]
[TR]
[TH]L18[/TH]
[TD="align: left"]=L15/B19[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Given the estimates in B19:B22, you can calculate the number you need to hit 100% for the year (I12). Divide that by 4 to get the number you need each month (I15). Then divide that by the monthly estimate to get the percentage estimate (I18). If you plug in the I12 amount into C19:C22, you'll get the percentage you expect. If you change the estimates in B19:B22, you'll get completely different results in the I and L columns.


I suspect you're thinking of a problem like: if I score 90, 92, 75, 88 on 4 tests, what do I need on the last 2 to average 95% overall? But the difference is that in this problem, each test has a maximum score of 100. But in your problem, there is no defined upper limit.