Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I ? math/excel question
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I ? math/excel question
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)) "Madduck" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I ? math/excel question
Try:
=TargetAvg*(COUNT(DataRng)+1)-SUM(DataRng) whe 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 "Madduck" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I ? math/excel question
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Possibly a loaded question, but I think | Excel Discussion (Misc queries) | |||
How do I find and replace a question mark in Excel? | Excel Discussion (Misc queries) | |||
Newbie With A Question | Excel Worksheet Functions | |||
Anybody Help with previous question | Excel Discussion (Misc queries) | |||
Hints And Tips For New Posters In The Excel Newsgroups | Excel Worksheet Functions |