XLS Spreadsheet

NEPAForum Admin wrote:How do gas bills come? By therms or cubic feet? Or both?

In my area total therms, cents per therm, fixed flat rate for distribution costs, total dollars.

Yanche

Ok, I have a simple working calculator like the other ones you have seen: [dead link removed]

I'll be adding the other fuels that are in the spreadsheet shortly and continue adding other things as I get to it. This is really just a first installment for testing purposes.

I'd appreciate if you can test it out, make sure it's coming up with the correct figures. On a side note testing it against the one on hearthnet.com it appears the one on herthnet is incorrect.... the figures my calculator are coming up with match the ones on the excel spreadsheet, the one on hearthnet doesn't match.

Yanche wrote:If you decide to add an on-line version of the DoE calculator be sure to look at the spread sheet cell formulas to understand how the various inputs are used. The most difficult one to put on line would be the heat pump, especially if you include the local weather conditions.

Could you post them here, I seem unable to extract them. Math is not my strongest point but adding them to my script should be quite simple, again I'm very unfamiliar with Excel spreadsheets.

NEPAForum Admin wrote:

Yanche wrote:If you decide to add an on-line version of the DoE calculator be sure to look at the spread sheet cell formulas to understand how the various inputs are used. The most difficult one to put on line would be the heat pump, especially if you include the local weather conditions.

Could you post them here, I seem unable to extract them. Math is not my strongest point but adding them to my script should be quite simple, again I'm very unfamiliar with Excel spreadsheets.

Do you have Excel on your computer? If yes, open the DoE file with Excel. With the mouse move to any cell and left button click. The cell will be highlighted and the contents of that cell will be displayed just above the top of the spreadsheet. This is called the formula bar. It will have the row column identifier something like E7, i.e. column E row 7. If it is a formula the value of the cell will start with an equal sign. For example =D7/C7. That means the cell E7 will have a value calculated by taking the value in cell D7 and dividing it by the value in cell C7.

You will need to traverse all the cells in the spreadsheet and determine what calculations are being done and how they relate to data from other cells. One a particular cell is selected and highlighted you can move around with the keyboard arrow keys easily and watch the formula bar change.

Hope this helps and if you already know this I don't mean to insult you, I just don't know where to start. The math operators used are the usual ones, *,/,+,- with parenthesis used for grouping. Just like any of the simple math calculators that are programmable. If you get stuck the help file would be the first place to look.

If you don't have Excel on your computer I'll have to think on how best to help you.

Yanche

Yanche wrote: ..the contents of that cell will be displayed just above the top of the spreadsheet.

I downloaded openoffice, thanks that's just what I needed. As long as I can see what calculations they are using I can easily transfer it. The code for my PHP script wouldn't be much different than what you find in an excel spreadsheet, the difference is I can display the results in a more presentable manner at least IMO.

Basically this is it, pretty basic:

Code: Select all

