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

 
PendleBusinessSolutionsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
0
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:
=B1/(A1*1440)
format result cell as currency
[1440 is the number of minutes in a day]
regards, barry
 
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
=SUM(B1:B2)/(SUM(A1:A2)*1440)
barry
 
0
Introduction to Web Design

Develop a strong foundation and understanding of web design by learning HTML, CSS, and additional tools to help you develop your own website.

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
barry
 

25535979.xls
0
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...

Regards,
Chris.
0
barry houdiniCommented:
Glad to hear that Chris - is the cheque in the post?!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Spreadsheets

From novice to tech pro — start learning today.