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

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.

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

Experts Exchange Solution brought to you by

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

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