Hi All,

I am having to workout my companies telephone bill to make sure the
carrier isn't charging more than what we agreed, I have done a few
calls manually but with 20,000 calls im not doing them all.

What I have is:-
The duration
The cost per minute
The connection charge

This is one example using easy numbers:-
Duration 1 minute received as 00:01:00
Cost Per Min received as 0.12p twelve pence per min
Connection Charge as 0.02p two pence

But here's my problem if I do =(00:01:00*0.12)+0.02 it doesn't work,
if i do it using cell referances I get the result of 0.020083333

So to get round this I put the time in as a calculation =((mm*60)+ss)/
60 but I have to do this for each call. is there a easier way?

Cheers
Mark :o)

Times are held in excel as fractions of a day.
Converting the .12/minute to a /day value comes to 172.8/day
(That's 60*24*0.12)
Now multipy each time by 172.8 then add 0.02, not forgetting to format
the result cell as currency in case it wants to display as a date.
(I've assumed they charge pro rata for fractions of a minute rather
than in increments of 1 minute)


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=126934

Well, if you are being charged 12 pence per minute plus a 2 pence
connection charge you are being vastly overcharged. Think about
changing supplier - it's easier than you think.

Pete

On Aug 19, 11:09*pm, Sh0t2bts wrote:
Hi All,

I am having to workout my companies telephone bill to make sure the
carrier isn't charging more than what we agreed, I have done a few
calls manually but with 20,000 calls im not doing them all.

What I have is:-
The duration
The cost per minute
The connection charge

This is one example using easy numbers:-
Duration 1 minute received as 00:01:00
Cost Per Min received as 0.12p twelve pence per min
Connection Charge as 0.02p two pence

But here's my problem if I do =(00:01:00*0.12)+0.02 it doesn't work,
if i do it using cell referances I get the result of 0.020083333

So to get round this I put the time in as a calculation =((mm*60)+ss)/
60 but I have to do this for each call. is there a easier way?

Cheers
Mark :o)

p45cal, thanks that worked a treat :o)

Pete_UK, I have just changed carrier and you are right the rates are
vastly different, calls to 118 number are hight 20p+ connection charge
16p per min..... local calls are better .008p connection charge 0.012p
per minute. I used full pence to make the reading easier.

Thanks Both for you help and advise
Mark :o)

Still not brilliant !!

You can get less than 1p/min (i.e. 0.7p) for Local/National calls with
no connection charge and no minimum charge, per second billing etc.
from a number of different (good quality) suppliers.

Pete

On Aug 20, 12:01*am, Sh0t2bts wrote:
p45cal, thanks that worked a treat :o)

Pete_UK, I have just changed carrier and you are right the rates are
vastly different, calls to 118 number are hight 20p+ connection charge
16p per min..... local calls are better .008p connection charge 0.012p
per minute. I used full pence to make the reading easier.

Thanks Both for you help and advise
Mark :o)