Prev: ELSEIF Loop to End
Next: Printing an Excel Spreadsheet
From: Thomas on 13 Jan 2010 18:25
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/tutorials/aspnet/2d46e075-3cdf-44d5-b2b6-f27c1f3beb34/using-the-webresourceaxd.aspx
From: Dave Peterson on 13 Jan 2010 20:43
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)

Thomas, Almanza wrote:
>
> 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/tutorials/aspnet/2d46e075-3cdf-44d5-b2b6-f27c1f3beb34/using-the-webresourceaxd.aspx

--

Dave Peterson
 | 
Pages: 1
Prev: ELSEIF Loop to End
Next: Printing an Excel Spreadsheet