<?php
// this determines if the form has been submitted
// if so those values are used
if(isset($_POST['calculate'])) {
    $cost_coal1 = $_POST['cost_coal1'];
   $cost_bagcoal1 = $_POST['cost_bagcoal1'];
   $cost_oil1 = $_POST['cost_oil1'];
   $cost_gas1 = $_POST['cost_gas1'];
   $cost_propane1 = $_POST['cost_propane1'];
   $cost_wood1 = $_POST['cost_wood1'];
   $cost_elec1 = $_POST['cost_elec1'];
   $cost_pellet1 = $_POST['cost_pellet1'];
   $cost_kero1 = $_POST['cost_kero1'];   
    $eff_coal = $_POST['eff_coal'];
   $eff_bagcoal = $_POST['eff_bagcoal'];
   $eff_oil = $_POST['eff_oil'];
   $eff_gas = $_POST['eff_gas'];
   $eff_propane = $_POST['eff_propane'];
   $eff_wood = $_POST['eff_wood'];
   $eff_elec = $_POST['eff_elec'];
   $eff_pellet = $_POST['eff_pellet'];
   $eff_kero = $_POST['eff_kero'];   
} else {
// if the page has been laoded fresh these are the default values
    $cost_coal1 = "130.00";
   $cost_bagcoal1 = "5.00";
   $cost_oil1 = "2.50";
   $cost_gas1 = "1.55";
   $cost_propane1 = "2.00";
   $cost_wood1 = "190.00";
   $cost_elec1 = "0.11";
   $cost_pellet1 = "190.00";
   $cost_kero1 = "2.87";
    $eff_coal = "75";
   $eff_bagcoal = "75";
   $eff_oil = "78";
   $eff_gas = "78";
   $eff_propane = "78";
   $eff_wood = "60";
   $eff_elec = "99";
   $eff_pellet = "80";
   $eff_kero = "80";   
}
// the calculations
$total_coal = $cost_coal1/(24916000 * ($eff_coal/100))*1000000;
$total_bagcoal = $cost_bagcoal1/(498320 * ($eff_bagcoal/100))*1000000;
$total_oil = $cost_oil1/(138690 * ($eff_oil/100))*1000000;
$total_gas = $cost_gas1/(100000 * ($eff_gas/100))*1000000;
$total_propane = $cost_propane1/(91333 * ($eff_propane/100))*1000000;
$total_wood = $cost_wood1/(20000000 * ($eff_wood/100))*1000000;
$total_elec = $cost_elec1/(3412 * ($eff_elec/100))*1000000;
$total_pellet = $cost_pellet1/(16500000 * ($eff_pellet/100))*1000000;
$total_kero = $cost_kero1/(135000 * ($eff_kero/100))*1000000;
$total_coal2 = $total_coal * 100;
$total_bagcoal2 = $total_bagcoal * 100;
$total_oil2 = $total_oil * 100;
$total_gas2 = $total_gas * 100;
$total_propane2 = $total_propane * 100;
$total_wood2 = $total_wood * 100;
$total_elec2 = $total_elec * 100;
$total_pellet2 = $total_pellet * 100;
$total_kero2 = $total_kero * 100;
?>
// the HTML
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html>
<head>
  <title>Fuel Comparison Calculator</title>
<style type="text/css">
input
{
text-align: right;
}
.money
{
background-image: url(dollar_sign.gif);
background-repeat: no-repeat;
padding-left: 40px;
}
.efficiency
{
text-align: center;
}
table
{
background-color: #D1D2C8;
border: 0px;
}
td
{
border-top: 1px #FFFFFF solid;
border-left: 1px #FFFFFF solid;
border-right: 1px #000000 solid;
border-bottom: 1px #000000 solid;
padding: 5px;
}
th
{
border-top: 1px #FFFFFF solid;
border-left: 1px #FFFFFF solid;
border-right: 1px #000000 solid;
border-bottom: 2px #000000 solid;
padding: 5px;
}
</style>
</head>
<body>
<p>NOTE: This is an experimental calculator with frequent changes to the code and may not rteturn valid figures.</p>

