rajeshi1980

New Member
Joined
May 9, 2013
Messages
14
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...
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

ztodd

Board Regular
Joined
Sep 17, 2014
Messages
221
Yep we can help, if you give us a lot more details. :) Can you give us some sample input and output that you're looking for?
 

rajeshi1980

New Member
Joined
May 9, 2013
Messages
14
For eg:

DateTimeOrigin NumberDestination NumberType of CallDuration of CallAmount
2/12/20138:30:003344553237738737Local Call0:02:001.2
2/12/20138:40:003344553239992222222ILD0:00:3218.2
2/12/20138:44:003344553233362733Local Call0:08:009.8
2/12/20138:49:003344553233362733Local Call0:48:005.2
2/13/20138:30:003344553237738737Local Call0:02:001.2
2/13/20138:40:003344553239992222222ILD0:00:3218.2
2/13/20138:44:003344553233362733Local Call1:12:009.8
2/13/20138:49:003344553233362733Local Call0:48:005.2

<colgroup><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>


and if 10 minutes is free for local calls, then the out put to be...


DateTimeOrigin NumberDestination NumberType of CallDuration of CallAmount
2/12/20138:30:003344553237738737Local Call0:02:000
2/12/20138:40:003344553239992222222ILD0:00:3218.2
2/12/20138:44:003344553233362733Local Call0:08:000
2/12/20138:49:003344553233362733Local Call0:48:005.2
2/13/20138:30:003344553237738737Local Call0:02:001.2
2/13/20138:40:003344553239992222222ILD0:00:3218.2
2/13/20138:44:003344553233362733Local Call1:12:009.8
2/13/20138:49:003344553233362733Local Call0:48:005.2

<colgroup><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>
 

ztodd

Board Regular
Joined
Sep 17, 2014
Messages
221
Q1. Is the amount column already using a formula?
Q2. What if local call #X doesn't land exactly on 500 minutes, but goes over it- you still want that to show zero?
 

rajeshi1980

New Member
Joined
May 9, 2013
Messages
14

ADVERTISEMENT

Q1. Is the amount column already using a formula?
Q2. What if local call #X doesn't land exactly on 500 minutes, but goes over it- you still want that to show zero?


A1. The amount column is using a formula, but its just the call rate * duration of call.
A2. Considering the above, it would be good if we can break the call #X into 2, i.e. if the call is of 00:04:50 and the 500 minutes value comes till the first minute, then maybe the call can be broken into two, 1. with call duration of 1 min, and 2. with call duration of 00:03:50. in this case, i will recalculate the call based on the new call duration.
 

ztodd

Board Regular
Joined
Sep 17, 2014
Messages
221
It looks like your data is in order by date and time right now?

If you're ok with more of a manual solution, you could : (Try to make sure you understand the logic behind these steps)

0. Make a backup of your workbook
1. Re-sort by type of call
2. Find where local calls start, highlight the amount column starting from that row down until the total showing at the bottom is just under 500 minutes (so that if you were to include one more row it would be over 500).
3. Write down this total
4. Type 0 [Enter], then press Control-D to fill down
5. Find how many minutes were left to reach 500 minutes: take 500 minutes minus the total you wrote down
6. Insert a row to break the next call into 2 as you described, put zero on the first row, and update the next row (will now be the first non-zero local call row) to subtract the # minutes left to reach 500 that you found in previous step.
7. If you need it back in the original order, re-sort by date and time.
 
Last edited:

rajeshi1980

New Member
Joined
May 9, 2013
Messages
14

ADVERTISEMENT

Thanks for this manual process... but I have been doing like this for a while now... I was looking for an automated procedure of doing this...
 

ztodd

Board Regular
Joined
Sep 17, 2014
Messages
221
Have you been splitting one row into 2 like that all the time? To do that would require VBA, but if it's ok to keep it one row and just calculate the amount using how much is left over the 500 minutes, then we could get away with just using a formula. But if you're looking to try to understand the formula or code so that it's more maintainable for you, then VBA might actually be easier to understand- although you'd still likely have a little learning curve either way.
 

ztodd

Board Regular
Joined
Sep 17, 2014
Messages
221
Sorry for the delay- if you still needs this, it would probly be easier for me if I knew your current formula so I can modify it. It doesn't seem to be a straight linear rate, if your example has true calculated #s in it...

Or we could add a column for adjusted duration which would either zero out, subtract the left over to reach 500, or leave the same- then you can make your formula for the amount to use the new adjustment duration column.
 

Forum statistics

Threads
1,141,062
Messages
5,704,050
Members
421,325
Latest member
tapete86

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top