Red Diesel Tax Calculator for Narrowboat

The IWA has a Campaign to keep derogation on Red Diesel. Something that is close to my heart. They have on their site a nifty spreadsheet which can be used to calculate the amount that people should be paying... but I needed to calculate how much we were using for propulsion or other purposes; not being a continuous cruiser it'd not be anywhere near the 60/40% split that HMRC reckons (60% for propulsion and 40% for other purposes such as hot water and electricity generation).

Being a bit of a novice when it comes to spreadsheets I thought I'd try to work it out myself and, with a little (okay, a lot of!) help, I came up with the following schema on Google Docs:

• Column A - The date, watch out for the localization here, make sure it's British (DD/MM/YYYY).
• Column B - Purpose, if you want this to act as something of a log make sure that the non-propulsion purpose is obvious, mine is "Electricity & Water", everything else is ignored.
• Column C - Is the hours on the clock when you turn the engine off.
• Column D - This is calculated by entering something like "=CX-C(X-1)"; so it you're on line 10 the formula would be "=C10-C9".
• Column E - Is the total hours used, or "=sum(D:D)".
• Column F - Is the total hours used for non-propulsion reasons, or "=sumif(B:B, "Electricity & Water", D:D)".
• Column G - Is the percentage used for non-propulsion reasons, or "=(F1/E1)".

I started doing this ages ago (08/10/2008) and I'm now onto my second sheet so I've had to alter columns E, F and G to:

• Column E (Sheet 2) - Total hours used: "=sum(sum(D:D),Sheet1!E1)".
• Column F (Sheet 2) - Total hours used for non-propulsion reasons: "=sum(sumif(B:B, "Electricity & Water", D:D),Sheet1!F1)".
• Column G (Sheet 2) - Percentage used for non-propulsion reasons: "=(F1/E1)".

Dead easy isn't it?