Software for energy management...
Degree-day data collection
Subscribers to degree-day services cab use the DDTABLE15.XLS workbook to tabulate their weekly or monthly data. The workbook automatically collects new data from the website as it becomes available and appends it to a table for each region that you subscribe to. Click here to get a copy: but save it to disc and load it from Excel rather than trying to run it direct from your browser. You will need to know your usercode to activate it.DDTABLE15.XLS is a recommended upgrade if you have been using DDTABLE11.XLS or a derivative and you are having problems after upgrading to Excel 2007.
Consolidate fragmented data histories
Lots of people keep their energy data in a separate workbook for each accounting year, which is fine for annual reporting but a real problem if you want to do a longer-term trend analysis. Even if you keep everything in a single sheet but insert summary lines after each twelve months, you still prevent continuous analysis.The Excel INDIRECT() command allows you to create a table of data assembled from various sources. You could tabulate the following information to identify an individual item of source data:
- workbook name
- sheet name
- column
- row
==========================================
A B C D
=======================================
1 | Workbook Sheet Row Column
2 |-------------- ------ ----- -----
3 |Book_A.XLS Sheet1 12 C
4 |Book_A.XLS Sheet1 13 C
5 |Book_A.XLS Sheet1 14 C
6 |Book_A.XLS Sheet1 15 C
7 |Book_B.XLS Sheet2 1 A
8 |Book_B.XLS Sheet2 2 A
9 |Book_B.XLS Sheet2 3 A
10 |Book_B.XLS Sheet2 4 A
In another column you would then put this formula in row 3 (and copy it down):
=INDIRECT("'["&$A3&"]"&$B3&"'!"&D3&TEXT($E3,"#0"))
So, for example, the formula in row 6 evaluates to:
=INDIRECT('[Book_A.XLS]Sheet1'!C15)
... which returns the value held at that location.
Subscribers to our email update service
have received a ready-made workbook that implements this principle, and can be use
immediately just by altering the table entries to point to their own data.
Sankey diagram softwareThe product recommended by one of our readers in October has been upgraded: click here for feedback from the user and news from the supplier.(V.V. 2 Dec 05) |
|
|
Excel tip: self-extending tabulations. If you have a table to which you add new rows of data from time to time, you will probably have to remember to copy down any formulae in other columns of the table. This macro does it for you automatically. Whenever you enter a new row of data to a table, all formula columns will immediately extend themselves down to the row you just added. Read article which includes working example to download. V.V. 25/11/05
Previously-featured tips: |
Units conversionI noticed this rather neat units-conversion program on a someone's PC; download free from joshmadison.com (V.V. 14/10/05) |
|