Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for
Keeping Track of my Broadband Usage - V2
Topic Options
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Plusnet Community
- :
- Forum
- :
- Help with my Plusnet services
- :
- Broadband
- :
- Keeping Track of my Broadband Usage - V2
Keeping Track of my Broadband Usage - V2
09-11-2012 10:19 AM
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Report to Moderator
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
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.
You know what a turtle is? Same thing.
Message 1 of 7
(1,416 Views)
6 REPLIES 6
Re: Keeping Track of my Broadband Usage - V2
09-11-2012 10:34 AM
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Report to Moderator
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:
Could you use that so you only have one version of the spreadsheet that automatically adapts?
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 (£14.40/month) Mobile: iD mobile (£4/month) |
Message 2 of 7
(795 Views)
Re: Keeping Track of my Broadband Usage - V2
09-11-2012 10:45 AM
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Report to Moderator
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 (£14.40/month) Mobile: iD mobile (£4/month) |
Message 3 of 7
(795 Views)
Re: Keeping Track of my Broadband Usage - V2
09-11-2012 10:58 AM
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Report to Moderator
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.
You know what a turtle is? Same thing.
Message 4 of 7
(795 Views)
Re: Keeping Track of my Broadband Usage - V2
09-11-2012 10:59 AM
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Report to Moderator
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.
Windows 10 Firefox 109.0 (64-bit)
To argue with someone who has renounced the use of reason is like administering medicine to the dead - Thomas Paine
To argue with someone who has renounced the use of reason is like administering medicine to the dead - Thomas Paine
Message 5 of 7
(795 Views)
Re: Keeping Track of my Broadband Usage - V2
09-11-2012 11:13 AM
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Report to Moderator
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 (£14.40/month) Mobile: iD mobile (£4/month) |
Message 6 of 7
(795 Views)
Re: Keeping Track of my Broadband Usage - V2
09-11-2012 11:29 AM
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Report to Moderator
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.
You know what a turtle is? Same thing.
Message 7 of 7
(795 Views)
Topic Options
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Plusnet Community
- :
- Forum
- :
- Help with my Plusnet services
- :
- Broadband
- :
- Keeping Track of my Broadband Usage - V2