Good Morning,

I am a novice Excel user, but i work in the expense world, and I wanted to create a simplified function to run against multiple lines of users if possible to save one of my data entry folks some time(and me, from doing manual calculations each month).

The scenario is this. A Telecom provider bills an overage fee for data used against a pool of GB. Lets say the Client has 120GB of data, pooling at 6GB per user.

The current billing method of the vendor, is to charge whichever user is using the pooled data when it exceeds the 120GB. This means a user that uses 1GB of data for the month, well under their 6GB of data, would be charged for that overage fee. Even if someone else used 25GB earlier in the month, they would not be billed an overage if the total pool had not been exceeded.

The manual formulas I use on my excel document to correct this are as such(The document breaks out rows of phone numbers, with columns that seperate the types of charges):

Filter to users who exceeded their 6GB data contribution.
Divide those users GB Usage by the total GB of all users exceeding 6GB.
Multiply each users percentage obtained from the prior formula against the total overage fee for the month.
In the column where the fees originally showed, they would be clean slated, and have these corrected totals applied.


Now that you see my process, is there a simpler method or a single formula to automate what I am going to have to do each month manually for this? If I can represent these steps to folks who are resistant to anything more complex then a system driven tool, I need to make it as few steps as possible to eliminate pushback.

If anything can be achieved from this I will try to contribute back in someway. Cheers.