How do I ? math/excel question

[Guest]

Hi team,

I have the below table and want to produce a figure that will show a rolling
target.
ie: the target for 12 months is 13... for one month someone gets a score of
11. what will be the target score next month to bring them back on target to
end up with 13?

Date TRUE CALLS score to get avg of 13
20/1/2006 11.91
3/2/2006 11.23
17/2/2006 11.33
3/3/2006 11.35
17/3/2006 11.41
31/3/2006 11.60
14/4/2006 11.91
28/4/2006 11.78
12/5/2006 11.68
26/5/2006 11.55
9/6/2006 11.57
23/6/2006 11.58
7/7/2006 11.55
21/7/2006
4/8/2006
18/8/2006
1/9/2006
15/9/2006
29/9/2006
13/10/2006
27/10/2006
10/11/2006
24/11/2006

Your help is much aprieciated

 

[Guest]

assuming you want to know what next score is required to bring average to
date to 13, and the data is in columns a and b starting in row 4, the
following in cell b17 results in 31.55 for the date 21/7/06, which brings
the 2006 ytd average to 13. This could be copied donw as necessary. Watch
the format on your dates.

=13*COUNTA($A$4:$A17)-SUMPRODUCT((RIGHT(A$4:$A16,2)=RIGHT(A17,2))*($B$4:$B16))

 

[Guest]

Try:

=TargetAvg*(COUNT(DataRng)+1)-SUM(DataRng)

where:
TargetAvg = the target average
DataRng = the range reference of the current data set

*I suggest you use Dynamic Named Range for DataRng.

For example, cells A2:A12 contain the following data:
17
19
14
9
11
18
22
22.5
16.5
20
21

Your target average = 19.
=TargetAvg*(COUNT(DataRng)+1)-SUM(DataRng)
=19*(11 + 1)-190 returns 38

So add 38 to the above data list into cell A13 and take the average of
A2:A13. Result = 19.

Regards,
Greg

 

[Guest]

Thank you Both,

Duane, I'm sure your answer works well. I thank you for your time.

Greg , yours is simplistic and a perfect fit for what I am doing.