Sum and Total from Large Data Set

[ZochSteveo]

Hi there everyone,

I have been working hard to conduct an analysis for my dissertation. I have an expansive dataset which has recorded regular temperature levels in different homes in a house. Temperature is recorded every 5 minutes. I have attached below a little snippet so that it should make a bit more sense what is going on. I will try and describe it as best as I can. Essentially in column A, I have the date and the corresponding time (each at a 5-minute interval) followed then by the temperature recordings in the 3 different rooms in column B, C and D respectively. Column E and F have the sunrise and sunset time for that particular day.

In column G, I have an indicator as to whether or not the time in column A falls inbetween the sunrise and sunset time for that particular day and so can be considered "daylight". This returns TRUE if correct, and FALSE if not. I then have just the date in the following column.

Finally, in column I, I have an indicator as to whether or not the temperature in column B is equal to or above 26 degrees celsius, and if it falls into the daylight time as well. It outputs a value of 1 if this is the case and 0 if it is not.

Essentially, I want to be able to calculate the percentage of time that the temperature is equal to or above 26 degrees celcius for each individual day and its corresponding daylight timings (which as explained are all different). So I need a way of tallying up all the 1s in the column, and then dividing by the number of 5-minute intervals there are for that day. As you can probably tell, this data set is expansive and so contains a lot of readings, most of which are not relevant as the temperature is not equal to or above 26 degrees. What should be my approach here as I need to calculate a percentage of time the temperature is over 26 degrees for those days which do have recordings of over 26 degrees?

Any thoughts would be greatly appreciated. The snapshot I have included using XL2BB is only small as I cannot upload that much on here, but hopefully should be informative enough as it includes a section where the temperature was over 26 degrees.

Many thanks,

TRIAL ATTEMPT 2 copy.xlsx
	A	B	C	D	E	F	G	H	I
25249	6/26/24 15:55	25.967 °C	N/A	N/A	4:55	21:30	TRUE	6/26/24	0
25250	6/26/24 16:00	25.939 °C	N/A	N/A	4:55	21:30	TRUE	6/26/24	0
25251	6/26/24 16:05	25.905 °C	N/A	N/A	4:55	21:30	TRUE	6/26/24	0
25252	6/26/24 16:10	25.945 °C	N/A	N/A	4:55	21:30	TRUE	6/26/24	0
25253	6/26/24 16:15	26.011 °C	N/A	N/A	4:55	21:30	TRUE	6/26/24	1
25254	6/26/24 16:20	26.068 °C	N/A	N/A	4:55	21:30	TRUE	6/26/24	1
Sheet6

Cell Formulas
Range	Formula
E25249:E25254	E25249	=INDEX($M$2:$M$184, INT((ROW()-1)/288) + 1)
F25249:F25254	F25249	=INDEX($N$2:$N$185, INT((ROW()-1)/288) + 1)
G25249:G25254	G25249	=AND(A25249>=DATEVALUE(TEXT(A25249, "dd/mm/yyyy")) + TIMEVALUE(TEXT(E25249, "hh:mm:ss")), A25249<=DATEVALUE(TEXT(A25249, "dd/mm/yyyy")) + TIMEVALUE(TEXT(F25249, "hh:mm:ss")))
H25249:H25254	H25249	=INT([@[Date-Time]])
I25249:I25254	I25249	=IF(AND([@[Daylight Indicator]]=TRUE, ISNUMBER([@[Lounge Temperature]]),[@[Lounge Temperature]]>=26),1,0)

 

[etaf]

Essentially, I want to be able to calculate the percentage of time that the temperature is equal to or above 26 degrees celcius for each individual day and its corresponding daylight timings

where do you want the output ?

you could get a unique set of dates
=unique( h2:h1000) whatever the range is
then
using that date
sumif(h2:H1000,the cell with the first unique date in , I2:I1000) / countif( h2:H1000,the cell with the first unique date in)

if you just want the daylight - G = TRUE

countifs( h2:H1000,the cell with the first unique date in , G2:G1000, TRUE)

sumif(h2:H1000,the cell with the first unique date in , I2:I1000) / countifs( h2:H1000,the cell with the first unique date in , G2:G1000, TRUE)