Calculate number needed to maintain an average

[Eric]

C2 through C22 represent the work days in August. In each cell total "Talk
Time" minutes are entered manually. C23 adds these up and returns them as
hours and minutes. C25 adds all the days worked (by counting the cells in C2
through C22 that have data in them). C24 is the "Average Talk Time"
(calculated by dividing C23 by C25).

What I need is a cell that will tell me the next day's "Talk Time" would
have to be to make sure that the "Average Talk Time" in C24 maintains 3 or
more hours.

Thank you!

 

[Luke M]

Next Value must be greater than or equal to:
=0.125*(C25+1)-C23

Note that 0.125 = 3 hrs.

Derivation:
CurrentSum/CurrentCount >=3
(CurrentSum+x)/(CurrentCount +1)>=3
CurrentSum+x>=3*(CurrentCount +1)
x>=3*(CurrentCount +1)-CurrentSum

 

[JoeU2004]

C2 through C22 represent the work days in August.
In each cell total "Talk Time" minutes are entered
manually. C23 adds these up and returns them as
hours and minutes.

English can be an ambiguous language for describing contents of Excel cells.
It would be better to show examples of data and formulas as they are
entered. These "little details" affect the solution in material ways.


If all cells contain time in the format [h]:mm, the new cell formula might
be:

=max(0, "3:00"*(C25+1) - C23)

formatted as [h]:mm.

Note: If Regional and Language settings permit a time separator other than
":" (colon), replace "3:00" with TIME(3,0,0) or 0.125.


But if you want the new cell to contain just minutes as an integer, perhaps
like C2:C22, use one of the following formulas, depending on the format of
C23:

(a) if C23 contains total time in the format [h]:mm, then:

=max(0,180*(C25+1) - C23*1440)

(b) if C23 contains total time as hours and fractional hours, then:

=max(0, 180*(C25+1) - C23*60)


In all case, the use of MAX(0,...) covers the case where current total time
(C23), in hours, exceeds 3 times the number of current workdays plus 1.

Consider the example of 7 current workdays with 3 hr 30 min of Talk Time
each. Without MAX(0,...), the formulas in #a and #b result in -30. But the
first formula ("3:00"*...) displays "###" because Excel will not display
negative time in [h]:mm format. In either case, it does not make sense for
"next day's Talk Time" to be negative time; greater than or equal to zero
covers the requirement.


----- original message -----