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/tutorials...nd-parame.aspx

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)



"Thomas Almanza" wrote:

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/tutorials...nd-parame.aspx
.

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


On 13 Sty, 06:53, Thomas Almanza wrote:
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 Procedurehttp://www.eggheadcafe.com/tutorials/aspnet/4520dcca-f4bb-4cd3-a80c-5...

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 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
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)



"Thomas Almanza" wrote:

Previous Posts In This Thread:


Submitted via EggHeadCafe - Software Developer Portal of Choice
ASP.NET Session Variables - Hijack
http://www.eggheadcafe.com/tutorials...variables.aspx

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)/COUNTA
(C2:C53)

HIH


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/tutorials...n-list--x.aspx

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.
--
David Biddulph


Thomas Almanza wrote:
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)/COUNTA
(C2:C53)

HIH


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/tutorials...n-list--x.aspx