CDR calculation

[rajeshi1980]

Hello,

Need help in calculating Call Detail Record of few mobile numbers.

Have already calculated all new rates as per new tariff, but am not able to deduct free minutes from the local charges.

basically in the raw cdr, with values, i want to make $0 to the top 500 minutes of local calls.
Can someone help me with this...

 

[ztodd]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]dt[/TD]
[TD]tm[/TD]
[TD]orig[/TD]
[TD]dest[/TD]
[TD]typ[/TD]
[TD]dur[/TD]
[TD]loc cum dur[/TD]
[TD]adj dur[/TD]
[TD]amt[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]02/12/2013[/TD]
[TD]08:30:00[/TD]
[TD]334455323[/TD]
[TD]7738737[/TD]
[TD]Local Call[/TD]
[TD]0:02:00[/TD]
[TD]0:02:00[/TD]
[TD]0:00:00[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]02/12/2013[/TD]
[TD]08:40:00[/TD]
[TD]334455323[/TD]
[TD]9992222222[/TD]
[TD]ILD[/TD]
[TD]0:00:32[/TD]
[TD]0:02:00[/TD]
[TD]0:00:32[/TD]
[TD]18.2[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]02/12/2013[/TD]
[TD]08:44:00[/TD]
[TD]334455323[/TD]
[TD]3362733[/TD]
[TD]Local Call[/TD]
[TD]0:07:00[/TD]
[TD]0:09:00[/TD]
[TD]0:00:00[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]02/12/2013[/TD]
[TD]08:49:00[/TD]
[TD]334455323[/TD]
[TD]3362733[/TD]
[TD]Local Call[/TD]
[TD]0:48:00[/TD]
[TD]0:57:00[/TD]
[TD]0:47:00[/TD]
[TD]5.2[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]02/13/2013[/TD]
[TD]08:30:00[/TD]
[TD]334455323[/TD]
[TD]7738737[/TD]
[TD]Local Call[/TD]
[TD]0:02:00[/TD]
[TD]0:59:00[/TD]
[TD]0:02:00[/TD]
[TD]1.2[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]02/13/2013[/TD]
[TD]08:40:00[/TD]
[TD]334455323[/TD]
[TD]9992222222[/TD]
[TD]ILD[/TD]
[TD]0:00:32[/TD]
[TD]0:59:00[/TD]
[TD]0:00:32[/TD]
[TD]18.2[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]02/13/2013[/TD]
[TD]08:44:00[/TD]
[TD]334455323[/TD]
[TD]3362733[/TD]
[TD]Local Call[/TD]
[TD]1:12:00[/TD]
[TD]2:11:00[/TD]
[TD]1:12:00[/TD]
[TD]9.8[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]02/13/2013[/TD]
[TD]08:49:00[/TD]
[TD]334455323[/TD]
[TD]3362733[/TD]
[TD]Local Call[/TD]
[TD]0:48:00[/TD]
[TD]2:59:00[/TD]
[TD]0:48:00[/TD]
[TD]5.2[/TD]
[/TR]
</tbody>[/TABLE]

Using the 2nd method- I also added a "helper" column to show cumulative local call duration in column G.

Make your sheet look like the table above.
formula in G2: =SUMIF(F$2:F2,"Local Call",G$2:G2)
formula in H2: =IF(F2="Local Call",IF(H2<10/60/24,0,IF(H2-G2<10/60/24,H2-10/60/24,G2)),G2)

Fill down column G and H.
Then change your formula in column I to be based on column H instead of F.