Keeping Track of my Broadband Usage - V2

[#1]

You may remember an earlier thread where I'd made myself a handy Excel spreadsheet to keep track of data usage over each month.  Well now we've upgraded to 80/20 with a bigger Allowance (250GB) but faster download speed, so keeping an eye on things is more important, especially now that both my kids have discovered Youtube.
All I do is put down the daily figures for Peak and Overnight usage from the VMBU and the spreadsheet does the rest.  I've got colums that show how far above or below my daily allowance I am for each day (250Gb a month works out round about 8Gb a day),  or how far cumulatively I'm above or below my expected Allowance as we move through the month (in other words how much I've got to spare, or if we've got to pull our horns in).
There are three graphs showing daily peak and overnight usage, average daily and overnight usage, and cumulative daily and overnight usage.  Expected Allowance usage is shown by a green line in all three, so I can easily see how far under (or over) Allowance we are.   I previously provided this free as-is when I was on 40/10, and now I'm on 80/20 I've updated it to reflect this.  I've also corrected some fundamental errors , like realising that "Allowance Per Day" changes depending on how many days there are in the month.  Duh.  So I've now provided 4 templates for the 4 different monthly day lengths (28, 29, 30, 31).  
If you would like to try this out you can download it from here:  
http://www.spacewarp.co.uk/FTTC-USAGE.ZIP
There's an example with data in so you can see how it works, and a  README file with instructions on how to change it to suit your own Allowance level (even if you're on ADSL). Please read the README file, and any comments are more than welcome.  
There are no viruses embedded in either of these files, but you should always scan any zip file you download from the internet.

Dave

[#2]

Why didn't you have a cell at the top in to which the allowance could be entered and use that in all the formula?
Given a month start date in cell B10, this formula will tell you the number of days in the current billing month:

=EDATE(B10,1)-B10

Could you use that so you only have one version of the spreadsheet that automatically adapts?

[#3]

You might also consider whether http://community.plus.net/forum/index.php/topic,103607.msg881633.html#msg881633 would be useful in conjunction which gives all the usage in GB removing the need to mess about with GB/MB/KB conversions when entering the data.

[#4]

Quote from: jelv

Why didn't you have a cell at the top in to which the allowance could be entered and use that in all the formula? Given a month start date in cell B10, this formula will tell you the number of days in the current billing month: =EDATE(B10,1)-B10 Could you use that so you only have one version of the spreadsheet that automatically adapts?

It would have to adapt to the extent of creating the correct number of rows as needed (31 for a 31-day month, 28 for February etc), plus populating them with formulae.  I'm not fantastic at Excel, and I can't figure out how to actually generate the extra rows from a single default spreadsheet simply by putting a month in.  That's too high-level for me.
Putting an allowance entry field is a good idea though, and I never thought of that.  However that wouldn't be necessary for an individual user, as Allowances don't change that frequently, but I take your point.
-Dave

[#5]

Quote from: jelv

Why didn't you have a cell at the top in to which the allowance could be entered and use that in all the formula?

I put the monthly allowance in cell D7 and changed P10 to =IF(ISBLANK(C10),"",$D$7-I10) then drag it down to fill all appropriate cells in that column.

[#6]

Haven't tried it yet but if the spreadsheet had 31 rows I think you could use conditional formatting to hide the last 1,2 or 3 rows using the row number and the number of days in the month.

[#7]

Quote from: Strat

I put the monthly allowance in cell D7 and changed P10 to =IF(ISBLANK(C10),"",$D$7-I10) then drag it down to fill all appropriate cells in that column.

Thanks.  I have done exactly this, modified the templates and Readme file and re-upped the ZIP.
-Dave