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

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...

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.


  • 4
3 Solutions
Brad Sims, CCNANetwork AdministratorCommented:
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.
barry houdiniCommented:
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:
format result cell as currency
[1440 is the number of minutes in a day]
regards, barry
barry houdiniCommented:
...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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

barry houdiniCommented:
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

PendleBusinessSolutionsAuthor 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...

barry houdiniCommented:
Glad to hear that Chris - is the cheque in the post?!
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.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now