Average Calculation for Sunday School Year Long Goal

[Thomas Almanza]

I am trying to maintain an average of 35 in Sunday School. Each week I input the number of attendees in my excel file. I need a formula that will tell me how many I need next Sunday, to make my year long goal of 35 as an average. For Ex:

A B C

1 Date # of Attendees #needed next week to avg 35

2 1/3/10 34 (? Formula so #36 would show)

3 1/10/10 38 (? Formula so #34 would show)

4. Etc. Etc. Etc.

Please help!


Submitted via EggHeadCafe - Software Developer Portal of Choice
Using the WebResource.axd Handler with Embedded ASP.NET Resources
http://www.eggheadcafe.com/tutorial...b6-f27c1f3beb34/using-the-webresourceaxd.aspx

 

[Dave Peterson]

With headers in row 1 and data starting in row 2, I used this in C2:

=35*(COUNT(B$1:B2)+1)-SUM(B$1:B2)
(and dragged down)

But this means you could end up with negative number of required attendees.

=max(0,35*(COUNT(B$1:B2)+1)-SUM(B$1:B2))
would show 0 if you're already assured of hitting 35 as the average.

And if you want to make sure that you don't see a number until the number of
attendees is entered:

=if(b2="","",max(0,35*(COUNT(B$1:B2)+1)-SUM(B$1:B2)))
(and drag down)