GAS meter spreadsheet

GAS meter spreadsheet

I would be grateful if you could help me with this problem,
perhaps by suggesting suitable freeware,
or maybe by pointing me to sources of help.

SITUATION: I take gas meter readings at random intervals;
sometimes seven days might elapse between readings, sometimes seventeen.
It could be any number of days between readings.

REQUIREMENT: I want to know how much gas has been used in the year up to
any day. The day might not be one on which I took a meter reading. Also,
the day 365 days before that might also be one for which the meter was
not read. I would assume linear interpolation between two adjacent
readings.

QUESTIONS: What would be the best way of doing this?
Spreadsheet?
Database?
Python or similar?
Anything else?

I should point out that I am not experienced with any of the above,
which means that I would have to learn from scratch whatever method is
suggested!

You might even know of the existence of something that will do it
already?
--
Chris

Chris,

Database -- not really. Spreadsheet -- yes. A good application for this.
It would not likely be complicated, depending on how fancy you want it.

Do you have a spreadsheet program (Excel, etc.)? Are you interested in
delving a bit into spreadsheet design? If not, consider getting someone to
do this for you if it's just the end result you're interested in.

Earl Kiosterud
www.smokeylake.com

"Chris" <nospam@[127.0.0.1]> wrote in message
news:Tcep0HHBUyhDFwdh@[127.0.0.1]...
> GAS meter spreadsheet
>
> I would be grateful if you could help me with this problem,
> perhaps by suggesting suitable freeware,
> or maybe by pointing me to sources of help.
>
> SITUATION: I take gas meter readings at random intervals;
> sometimes seven days might elapse between readings, sometimes seventeen.
> It could be any number of days between readings.
>
> REQUIREMENT: I want to know how much gas has been used in the year up to
> any day. The day might not be one on which I took a meter reading. Also,
> the day 365 days before that might also be one for which the meter was not
> read. I would assume linear interpolation between two adjacent readings.
>
> QUESTIONS: What would be the best way of doing this?
> Spreadsheet?
> Database?
> Python or similar?
> Anything else?
>
> I should point out that I am not experienced with any of the above,
> which means that I would have to learn from scratch whatever method is
> suggested!
>
> You might even know of the existence of something that will do it already?
> --
> Chris

In article <#[email protected]>, Earl Kiosterud
<[email protected]> writes
>"Chris" <nospam@[127.0.0.1]> wrote in message
>news:Tcep0HHBUyhDFwdh@[127.0.0.1]...
>> GAS meter spreadsheet
>>
>> I would be grateful if you could help me with this problem,
>> perhaps by suggesting suitable freeware,
>> or maybe by pointing me to sources of help.
>>
>> SITUATION: I take gas meter readings at random intervals;
>> sometimes seven days might elapse between readings, sometimes seventeen.
>> It could be any number of days between readings.
>>
>> REQUIREMENT: I want to know how much gas has been used in the year up to
>> any day. The day might not be one on which I took a meter reading. Also,
>> the day 365 days before that might also be one for which the meter was not
>> read. I would assume linear interpolation between two adjacent readings.
>>
>> QUESTIONS: What would be the best way of doing this?
>> Spreadsheet?
>> Database?
>> Python or similar?
>> Anything else?
>>
>> I should point out that I am not experienced with any of the above,
>> which means that I would have to learn from scratch whatever method is
>> suggested!
>>
>> You might even know of the existence of something that will do it already?

>Database -- not really. Spreadsheet -- yes. A good application for this.
>It would not likely be complicated, depending on how fancy you want it.
>
>Do you have a spreadsheet program (Excel, etc.)? Are you interested in
>delving a bit into spreadsheet design? If not, consider getting someone to
>do this for you if it's just the end result you're interested in.

I have Excel 2003 ... and I am interested in delving a bit into
spreadsheet design - in fact this application would be a good
opportunity to get into it.

I really need someone to point me in the right direction about the
methods needed. I know the basic principles of a spreadsheet - but how
would you do the programming to calculate the interpolated meter
readings from the actual ones?

I want to be able to tell the gas company that I used a certain volume
of gas in the 365 days up to a certain day.

So let's consider the following scenario.
There are readings for these days:

Day number Meter reading
3 4993
13 5003
..
<snip - lots of other readings>
..
374 37900
379 38400

