Formula for variable transformation

Hello. Thanks in advance for helping me with this question.

I have a 20-yr data set of hourly hydroelectric dam discharge data. I would like to calculate the consecutive number of hours of no power generation (variable called ‘Turbine Units’). I was able to research and find out how to do that but I would like to perform a data transformation where pulsing or short periods of little hydroelectric generation is “excused” and a formula converts those hourly data points to 0. I define this as data in the column ‘Turbine Units’ with value of 1 for two consecutive hours and before noon. You can see my poor performing formula in the 'Mod T Units' column in the attached sample file. For example, the formula is appropriately transforming data on 7/23/17 on the 6:00 and 7:00 am hours to 0. However, my formula inappropriately transforms data at the beginning of a block of turbine generation (eg. 11:00 am and noon on 7/22/17 and 11:00 am on 7/24/17) to 0 when the data should not be transformed.

Thanks!

The problem appears to be that when you test the last two hours Units, you should only be making an adjustment if the Unit Total (e.g. SUM(G11:G12) ) is 2? Instead your using <=2, so a total of 1 will return TRUE for Logical2 in the AND() function?
You could try:=IF(AND(G10=0,SUM(G11:G12)=2,[@Time]<=0.5),0,[@[Turbine Units]]) in H12, which when replicated in the rest of Mod T Units should work as you expect.

Hercules,
Thanks for your reply. However, when replicated your suggested correction does not correctly transform the two consecutive hours of 1 turbine pulsing to 0 (H31 and H32). It will correctly transform only the second of the two hours, which obviously still will disrupt what I would like to consider one consecutive block of zero turbine generation in Mod T Units column.

I will upload the example file again with the rows in Mod T Units highlighted in yellow where the formula should return 0. All other rows the formula should return the value in Turbine Units. You can imagine that with 20 yrs of hourly data there is just about every scenario and it will be imperative that formula only transform in the narrow conditions described in the original post.

I appreciate any further assistance!

Hello - I'm sorry that my suggestion didn't resolve your issue. I don't think that I fully understand what your trying to do, as the formulas you have don't seem to line up with your explanation in the first post. Based on your latest attachment, we have 3 cells (H12,13, and 60) that are making the wrong adjustments, so instead of me suggesting a formula, let us look at why your formula isn't working in these cells and perhaps you can find the right amendment without upsetting other results.

Cell H12 - In the AND() statement, one or more of the 3 logicals must return FALSE in order for the value in Turbine Units (1) to be returned. G10=0, SUM(G10:G12)<=2, and [@Time] <=0.5 are all TRUE
Cell H13 - As for H12 except that logical2 is SUM(G11:G13)<=2 (TRUE)
Cell H60 - As for H12 with logical2 SUM(G58:G60) <=2 (TRUE)

Hope that helps

Hercules1946 said:

Hello - I'm sorry that my suggestion didn't resolve your issue. I don't think that I fully understand what your trying to do, as the formulas you have don't seem to line up with your explanation in the first post.

Click to expand...

Thanks again for your response Hercules. I am not sure how to answer. Yes, my attempt at a formula doesn't do what I want it to do in all cases but I thought it was better to leave a starting point. I will restate my desire in another way and try to be more clear. I would like to have another column called "Mod T Units" where the values would be "Turbine Units" except in a very narrow set of circumstances and since of we are talking about almost 182,000 rows of hourly data I prefer the formula to perform correctly without having to check each row of data. If this was what you were suggesting?

Based on your latest attachment, we have 3 cells (H12,13, and 60) that are making the wrong adjustments, so instead of me suggesting a formula, let us look at why your formula isn't working in these cells and perhaps you can find the right amendment without upsetting other results.

Click to expand...

I may not have been very clear in my original post on the set of circumstances in which I would like the formula to return the alternative value of the number zero instead of "Turbine Units" value. I was trying to be brief but maybe some more background might help. On many days of the year this dam may not produce power until late morning. Once they start for the day then they continue producing power until late that evening, so there is a block of time where Turbine Units values are 1 or more. I would like to be able to have a count of consecutive hours where no power was generated (Turbine Units = 0) before this block of turbine generation. I have a formula that can do that correctly.
The problem is there are occasions when they will produce power with one turbine for one hour or for two hours before and apart from the major block of power generation for that day. I would like a formula that could exempt or overlook this pulse of generation and instead return a zero in the Mod T Units column for those rows of data. So the instances, before noon, where Turbine Units has a value of 1 for one hour or 1 for two consecutive hours that is also apart from a block of consecutive rows of Turbine Units values would return the alternative value of zero. In all other cases the formula would return Turbine Units value. I then would utilize the Mod T Units column for counting the consecutive hours of zero generation.
Hopefully that clarifies.
Thanks for any further assistance.