cancel
Showing results for 
Search instead for 
Did you mean: 

Keeping Track of my Broadband Usage - V2

lorisarvendu
Grafter
Posts: 334
Registered: ‎26-08-2007

Keeping Track of my Broadband Usage - V2

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
A tortoise? What's that?
You know what a turtle is? Same thing.
6 REPLIES
Community Veteran
Posts: 26,720
Thanks: 934
Fixes: 10
Registered: ‎10-04-2007

Re: Keeping Track of my Broadband Usage - V2

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?
jelv (a.k.a Spoon Whittler)
   Why I have left Plusnet (warning: long post!)   
Broadband: Andrews & Arnold Home::1 (FTTC 80/20)
Line rental: Pulse 8 Home Line Rental (£13/month)
Mobile: iD mobile (£4/month)
Community Veteran
Posts: 26,720
Thanks: 934
Fixes: 10
Registered: ‎10-04-2007

Re: Keeping Track of my Broadband Usage - V2

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.
jelv (a.k.a Spoon Whittler)
   Why I have left Plusnet (warning: long post!)   
Broadband: Andrews & Arnold Home::1 (FTTC 80/20)
Line rental: Pulse 8 Home Line Rental (£13/month)
Mobile: iD mobile (£4/month)
lorisarvendu
Grafter
Posts: 334
Registered: ‎26-08-2007

Re: Keeping Track of my Broadband Usage - V2

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
A tortoise? What's that?
You know what a turtle is? Same thing.
Moderator
Moderator
Posts: 27,070
Thanks: 1,785
Fixes: 144
Registered: ‎14-04-2007

Re: Keeping Track of my Broadband Usage - V2

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.

Customer and Forum Moderator. Windows 10 Firefox 62.0.3 (64-bit)

Community Veteran
Posts: 26,720
Thanks: 934
Fixes: 10
Registered: ‎10-04-2007

Re: Keeping Track of my Broadband Usage - V2

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.
jelv (a.k.a Spoon Whittler)
   Why I have left Plusnet (warning: long post!)   
Broadband: Andrews & Arnold Home::1 (FTTC 80/20)
Line rental: Pulse 8 Home Line Rental (£13/month)
Mobile: iD mobile (£4/month)
lorisarvendu
Grafter
Posts: 334
Registered: ‎26-08-2007

Re: Keeping Track of my Broadband Usage - V2

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
A tortoise? What's that?
You know what a turtle is? Same thing.