<form action="<?php echo $PHP_SELF; ?>" method="post">
<table cellspacing="0">
  <tr>
     <th>Fuel Type</th>
     <th>Fuel Unit Cost</th>
    <th>Fuel Unit Size (1)</th>
     <th>Efficiency of Heating Unit</th>
    <th>Price per Million BTU</th>
    <th>To Heat Average Home</th>
  </tr>
  <tr>
     <td>Coal</td>
     <td class="money"><input type="text" name="cost_coal1" value="<?php echo  $cost_coal1 ?>" size="4">
    <hr />
    <input type="text" name="cost_bagcoal1" value="<?php echo  $cost_bagcoal1 ?>" size="4">
    </td>
    <td>Ton<hr />
    40lb. Bag</td>
     <td><input class="efficiency" type="text" name="eff_coal" value="<?php echo  $eff_coal ?>" size="1">%<hr />
    <input class="efficiency" type="text" name="eff_bagcoal" value="<?php echo  $eff_bagcoal ?>" size="1">%
    </td>
    <td class="money"><?php printf ("%0.2f",$total_coal); ?><hr />
    <?php printf ("%0.2f",$total_bagcoal); ?>
    </td>
    <td class="money"><?php printf ("%0.2f",$total_coal2); ?><hr />
    <?php printf ("%0.2f",$total_bagcoal2); ?>
    </td>
  </tr>
  <tr>
     <td>Fuel Oil (No.2)</td>
     <td class="money"><input type="text" name="cost_oil1" value="<?php echo  $cost_oil1 ?>" size="4">
    </td>
    <td>Gallon</td>
     <td><input class="efficiency" type="text" name="eff_oil" value="<?php echo  $eff_oil ?>" size="1">%</td>
    <td class="money"><?php printf ("%0.2f",$total_oil); ?></td>
    <td class="money"><?php printf ("%0.2f",$total_oil2); ?></td>
  </tr>
  <tr>
     <td>Natural Gas</td> 
     <td class="money"><input type="text" name="cost_gas1" value="<?php echo  $cost_gas1 ?>" size="4">
    </td>
    <td>Therm</td>
     <td><input class="efficiency" type="text" name="eff_gas" value="<?php echo  $eff_gas ?>" size="1">%</td>
    <td class="money"><?php printf ("%0.2f",$total_gas); ?></td>
    <td class="money"><?php printf ("%0.2f",$total_gas2); ?></td>
  </tr>
  <tr>
     <td>Propane</td>
     <td class="money"><input type="text" name="cost_propane1" value="<?php echo  $cost_propane1 ?>" size="4">
    </td>
    <td>Gallon</td>
     <td><input class="efficiency" type="text" name="eff_propane" value="<?php echo  $eff_propane ?>" size="1">%</td>
    <td class="money"><?php printf ("%0.2f",$total_propane); ?></td>
    <td class="money"><?php printf ("%0.2f",$total_propane2); ?></td>
  </tr>
  <tr>
     <td>Wood</td>
     <td class="money"><input type="text" name="cost_wood1" value="<?php echo  $cost_wood1 ?>" size="4">
    </td>
    <td>Cord</td>
     <td><input class="efficiency" type="text" name="eff_wood" value="<?php echo  $eff_wood ?>" size="1">%</td>
    <td class="money"><?php printf ("%0.2f",$total_wood); ?></td>
    <td class="money"><?php printf ("%0.2f",$total_wood2); ?></td>
  </tr>
  <tr>
     <td>Electricity</td>
     <td class="money"><input type="text" name="cost_elec1" value="<?php echo  $cost_elec1 ?>" size="4">
    </td>
    <td>kWh</td>
     <td><input class="efficiency" type="text" name="eff_elec" value="<?php echo  $eff_elec ?>" size="1">%</td>
    <td class="money"><?php printf ("%0.2f",$total_elec); ?></td>
    <td class="money"><?php printf ("%0.2f",$total_elec2); ?></td>
   
  </tr>
  <tr>
     <td>Wood Pellets</td>
     <td class="money"><input type="text" name="cost_pellet1" value="<?php echo  $cost_pellet1 ?>" size="4">
    </td>
    <td>Ton</td>
     <td><input class="efficiency" type="text" name="eff_pellet" value="<?php echo  $eff_pellet ?>" size="1">%</td>
    <td class="money"><?php printf ("%0.2f",$total_pellet); ?></td>
    <td class="money"><?php printf ("%0.2f",$total_pellet2); ?></td>
  </tr>
  <tr>
     <td>Kerosene</td>
     <td class="money"><input type="text" name="cost_kero1" value="<?php echo  $cost_kero1 ?>" size="4">
    </td>
    <td>Gallon</td>
     <td><input class="efficiency" type="text" name="eff_kero" value="<?php echo  $eff_kero ?>" size="1">%</td>
    <td class="money"><?php printf ("%0.2f",$total_kero); ?></td>
    <td class="money"><?php printf ("%0.2f",$total_kero2); ?></td>
  </tr>
</table>
<input type="submit" name="calculate" value="Recalculate">
</form>
<form method="link" action="<?php echo $PHP_SELF; ?>">
<input type="submit" value="Reset">
</form>
</body>
</html>