Average Goal Calculation

[Thomas Almanza]

I am trying to average 35 every week in my Sunday school.
Last week, I had 32, the week before that 36. I would like a formula to tell me how many I need on the next Sunday, to average 35 for the year. All of the weeks are in column c (C2:C53) I would like 1 cell at the bottom of column c to have the answer continually update itself when I insert Sunday's attendance. What do I do?


Submitted via EggHeadCafe - Software Developer Portal of Choice
Crystal Report And Parameter Passing Using Stored Procedure
http://www.eggheadcafe.com/tutorial...c-569d6ea46488/crystal-report-and-parame.aspx

 

[walrus]

Heya Thomas

what you need is Goal Seek but you will have to do it weekly. in the table
below go to cell F2 (average formula) and in the DATA Menu > What-if analysis

click Goal Seek. in the box that open - Set cell is you selected cell (F2)
To value put 35 > By changing cell C4 > Enter when calculation finished

cick OK. you will see in cell C4 that you need 37 more to make your average
35. just paste this value in cell D4 and when you know the actual attendence
just overwrite the 37 in cell C4 this way you also check your progress viz
required and actual.

A B C D E F
1 Attnd Attnd Req Avg
Avg Req
2 Week1 36 35 =AVERAGE($C$2:$C$53) =AVERAGE($C$2:$C$53)
3 Week2 32 34 =AVERAGE($C$2:$C$53)
4 Week3 37 =AVERAGE($C$2:$C$53)

 

[Jarek Kujawa]

another way (without going to GoalSeek):

in C1:

=(1+COUNTA(C2:C53))*(35-SUM(C2:C53)/COUNTA(C2:C53))+SUM(C2:C53)/COUNTA
(C2:C53)

HIH

 

[Thomas Almanza]

I have been trying to understand this principle but need you to clarify your instructions.

Example:

C1 2010 (Year) To Avg. 35 (Title)

C2 36 Formula (Need 34)
C3 32 Formula (Need 37)
C4 34 Formula (Need 38)


The formula needs to adjust calculating all the weeks prior to it to be able to avg. 35 for the year

Thank-you










walrus wrote:

Heya Thomaswhat you need is Goal Seek but you will have to do it weekly.
13-Jan-10

Heya Thoma

what you need is Goal Seek but you will have to do it weekly. in the tabl
below go to cell F2 (average formula) and in the DATA Menu > What-if analysi
cick OK. you will see in cell C4 that you need 37 more to make your averag
35. just paste this value in cell D4 and when you know the actual attendenc
just overwrite the 37 in cell C4 this way you also check your progress vi
required and actual

A B C D E
1 Attnd Attnd Req Av
Avg Re
2 Week1 36 35 =AVERAGE($C$2:$C$53) =AVERAGE($C$2:$C$53
3 Week2 32 34 =AVERAGE($C$2:$C$53
4 Week3 37 =AVERAGE($C$2:$C$53


:

Previous Posts In This Thread:


Submitted via EggHeadCafe - Software Developer Portal of Choice
ASP.NET Session Variables - Hijack
http://www.eggheadcafe.com/tutorial...b1-c5283d523c8c/aspnet-session-variables.aspx

 

[Thomas Almanza]

Please resend your formula because for some reaon excel is saying there is an error in it.

Thank-you



Jarek Kujawa wrote:

another way (without going to GoalSeek):in
13-Jan-10

another way (without going to GoalSeek)

in C1

=3D(1+COUNTA(C2:C53))*(35-SUM(C2:C53)/COUNTA(C2:C53))+SUM(C2:C53)/COUNT
(C2:C53

HI

On 13 Sty, 06:53, Thomas Almanza wrote
ell me how many I need on the next Sunday, to average 35 for the year. All
of the weeks are in column c =A0(C2:C53) I would like 1 cell at the bottom
of column c to have the answer continually update itself when I insert Sund
ay's attendance. What do I do
headcafe.com/tutorials/aspnet/4520dcca-f4bb-4cd3-a80c-5...

Previous Posts In This Thread:


Submitted via EggHeadCafe - Software Developer Portal of Choice
ASP.NET DropDown List - XML
http://www.eggheadcafe.com/tutorial...923-2457fe12d1cb/aspnet-dropdown-list--x.aspx

 

[David Biddulph]

Jarek didn't say
=3D(1+COUNTA(C2:C53))*(35-SUM(C2:C53)/COUNTA(C2:C53))+SUM(C2:C53)/COUNTA
(C2:C53)

He said
=(1+COUNTA(C2:C53))*(35-SUM(C2:C53)/COUNTA(C2:C53))+SUM(C2:C53)/COUNTA
(C2:C53)

I don't know where you got the 3D from. Perhaps it was introduced by
EggHeadCafe? You'd be better off accessing the newsgroup directly using a
news server and a newsreader.