For one year I've been chasing a problem with one of the spreadsheets we use at work. The author of the spreadsheet retired 7 months before I was transferred back to the plant and was/is not available to ask how he arrived at some of his data queries. So I've had to try and think like him to come up with some of the logic behind his process. Just getting my mind set to his thought process is enough to get my head hurting. I'm fortunate to have worked with him while he was developing the initial spreadsheet so I'm familiar with his original plan. The biggest problem comes with the fact he was building this spreadsheet as he learned Excel. As he would learn how to massage a data stream to get what he needed he would use the process to update what he had already done. In some cases he has the data spread over 6 different sheets and they are all linked back to the first sheet. This wouldn't be too bad but he has incorporated another data intensive program on multiple sheets to upload the needed data. It all made perfect sense to him and sense he WAS the only one maintaining the spreadsheet there was no real issue since all anyone else needed to do was enter hourly figures in the right box if the spreadsheet should happen to not collect the data. And there is where our problem lies, the spreadsheet is NOT talking with the other program to get the correct data.
So for right at a year now I have been using my spare time at work to see if I could locate the issue. We are getting data feeds but they are not correct, sometimes the data could be off by 200 to 6700 mmBTU's.
Last week, the junior member of my crew was noticing I was working on the spreadsheet and decided he needed to get a little training on it. So I was showing him what I was trying to do. We went along and he started asking questions and I was answering them. As I was explaining what I was trying to do he noticed an odd equation and asked what this does. So I told him we take the MCF of the natural gas we burn, multiply it by the BTU content to arrive at the actual MMBTU of the fuel. It was at this point that it finally sunk in, our control system was doing this for us yet our indications had not been changed from MCF to MMBTU and we were in turn doing it again in the spreadsheet. Now all I had to do was correct the formula used to arrive at the hourly burn rate and the remaining hourly rate to fulfill our hourly and daily contractual burns.
Of course after I finally edited the formulas I had to tell Caleb that he was fired because he let me slave over this for a year before he decided he wanted to learn more about the work behind the scene. He did make a good case to save his job and I let him stay. He pointed out he had brought lunch and if he left he was taking it with him. . . So I let him stay but told him not to do it again.
So for right at a year now I have been using my spare time at work to see if I could locate the issue. We are getting data feeds but they are not correct, sometimes the data could be off by 200 to 6700 mmBTU's.
Last week, the junior member of my crew was noticing I was working on the spreadsheet and decided he needed to get a little training on it. So I was showing him what I was trying to do. We went along and he started asking questions and I was answering them. As I was explaining what I was trying to do he noticed an odd equation and asked what this does. So I told him we take the MCF of the natural gas we burn, multiply it by the BTU content to arrive at the actual MMBTU of the fuel. It was at this point that it finally sunk in, our control system was doing this for us yet our indications had not been changed from MCF to MMBTU and we were in turn doing it again in the spreadsheet. Now all I had to do was correct the formula used to arrive at the hourly burn rate and the remaining hourly rate to fulfill our hourly and daily contractual burns.
Of course after I finally edited the formulas I had to tell Caleb that he was fired because he let me slave over this for a year before he decided he wanted to learn more about the work behind the scene. He did make a good case to save his job and I let him stay. He pointed out he had brought lunch and if he left he was taking it with him. . . So I let him stay but told him not to do it again.
