• Status: Solved
• Priority: Medium
• Security: Public
• Views: 8428

# How to calculating cost per Minute in Excel 2007

I'm trying to sum all the different tariffs on a phone bill to Calculate the cost per minute in excel, so I can do a cost comparison with other providers...

E.g.
Local Calls: 21 hours, 15 minutes, 30 seconds (21:15:30) I have been charged £22.04
National Calls: 26 hours, 26 minutes, 42 seconds (26:26:42) I have been charged £26.33

I would then like total these to get an average cost per minute across Local and Nation Calls.

I have made some progress, in that 21 hours if charged £22.04 comes out at 1.7pence per minute,
But when it goes over 24 hours it goes haywire...

Cheers Guy's.

Chris

0
• 4
3 Solutions

Unless I'm mistaken, you'll need to multiply the number of hours * 60 to get the number of minutes and divide by the total.
0

Commented:
If you have the duration in time format in A1 and the cost in £ in B1 then you can use this formula to get the cost per minute:
=B1/(A1*1440)
format result cell as currency
[1440 is the number of minutes in a day]
regards, barry

0

Commented:
...obviously if you want the average for a number of different times and amounts then you need to use the total time in place of A1 above and the total cost in place of B1, so if you have your two times in A1 and a2 and two amounts in B1 and B2 the formula would become
=SUM(B1:B2)/(SUM(A1:A2)*1440)
barry

0

Commented:
See attached example:
Note that the result is in £ i.e. £0.017 represents 1.7 pence. If you want to actaully get 1.7 then multiply by 100
barry

25535979.xls
0

Author Commented:
Barry, your the man... many thanks, it works great...

I've just saved a client over 40% on their Landline calls and 30% on Mobiles...

Regards,
Chris.
0

Commented:
Glad to hear that Chris - is the cheque in the post?!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.