Now then, let's say I want the consumption for the 365 days ending on
day number 375.

I need the meter readings for day 375 and for day 10.

I can estimate those readings quite accurately by interpolating between
days 374 and 379 ... to get a reading for day 375

and also between days 3 and 13 ... to get a reading for day 10.

That will give me a reading of 38000 for day 375
and a reading of 5000 for day 10.

So my usage for the 365 days ending on day 375 is 33000 cubic metres.

So my spreadsheet would probably have three columns:

DATE METER-READING USAGE-FOR-YEAR-UP-TO-THIS-DATE
--
Chris

(reply at end, since poster has established latest-post-last layout)

"Chris" <nospam@[127.0.0.1]> wrote in message
news:4LtTBLKMs2hDFwYz@[127.0.0.1]...
> In article <#[email protected]>, Earl Kiosterud
> <[email protected]> writes
>>"Chris" <nospam@[127.0.0.1]> wrote in message
>>news:Tcep0HHBUyhDFwdh@[127.0.0.1]...
>>> GAS meter spreadsheet
>>>
>>> I would be grateful if you could help me with this problem,
>>> perhaps by suggesting suitable freeware,
>>> or maybe by pointing me to sources of help.
>>>
>>> SITUATION: I take gas meter readings at random intervals;
>>> sometimes seven days might elapse between readings, sometimes seventeen.
>>> It could be any number of days between readings.
>>>
>>> REQUIREMENT: I want to know how much gas has been used in the year up to
>>> any day. The day might not be one on which I took a meter reading. Also,
>>> the day 365 days before that might also be one for which the meter was
>>> not
>>> read. I would assume linear interpolation between two adjacent readings.
>>>
>>> QUESTIONS: What would be the best way of doing this?
>>> Spreadsheet?
>>> Database?
>>> Python or similar?
>>> Anything else?
>>>
>>> I should point out that I am not experienced with any of the above,
>>> which means that I would have to learn from scratch whatever method is
>>> suggested!
>>>
>>> You might even know of the existence of something that will do it
>>> already?
>
>>Database -- not really. Spreadsheet -- yes. A good application for this.
>>It would not likely be complicated, depending on how fancy you want it.
>>
>>Do you have a spreadsheet program (Excel, etc.)? Are you interested in
>>delving a bit into spreadsheet design? If not, consider getting someone
>>to
>>do this for you if it's just the end result you're interested in.
>
> I have Excel 2003 ... and I am interested in delving a bit into
> spreadsheet design - in fact this application would be a good opportunity
> to get into it.
>
> I really need someone to point me in the right direction about the methods
> needed. I know the basic principles of a spreadsheet - but how would you
> do the programming to calculate the interpolated meter readings from the
> actual ones?
>
> I want to be able to tell the gas company that I used a certain volume of
> gas in the 365 days up to a certain day.
>
> So let's consider the following scenario.
> There are readings for these days:
>
> Day number Meter reading
> 3 4993
> 13 5003
> .
> <snip - lots of other readings>
> .
> 374 37900
> 379 38400
>
> Now then, let's say I want the consumption for the 365 days ending on day
> number 375.
>
> I need the meter readings for day 375 and for day 10.
>
> I can estimate those readings quite accurately by interpolating between
> days 374 and 379 ... to get a reading for day 375
>
> and also between days 3 and 13 ... to get a reading for day 10.
>
> That will give me a reading of 38000 for day 375
> and a reading of 5000 for day 10.
>
> So my usage for the 365 days ending on day 375 is 33000 cubic metres.
>
> So my spreadsheet would probably have three columns:
>
> DATE METER-READING USAGE-FOR-YEAR-UP-TO-THIS-DATE
> --
> Chris

Chris,

So we need something like this? (best viewed with a fixed font like
Courier)

Date Meter Usage
1
2
3 4993
4
5
6
7
8
9
10
11
12
13 5003
14
..
..
..
374 37900
375
376
377
378
379 38400

Is this the layout you want? If so, we'll put a formula in the Usage column
for each day (starting with day 365).

Earl Kiosterud
www.smokeylake.com

