I have a spreadsheet showing a table of indoor temperature sensor readings and am trying to count readings that are below a minimum that changes based on both the time of day (datetime column A) and the outside temperature (column Q). The minimum is 62 degrees between 10 PM and 6 AM, and 68 if the outdoor temperature is below 55 between 6 AM and 10 PM. There is no minimum if daytime temps are above 55.
I have conditional formatting that successfully highlights the below-minimum temperatures, but I am having a devil of a time counting those substandard readings for a summary header. I am using Google sheets, but I suspect there is an easy answer to my problem that is common to Excel.
The closest I've come is by creating a helper column to calculate the minimum using this formula in Column R:
=IF(OR(HOUR($A6)>22, HOUR($A6)<6),62,IF($Q6<55,68,0))
I was then able to get the result I wanted with this formula at the header:
=SUMPRODUCT(--(B6:B<$R6:$R))
However, the spreadsheet updates automatically, adding a row every hour, and I would like to avoid having to deal with updating the helper column, so I need everything to be in the count formula at the head of each column of sensor readings. I tried to incorporate the calculations for the helper column in the sumproduct formula as follows:
=SUMPRODUCT(--(B6:B<IF(OR(HOUR($A6:$A)>22, HOUR($A6:$A)<6),62,IF($Q6:$Q<55,68,0))))
But that returned a value of 482 when I knew that column only had 15 readings that should have been counted.
Can anyone help?
Edit:
Here is a sample
| Apartment: | 4F | 4J | 4N | Outside |
|---|---|---|---|---|
| "# Hours in Violation" | 1 | 1 | 3 | |
| 1/4/2023 10:00:00 | 70.88 | 73.04 | 70.7 | 58.1 |
| 1/4/2023 9:00:00 | 70.7 | 73.22 | 67.50 | 55.04 |
| 1/4/2023 8:00:00 | 68.18 | 72.32 | 67.82 | 54.14 |
| 1/4/2023 7:00:00 | 68.18 | 72.5 | 67.28 | 54.86 |
| 1/4/2023 6:00:00 | 67.28 | 71.6 | 66.38 | 53.96 |
| 1/4/2023 5:00:00 | 67.64 | 71.96 | 66.02 | 54.68 |
| 1/4/2023 4:00:00 | 67.82 | 61 | 66.56 | 57 |
The counter cells would be those after "# Hours in Violation", with the expected values reflecting the parameters described above. For purposes of this example, I've bolded the values that would be counted.
I would love a formula that works in Sheets.
Edit:
Again, and just to put all of the conditions in one place:
I want to count the values for each sensor that are below a calculated minimum. That minimum changes based on the time of day and the outdoor temp, as follows:
- Between 10 PM and 6 AM, as derived from column A (night), the minimum is always 62.
- Between 6 AM and 10 PM inclusive (day), the minimum is 68 IF the outside temperature (column Q) is less than 55, and zero if outside temperature is more than 55.
Thus, in the example table above, the expected counts appear in the row entitled "# hours in violation" and are bolded.
You can see my table with the working conditional formatting and the helper column here. The counter cell above the 1F column was my failed effort at incorporating everything I need into the SUMPRODUCT function. I also attempted to do this with a COUNTIF function with similarly nonfunctional results.
CodePudding user response：
If the sample table above is copied into new sheet at A1, then the following formula in B2 generates the indicated 'hours in violation' (N.B. delete the manually entered values in B2-D2 first):
=arrayformula(lambda(lastrow,bycol(n(B3:index(D3:D,lastrow)<ifs(not(isbetween(hour(A3:index(A3:A,lastrow)),6,22)),62,E3:index(E3:E,lastrow)<55,68,TRUE,)),lambda(x,sum(x))))(counta(A3:A)))
This should also automatically recalculate when additional rows are added.