Formula for a per minute charge for a call 1

[telecomadmin12]

I have two columns in Excel. One shows the duration of a call. The other one the total charges for that call. If the call is shorter than a minute I am still being charged the rate for one whole minute. I want to create another column that shows the per minute charge for the respective call. Can you help with a formula?
Duration Of Call Call Charges ( USD )
0:00:28 $0.1890
0:07:34 $1.5120
0:12:53 $0.7670

 

[xlhelp]

=(HOUR(A1)+MINUTE(A1)+1)/60*60*B1 provided your values are columns A and B


Avoid Hangovers. Stay drunk.

 

[telecomadmin12]

Thank you very much.
The formula is not giving me the desired results. Actually the two columns get multiplied. Here is what I get assuming that column A is the duration of a call, column B the total charge and column C where I have the formula.
Time----Total Cost----Charge per minute
0:00:28 $0.1890-------$0.1890
0:07:34 $1.5120-------$12.0960
0:12:53 $0.7670-------$9.9710

 

[xlhelp]

I think I have trouble with comprehension.

Sorry, the formula should be
=B1/(HOUR(A1)+MINUTE(A1)+1)/60*60




Avoid Hangovers. Stay drunk.

 

[telecomadmin12]

Thank you, that works.
Can you explain what the /60*60 at the end of the formula does?

 

[telecomadmin12]

Also it works well when the hour value is 0. When a call took longer than an hour I get a result like this:
Time----Total Cost-Charge per minute
1:02:47 $3.7170 $0.9293

The charge per minute should be $0.059 instead of $0.9293 (3.717/63). Can you assist?
Thanks again.

 

[SkipVought]

Cost per minuite: Charge/(duration*24*60)

The UNITS must agree.

So the Charge is in units of DOLLARS
Duration is in units of DAYS

So we have

DOLLARS    DAY        HOUR          DOLLARS
------- x  -------- x ---------- == -------
DAYS       24 HOURS   60 MINUTES    MINUTE

Result

[b]
duration charge  per min
0:00:28  0.1890  0.4050[/b]
0:07:34  1.5120  0.1998
0:12:53  0.7670  0.0595
1:02:47  3.7170  0.0592

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[xlhelp]

just change it to
=B1/(HOUR(A1)*60+MINUTE(A1)+1)/60*60

Please note: my formula above wil not work well when seconds are Zero. in that case the formula becomes
=IF(SECOND(A1)=0,B1/(HOUR(A1)*60+MINUTE(A1))/60*60,B1/(HOUR(A1)*60+MINUTE(A1)+1)/60*60)



Avoid Hangovers. Stay drunk.

 

[SkipVought]

converting a Date/Time value to Hours & Minutes & Seconds is so unnecessary!

All you need to is convert DAYS to MINUTES in the formula! It is so simple!

I can't remember exactly who it was, I think it was a college physics instructor, who gave us this great tip for formulas...
[tt]
The UNITS are the KEY to any formula.

So if you need DOLLAS per MINUTE as the result and the units of the source data are DOLLARS and DAYS, then you must convert DAYS to MINUTES. And we know that there are 24 HOURS per DAY and 60 DAYS per MINUTE, so thoes are the factors we need to manupulate. BUT do you MULTIPLY or DIVIDE??? The UNITS lead you so that you get DOLLARS in the numerator and MINUTES in the denominator as my example of 22 Jul 13 10:15 attempts to demonstrate.
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Andrzejek]

60 DAYS per MINUTE

That's either one long day, or one very short minute.
Or ia that another way around....?

Have fun.

---- Andy

 

[SkipVought]

60 MINUTES per HOUR

But you know one day flows into the next!

And "with The Lord a day is as 1000 year and 1000 yeas as a day!" So how do you program THAT???

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[byundt]

I rewrote xlhelp's formula as:

=B1/(HOUR(A1)*60+MINUTE(A1)+(SECOND(A1)>0))

The Boolean expression SECOND(A1)>0 rounds the seconds up to the next minute.

It is worth noting that =1/60*60 will return 1. If you want 1/3600 you need =1/(60*60) according to Excel's order of precedence of operators.

You could also use the CEILING function to round up the number of minutes. In the formula below, 1440 = 24*60 is the number of minutes in a day.

=B1/CEILING(1440*A1,1)

When displaying the answers, I saw that the rates included tenths of a cent, so you will want to use Number format rather than Currency for accuracy. The results of my formulas will also differ slightly from Skip's formula because he wasn't rounding the seconds up.

Brad