>(reply at end, since poster has established latest-post-last layout)
In article <[email protected]>, Earl Kiosterud
<[email protected]> writes
>"Chris" <nospam@[127.0.0.1]> wrote in message
>news:4LtTBLKMs2hDFwYz@[127.0.0.1]...
>> In article <#[email protected]>, Earl Kiosterud
>> <[email protected]> writes
>>>"Chris" <nospam@[127.0.0.1]> wrote in message
>>>news:Tcep0HHBUyhDFwdh@[127.0.0.1]...
>>>> GAS meter spreadsheet
>>>>
>>>> I would be grateful if you could help me with this problem,
>>>> perhaps by suggesting suitable freeware,
>>>> or maybe by pointing me to sources of help.
>>>>
>>>> SITUATION: I take gas meter readings at random intervals;
>>>> sometimes seven days might elapse between readings, sometimes seventeen.
>>>> It could be any number of days between readings.
>>>>
>>>> REQUIREMENT: I want to know how much gas has been used in the year up to
>>>> any day. The day might not be one on which I took a meter reading. Also,
>>>> the day 365 days before that might also be one for which the meter was
>>>> not
>>>> read. I would assume linear interpolation between two adjacent readings.
>>>>
>>>> QUESTIONS: What would be the best way of doing this?
>>>> Spreadsheet?
>>>> Database?
>>>> Python or similar?
>>>> Anything else?
>>>>
>>>> I should point out that I am not experienced with any of the above,
>>>> which means that I would have to learn from scratch whatever method is
>>>> suggested!
>>>>
>>>> You might even know of the existence of something that will do it
>>>> already?
>>
>>>Database -- not really. Spreadsheet -- yes. A good application for this.
>>>It would not likely be complicated, depending on how fancy you want it.
>>>
>>>Do you have a spreadsheet program (Excel, etc.)? Are you interested in
>>>delving a bit into spreadsheet design? If not, consider getting someone
>>>to
>>>do this for you if it's just the end result you're interested in.
>>
>> I have Excel 2003 ... and I am interested in delving a bit into
>> spreadsheet design - in fact this application would be a good opportunity
>> to get into it.
>>
>> I really need someone to point me in the right direction about the methods
>> needed. I know the basic principles of a spreadsheet - but how would you
>> do the programming to calculate the interpolated meter readings from the
>> actual ones?
>>
>> I want to be able to tell the gas company that I used a certain volume of
>> gas in the 365 days up to a certain day.
>>
>> So let's consider the following scenario.
>> There are readings for these days:
>>
>> Day number Meter reading
>> 3 4993
>> 13 5003
>> .
>> <snip - lots of other readings>
>> .
>> 374 37900
>> 379 38400
>>
>> Now then, let's say I want the consumption for the 365 days ending on day
>> number 375.
>>
>> I need the meter readings for day 375 and for day 10.
>>
>> I can estimate those readings quite accurately by interpolating between
>> days 374 and 379 ... to get a reading for day 375
>>
>> and also between days 3 and 13 ... to get a reading for day 10.
>>
>> That will give me a reading of 38000 for day 375
>> and a reading of 5000 for day 10.
>>
>> So my usage for the 365 days ending on day 375 is 33000 cubic metres.
>>
>> So my spreadsheet would probably have three columns:
>>
>> DATE METER-READING USAGE-FOR-YEAR-UP-TO-THIS-DATE
>> --
>> Chris
>
>Chris,
>
>So we need something like this? (best viewed with a fixed font like
>Courier)
>
>Date Meter Usage
>1
>2
>3 4993
>4
>5
>6
>7
>8
>9
>10
>11
>12
>13 5003
>14
>.
>.
>.
>374 37900
>375
>376
>377
>378
>379 38400
>
>Is this the layout you want? If so, we'll put a formula in the Usage column
>for each day (starting with day 365).
>
>Earl Kiosterud
>www.smokeylake.com

Yes - that's exactly what I want.
Thanks for your reply.
Also ... thanks for your courtesy in responding to my bottom posting.
Such courtesy on Usenet is rare - and much appreciated.
So - what is the next step for the spreadsheet?
--
Chris

Hello,
If you can assume a relatively constant usage then you can use the linest
=LINEST(B5:B35,A5:A35,TRUE,FALSE) where B are the gas readings, A the day number for the year,
and the last two values related to where the line intercept should be, and the statistics. In this case the intercept
should be 0, but......

