Gas Temperature correction factors in an excel spreadsheet.

G

greg

I am working on a spreadsheet for calculating natural gas usage. Our meters
aren't temperature adjusted so I need to incorporate a calculation in our
spreadsheet that takes the temperature we input and adjusts our gas usage
accordingly.
The temp. factor sheet I have goes from -20 degrees to 139 degrees. I am
mainly concerned with 30 degrees (meter reading*1.0612) to 110 degrees (meter
reading*.9123), 60 degrees being meter reading * 1. The factor increase from
30 to 59 is .002488 per degree (60 is not corrected) and then at 61 degrees
and above the factor decreases by the same .002488.
Any help or suggestions would be greatly appreciated.
Thank You,
Greg
 
R

Roger Govier

hi Greg

With meter reading in A1 and Temperature in B1 enter in C1
=A1+A1*ABS(A2-65)*0.002488*(SIGN(A2-65))
 
G

Gary''s Student

Say we first construct a temperature table from H1 to I82 that looks like:

30 1.0612
31 1.059361728
32 1.057523457
33 1.055685185
34 1.053846914
35 1.052008642
36 1.05017037
37 1.048332099
38 1.046493827
39 1.044655556
40 1.042817284
41 1.040979012
42 1.039140741
43 1.037302469
44 1.035464198
45 1.033625926
46 1.031787654
47 1.029949383
48 1.028111111
49 1.02627284
50 1.024434568
51 1.022596296
52 1.020758025
53 1.018919753
54 1.017081481
55 1.01524321
56 1.013404938
57 1.011566667
58 1.009728395
59 1.007890123
60 1.006051852
61 1.00421358
62 1.002375309
63 1.000537037
64 0.998698765
65 0.996860494
66 0.995022222
67 0.993183951
68 0.991345679
69 0.989507407
70 0.987669136
71 0.985830864
72 0.983992593
73 0.982154321
74 0.980316049
75 0.978477778
76 0.976639506
77 0.974801235
78 0.972962963
79 0.971124691
80 0.96928642
81 0.967448148
82 0.965609877
83 0.963771605
84 0.961933333
85 0.960095062
86 0.95825679
87 0.956418519
88 0.954580247
89 0.952741975
90 0.950903704
91 0.949065432
92 0.94722716
93 0.945388889
94 0.943550617
95 0.941712346
96 0.939874074
97 0.938035802
98 0.936197531
99 0.934359259
100 0.932520988
101 0.930682716
102 0.928844444
103 0.927006173
104 0.925167901
105 0.92332963
106 0.921491358
107 0.919653086
108 0.917814815
109 0.915976543
110 0.914138272
111 0.9123

You would use the exact values rather than the estimated ones I used.

Put your meter reading in column A and the temperature in column B and in
column C, we enter:

=A1*VLOOKUP(B1,$H$1:$I$82,2) to give the adjusted meter readings.
 
D

David Biddulph

Your figures are not consistent.
..002488 per degree does not give 1.0612 at 30 degrees, nor does it give
0.9123 at 110 degrees.
You need to recheck your calculations.
 
R

Rick Rothstein

With meter reading in A1 and Temperature in B1 enter in C1
=A1+A1*ABS(A2-65)*0.002488*(SIGN(A2-65))
Click to expand...

Unless I am missing something, I think your formula is equivalent to this...

=A2+A2*(A3-65)*0.002488
 
G

greg

Yeah! This worked for me. I haven't done the tables and I have never used
vlookup before. I will look more into it now that you showed me. Thanks,
Greg
 
G

greg

Thanks for looking. I found one that worked like I needed it to, maybe a
little more involved but good non the less. Thanks for your input and time.
Greg
 
G

greg

Thanks for looking and your input. I have it now, a little different but it
works.
Greg
 
Top