Mobile Phone Bill

[NTZ]

Hi, my boss has asked me to look at our mobile/cell phone bill. We should recieve three thousand minutes of free air time each month. The duration of the calls are marked onthe bill in hrs.min.sec e.gif a call lasted 8mins and 43sec then it shows as 00:08:43.
This information is all in one column how do I add the figures in the column to give me a total at the bottom that displays the total number of free hours, mins, sec we have recieved.

Many Thanks

NTZ

 

[NBVC]

Try:


=IF(SUM(A1:A100)>2.08333333,2.0833333,SUM(A1:A100))

Adjust range as necessary:

2.08333 is Excel's interpretation of 3000 min (or 50 hrs) or 2.08333 days

You may have to format result as Time.

 

[just_jon]

Maybe

=MAX(3000/(24*60),SUM(A1:A100))

 

[al_b_cnu]

Maybe

=MAX(3000/(24*60),SUM(A1:A100))

Hi JJ,
Doesnt the OP want MIN not MAX here?

 

[NBVC]

Maybe

=MAX(3000/(24*60),SUM(A1:A100))

My interpretation of the OPs question is to find out how many free minutes were used in the month.

Your formula returns 3000 minutes always (which is not necessarily how much time they used up), unless the sum(a1:a100) is more than 3000. But then those extra minutes are not free minutes anymore.

Maybe =MIN(3000/(24*60),SUM(A1:A100))

 

[NTZ]

Hi thanks for the replies, I haven't explained myself very well. I have filtered the bill by the call charge so the column I am left with is the calls that are marked as free. So I am just adding these times together to give me the total number of free hour,mins and secs.

Thanks NTZ

 

[NBVC]

Then just use =Sum(A1:A100) formatted as Time:

 

[NBVC]

Actually,

For your filtered case (if you used DATA|FILTER|AUTOFILTER), better to use

=SUBTOTAL(9,A1:A100). This will sum everything after the filtering (ie. not include hidden values).

 

[just_jon]

Maybe

=MIN(3000/(24*60),SUM(A1:A100))

Hi JJ,
Doesnt the OP want MIN not MAX here?

Absolutely, need coffee !

 

[just_jon]

Hi thanks for the replies, I haven't explained myself very well. I have filtered the bill by the call charge so the column I am left with is the calls that are marked as free. So I am just adding these times together to give me the total number of free hour,mins and secs.

Thanks NTZ

Can also use SUMIF

=SUMIF(FreeColumn,"Free",MinutesColumn)