| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Gas Temperature correction factors in an excel spreadsheet.
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Gas Temperature correction factors in an excel spreadsheet.
hi Greg
With meter reading in A1 and Temperature in B1 enter in C1 =A1+A1*ABS(A2-65)*0.002488*(SIGN(A2-65)) -- Regards Roger Govier "greg" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Gas Temperature correction factors in an excel spreadsheet.
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. -- Gary''s Student - gsnu200830 "greg" wrote: 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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Gas Temperature correction factors in an excel spreadsheet.
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. -- David Biddulph "greg" wrote in message ... 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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Gas Temperature correction factors in an excel spreadsheet.
With meter reading in A1 and Temperature in B1 enter in C1
=A1+A1*ABS(A2-65)*0.002488*(SIGN(A2-65)) Unless I am missing something, I think your formula is equivalent to this... =A2+A2*(A3-65)*0.002488 -- Rick (MVP - Excel) "Roger Govier" <roger@technology4unospamdotcodotuk wrote in message ... hi Greg With meter reading in A1 and Temperature in B1 enter in C1 =A1+A1*ABS(A2-65)*0.002488*(SIGN(A2-65)) -- Regards Roger Govier "greg" wrote in message ... 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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Gas Temperature correction factors in an excel spreadsheet.
Thanks, I have got it now but thanks for looking.
greg "David Biddulph" wrote: 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. -- David Biddulph "greg" wrote in message ... 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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Gas Temperature correction factors in an excel spreadsheet.
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 "Gary''s Student" wrote: 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. -- Gary''s Student - gsnu200830 "greg" wrote: 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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Gas Temperature correction factors in an excel spreadsheet.
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 "Rick Rothstein" wrote: With meter reading in A1 and Temperature in B1 enter in C1 =A1+A1*ABS(A2-65)*0.002488*(SIGN(A2-65)) Unless I am missing something, I think your formula is equivalent to this... =A2+A2*(A3-65)*0.002488 -- Rick (MVP - Excel) "Roger Govier" <roger@technology4unospamdotcodotuk wrote in message ... hi Greg With meter reading in A1 and Temperature in B1 enter in C1 =A1+A1*ABS(A2-65)*0.002488*(SIGN(A2-65)) -- Regards Roger Govier "greg" wrote in message ... 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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Gas Temperature correction factors in an excel spreadsheet.
Thanks for looking and your input. I have it now, a little different but it
works. Greg "Roger Govier" wrote: hi Greg With meter reading in A1 and Temperature in B1 enter in C1 =A1+A1*ABS(A2-65)*0.002488*(SIGN(A2-65)) -- Regards Roger Govier "greg" wrote in message ... 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 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Gas Temperature correction factors in an excel spreadsheet.
Yes, but I like to make things complicated, Rick<bg
Well spotted. -- Regards Roger Govier "Rick Rothstein" wrote in message ... With meter reading in A1 and Temperature in B1 enter in C1 =A1+A1*ABS(A2-65)*0.002488*(SIGN(A2-65)) Unless I am missing something, I think your formula is equivalent to this... =A2+A2*(A3-65)*0.002488 -- Rick (MVP - Excel) "Roger Govier" <roger@technology4unospamdotcodotuk wrote in message ... hi Greg With meter reading in A1 and Temperature in B1 enter in C1 =A1+A1*ABS(A2-65)*0.002488*(SIGN(A2-65)) -- Regards Roger Govier "greg" wrote in message ... 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 |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Gas Temperature correction factors in an excel spreadsheet.
Yes, but I like to make things complicated, Rick<bg
LOL -- Rick (MVP - Excel) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Temperature Formula HELP!!! | Excel Worksheet Functions | |||
| HOW DO I USE EXCEL FOR QUALITY CONTROL CHART SUCH AS TEMPERATURE . | Charts and Charting in Excel | |||
| Can Excel reveal factors for success? | Excel Discussion (Misc queries) | |||
| Excel - Discount Factors and NOV | Excel Worksheet Functions | |||
| Line charts using temperature and time factors | Charts and Charting in Excel | |||
Linear Mode
