Rook-Knight
New Member
- Joined
- Jul 16, 2012
- Messages
- 5
Hi,
I have a data set that consists of 17,280 numbers (values pulled every 5 seconds over a 24 hr period for a water level). The purpose is to monitor the efficiency of a pump. So, the water level begins accumulating (after a pump cycle) and it is 2.0, 2.05, 2.07... all the way to about 4.0, where the pump is triggered to start pumping and pump the water level back down to 2 feet, which is when it will shut off and water will begin accumulating again. This cylces through roughly once per hour (24 cycles).
A perfect formula would be one which works like this: "Begin counting once a cell value is less than the value immediately preceeding it, and continue counting until a cell value is greater than the cell value immediately proceeding it." I want the number of cells counted, not a sum of the values within the cells. Then I need the formula to do that again and again through each cycle, and at the end of the column, post the maximum number of cells it counted for any one cycle (which shows me the longest time that the pump took to reduce the water level back to 2ft - I just multiply it by 5 sec). To further complicate it, I don't want it to count the numbers between 2 and 4 while the level is increasing, just when it is decreasing.
To give an example of a similar formula, there is another pumping system which I am monitoring. This data set simply returns "ON" or "OFF". I have the formula counting the maximum number of consecutive times that "ON" is returned, to ensure the pump does not run too long and burn up. It cycles over 100 times per day. The formula looks like this:
{=MAX(FREQUENCY(IF(V5:V293="OFF",ROW(V5:V293)),IF(V5:V293<>"OFF",ROW(V5:V293))))}
Another formula I have is to monitor a sensor and be sure that it is not going into a static state where it reurns the same readings. So, I have excel count the number of times it returns a consecutive identical reading. It looks like this:
=SUMPRODUCT((ABS(S5:S293-S6:S294)<=0)*(S6:S294<>""))
I have no idea how to approach this one. I have tried modifying those formulas above, but to no avail. I explained the purpose of the formula so that if anyone else has another idea of how I can approach the monitoring of the pump efficiency, they could throw it out there. I need this to work for a 97-2003 version. I can test it in either 97-2003 or 2010. But, it MUST work for a 97-03 version. Any help is greatly appreciated.
I have a data set that consists of 17,280 numbers (values pulled every 5 seconds over a 24 hr period for a water level). The purpose is to monitor the efficiency of a pump. So, the water level begins accumulating (after a pump cycle) and it is 2.0, 2.05, 2.07... all the way to about 4.0, where the pump is triggered to start pumping and pump the water level back down to 2 feet, which is when it will shut off and water will begin accumulating again. This cylces through roughly once per hour (24 cycles).
A perfect formula would be one which works like this: "Begin counting once a cell value is less than the value immediately preceeding it, and continue counting until a cell value is greater than the cell value immediately proceeding it." I want the number of cells counted, not a sum of the values within the cells. Then I need the formula to do that again and again through each cycle, and at the end of the column, post the maximum number of cells it counted for any one cycle (which shows me the longest time that the pump took to reduce the water level back to 2ft - I just multiply it by 5 sec). To further complicate it, I don't want it to count the numbers between 2 and 4 while the level is increasing, just when it is decreasing.
To give an example of a similar formula, there is another pumping system which I am monitoring. This data set simply returns "ON" or "OFF". I have the formula counting the maximum number of consecutive times that "ON" is returned, to ensure the pump does not run too long and burn up. It cycles over 100 times per day. The formula looks like this:
{=MAX(FREQUENCY(IF(V5:V293="OFF",ROW(V5:V293)),IF(V5:V293<>"OFF",ROW(V5:V293))))}
Another formula I have is to monitor a sensor and be sure that it is not going into a static state where it reurns the same readings. So, I have excel count the number of times it returns a consecutive identical reading. It looks like this:
=SUMPRODUCT((ABS(S5:S293-S6:S294)<=0)*(S6:S294<>""))
I have no idea how to approach this one. I have tried modifying those formulas above, but to no avail. I explained the purpose of the formula so that if anyone else has another idea of how I can approach the monitoring of the pump efficiency, they could throw it out there. I need this to work for a 97-2003 version. I can test it in either 97-2003 or 2010. But, it MUST work for a 97-03 version. Any help is greatly appreciated.
Last edited:
