Many common software questions are answered in the categories below. If you can’t find a response for yours, try checking our User Forum, or, contact HelpDesk for direct assistance
| Metrix » Troubleshooting Your Project |
Q: Excel tools to troubleshoot your project
A: Project files for big jobs, can grow to enormous size. Everyday we assist energy managers who oversee 300+ room hotels, 20 campus school districts, 100-200 meter projects and their results need to be error free. How on earth can one person keep so many details straight?
They work smarter, not harder! Any database that is larger than 1 scroll screen is more than you should be inspecting with your eyes! Energy managers are database managers (whether they recognize it or not). Gaining proficiency with Excel and other database tools, will reward you every day of your professional life!
Excel functions ALL Metrix users should be familiar with:
1) Text to Columns Function - Metrix imports and exports in .csv (comma separated variable) format. This is a universal database vernacular, but hard on the eyes. https://www.abraxasenergy.com/faq.php?cid=26&fid=274
2) Auto Filter - sort/filter rows of a column (and those adjacent). Highlight your data. Tip: single left click a cell then ctrl + shift + an arrow key to snap the cursor to the end of row/column or next blank cell. Once highlighted go to Data > Filter > Auto Filter. A Box with a down arrow will appear. click the box to filter elements of same kind or sort acsending/descending.
Try this: on an MT1, go to the number of days column and sort descending. If you see a jump from 31 to 62, there's a good chance there is a missing bill. That could account for a suspicious baseline value. A 365 day bill probably has a typo in the year. Sort ascending as well; a 0 day bill could mean a duplicate bill.
3) Find/Replace function - let Excel find the needle in the haystack. Edit > Find. Most people know this from error checking in Word, but not everyone thinks to use it in Excel.
Try this: export a weather file, search for "-", Christmas in July?
4) IF statements - this is much more advanced. (Excel has lots of good help files on this)
Try this: find a double bill. Sort all the bills in ascending order of cost (Col L). Create a colum adjacent to the cost column (new blank Col M). In the first row Type: =if(L1=L2, "double","") Then drag it down the entire column. If 2 bills (in col L) are identical, a cell in adjacent Col M will say "double". It is highly improbable that 2 months are identical down to the cent, perhaps this is a data entry error. Very cool.
Try this: find an absurd cost. Let's say $10,000 is definitely too much for the meter in question, but Report Maker says this what the baseline should be. There is no rate information entered, so the baseline is averaged from historical costs; the problem must be in the bill register. Create a new column adjacent to the cost column. Type: =if(L1>10,000,"too high","") then drag it down the entire column.
A: Project files for big jobs, can grow to enormous size. Everyday we assist energy managers who oversee 300+ room hotels, 20 campus school districts, 100-200 meter projects and their results need to be error free. How on earth can one person keep so many details straight?
They work smarter, not harder! Any database that is larger than 1 scroll screen is more than you should be inspecting with your eyes! Energy managers are database managers (whether they recognize it or not). Gaining proficiency with Excel and other database tools, will reward you every day of your professional life!
Excel functions ALL Metrix users should be familiar with:
1) Text to Columns Function - Metrix imports and exports in .csv (comma separated variable) format. This is a universal database vernacular, but hard on the eyes. https://www.abraxasenergy.com/faq.php?cid=26&fid=274
2) Auto Filter - sort/filter rows of a column (and those adjacent). Highlight your data. Tip: single left click a cell then ctrl + shift + an arrow key to snap the cursor to the end of row/column or next blank cell. Once highlighted go to Data > Filter > Auto Filter. A Box with a down arrow will appear. click the box to filter elements of same kind or sort acsending/descending.
Try this: on an MT1, go to the number of days column and sort descending. If you see a jump from 31 to 62, there's a good chance there is a missing bill. That could account for a suspicious baseline value. A 365 day bill probably has a typo in the year. Sort ascending as well; a 0 day bill could mean a duplicate bill.
3) Find/Replace function - let Excel find the needle in the haystack. Edit > Find. Most people know this from error checking in Word, but not everyone thinks to use it in Excel.
Try this: export a weather file, search for "-", Christmas in July?
4) IF statements - this is much more advanced. (Excel has lots of good help files on this)
Try this: find a double bill. Sort all the bills in ascending order of cost (Col L). Create a colum adjacent to the cost column (new blank Col M). In the first row Type: =if(L1=L2, "double","") Then drag it down the entire column. If 2 bills (in col L) are identical, a cell in adjacent Col M will say "double". It is highly improbable that 2 months are identical down to the cent, perhaps this is a data entry error. Very cool.
Try this: find an absurd cost. Let's say $10,000 is definitely too much for the meter in question, but Report Maker says this what the baseline should be. There is no rate information entered, so the baseline is averaged from historical costs; the problem must be in the bill register. Create a new column adjacent to the cost column. Type: =if(L1>10,000,"too high","") then drag it down the entire column.
I can’t find the answer to your question ? Try checking our User Forums.
Otherwise feel free to contact our Tech Support staff at (805) 329-6565, or via email at helpdesk@abraxasenergy.com.