CDR calculation

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?

For eg:



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

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?

ztodd said:

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?

Click to expand...

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.

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.

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...

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.

Am ok with calculating in the same row instead of splitting...

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.