Average Calculation for Sunday School Year Long Goal

T

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
 
D

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top