Dynamic List, with a catch

[jamhr001]

Hello all. First post...

Here is a simplified example of what I want to accomplish.

I can change the individual's mobile data rate plan for my group each month, depending on usage.

I have ~1000 lines to work with.

The plans are only grouped for readability. Rate plans are:
01. 1 MB Plan - $2.00
02. 15 MB Plan - $3.50
03. 25 MB Plan - $5.00

04. 50 MB - 6.50
05. 100 MB - 7.50
06. 500 MB - 11.00

07. 1 GB - 13.50
08. 2 GB - 20.00
09. 5 GB - 40.00

10. 10 GB - 70.00
11. 20 GB - 130.00
12. 40 GB - 275.00
13. 80 GB - 550.00

Note: the monthly dollar cost (approximate) is not part of the equation. It is only included to show that, as you would expect, the higher the plan, the MORE it costs/month, but the LESS it costs/MB, whether we use the data or not.

I need to look at the usage of each line, each month, and assign the best plan.

Easy!

-------------------------------------------

But, my agreement allows me to AVERAGE my lines into rate-plan based "pools."

Example: Sorted by data usage, the first 84 lines used 0.00 MB - nothing at all. Paper weights. They go to the lowest cost plan, obviously, the cheapest 1 MB plan.

But because the first 84 lines used 0 MB, I can also include the next 6 lines under the same "pooled" 1 MB plan, even though they used a combined 73 MB of data.

Averaged in together, the first 90 lines used .81 MB per line.

(An additional requirement: I need the "pooled averages" to be <= 90% of the data allotted by a plan. So anything pushing the average above 45 MB would move from the 50 MB Plan group to the 100 MB Plan, etc.)

So here is my question:

For assigning plans to individual lines by their data usage, it would be easy to do an approximate VLOOKUP (thanks to Mr. Excel), but that will not allow for pooling by least expensive plan.

It is also fairly simple to 1)sort the lines by data usage, least to greatest, and 2) use a rolling average (=AVERAGE($B$2:Bn) to find the FIRST group, where the "pooling average" is <= 1 MB * 90%. The are my 1MB Plan.

But I'm stumped as to how to begin a new pooling average, excluding those 6 lines (from the example) that nominally exceed the 1 MB rate plan, but are included with the 1MB Plan as part of the "pooling average."


This is a very simplified example of what the real spreadsheet (with 1000+ lines) looks like.

Phone # | Data Usage | Roll Avg | Plan
------------------------------------------
9447891232 | 0.00 | 0.00[/FONT][/B] | 1 MB
9444213587 | 0.00 | 0.00 | 1 MB
9443413888 | 5.55 | 1.85 | 15 MB
9441533558 | 11.32 | 4.22 | 15 MB
9443512351 | 77.22 | 18.82 | 25 MB

---------------

This is very long. If you're still with me, I can't help but think there's an easy solution. Any takers?

Thanks.

 

[jamhr001]

Hey Fluff,

I see you edited my post and assume you are one of the mods. I was hoping the post could be in the "Courier New" font so things would line up properly. Is that alright?

Thanks!

 

[offthelip]

Assuming the average data usage is in column B in row 3

put the first rate plan actual limit in F2 (1mb)
put this equation in F3 and copy down :
=AVERAGE(B$3:B3)
put this in G3 and copy down:
=F3>F$2
put the 2nd rate plan limit in H2 (15mb)
put this in H3 and copy down
[TABLE="width: 354"]
<tbody>[TR]
[TD="width: 354"]=IFERROR(AVERAGEIF(G$3:G3,TRUE,B$3:B3),0)
put this in I3 and copy down
=H3>H$2
etc ,etc across the columns until you have done all plans

[/TD]
[/TR]
</tbody>[/TABLE]