I guess that if you are relating to cooking gas the linear assumption might hold, but you wouldn't want to model use
for heating fuel that way. Of course a simpler approach may be sufficient for your needs. One involving simple
linear interpolation between known points. It isn't very accurate perhaps but the mistakes would even out.

Hope that helps





----- Original Message -----
>(reply at end, since poster has established latest-post-last layout)
In article <[email protected]>, Earl Kiosterud
<[email protected]> writes
>"Chris" <nospam@[127.0.0.1]> wrote in message
>news:4LtTBLKMs2hDFwYz@[127.0.0.1]...
>> In article <#[email protected]>, Earl Kiosterud
>> <[email protected]> writes
>>>"Chris" <nospam@[127.0.0.1]> wrote in message
>>>news:Tcep0HHBUyhDFwdh@[127.0.0.1]...
>>>> GAS meter spreadsheet
>>>>
>>>> I would be grateful if you could help me with this problem,
>>>> perhaps by suggesting suitable freeware,
>>>> or maybe by pointing me to sources of help.
>>>>
>>>> SITUATION: I take gas meter readings at random intervals;
>>>> sometimes seven days might elapse between readings, sometimes seventeen.
>>>> It could be any number of days between readings.
>>>>
>>>> REQUIREMENT: I want to know how much gas has been used in the year up to
>>>> any day. The day might not be one on which I took a meter reading. Also,
>>>> the day 365 days before that might also be one for which the meter was
>>>> not
>>>> read. I would assume linear interpolation between two adjacent readings.
>>>>
>>>> QUESTIONS: What would be the best way of doing this?
>>>> Spreadsheet?
>>>> Database?
>>>> Python or similar?
>>>> Anything else?
>>>>
>>>> I should point out that I am not experienced with any of the above,
>>>> which means that I would have to learn from scratch whatever method is
>>>> suggested!
>>>>
>>>> You might even know of the existence of something that will do it
>>>> already?
>>
>>>Database -- not really. Spreadsheet -- yes. A good application for this.
>>>It would not likely be complicated, depending on how fancy you want it.
>>>
>>>Do you have a spreadsheet program (Excel, etc.)? Are you interested in
>>>delving a bit into spreadsheet design? If not, consider getting someone
>>>to
>>>do this for you if it's just the end result you're interested in.
>>
>> I have Excel 2003 ... and I am interested in delving a bit into
>> spreadsheet design - in fact this application would be a good opportunity
>> to get into it.
>>
>> I really need someone to point me in the right direction about the methods
>> needed. I know the basic principles of a spreadsheet - but how would you
>> do the programming to calculate the interpolated meter readings from the
>> actual ones?
>>
>> I want to be able to tell the gas company that I used a certain volume of
>> gas in the 365 days up to a certain day.
>>
>> So let's consider the following scenario.
>> There are readings for these days:
>>
>> Day number Meter reading
>> 3 4993
>> 13 5003
>> .
>> <snip - lots of other readings>
>> .
>> 374 37900
>> 379 38400
>>
>> Now then, let's say I want the consumption for the 365 days ending on day
>> number 375.
>>
>> I need the meter readings for day 375 and for day 10.
>>
>> I can estimate those readings quite accurately by interpolating between
>> days 374 and 379 ... to get a reading for day 375
>>
>> and also between days 3 and 13 ... to get a reading for day 10.
>>
>> That will give me a reading of 38000 for day 375
>> and a reading of 5000 for day 10.
>>
>> So my usage for the 365 days ending on day 375 is 33000 cubic metres.
>>
>> So my spreadsheet would probably have three columns:
>>
>> DATE METER-READING USAGE-FOR-YEAR-UP-TO-THIS-DATE
>> --
>> Chris
>
>Chris,
>
>So we need something like this? (best viewed with a fixed font like
>Courier)
>
>Date Meter Usage
>1
>2
>3 4993
>4
>5
>6
>7
>8
>9
>10
>11
>12
>13 5003
>14
>.
>.
>.
>374 37900
>375
>376
>377
>378
>379 38400
>
>Is this the layout you want? If so, we'll put a formula in the Usage column
>for each day (starting with day 365).
>
>Earl Kiosterud
>www.smokeylake.com

Yes - that's exactly what I want.
Thanks for your reply.
Also ... thanks for your courtesy in responding to my bottom posting.
Such courtesy on Usenet is rare - and much appreciated.
So - what is the next step for the spreadsheet?
--
Chris