Google Sheets average analysis by time value

Question

Link to test sheet

In the above segment of a wider Google sheet I want to analyse the results of test1 test2 and test3 according to the time of the test to the extent that I wish to average all results taken in the dark yellow range (Matin 00:00 - 10:00), the yellow range (Jour 10:00 - 16:00) and the blue range (Soir 16:00 - 23:59).

The timing colour coding comes from underlying conditional formatting and the test results similarly for readability purposes (nothing more).

Problem:

using =iferror(AVERAGEIF(V5:V35; "<>0");"") for test1 will generally seems to work but extending that formula to test2 =AVERAGEIFs(W5:W35;U5:U35; "<=10:00") and so test3 =AVERAGEIFs(X5:X35;U5:U35; "<=10:00") will result in an incorrect results in test2 since times in test1 will be included as will results in tes3 because the results in test1 and test2 will be included as the meet the criterion specified.

In Soir, test1 I have attempted to overcome this by using the following formula =query(u24:v34, "Select Avg(V) where U '<=09:59'") but this causes a formula parse error.

EDIT #1: The tests are performed daily (where blank no test that day was carried out) and there are 3 measures each test (see the test sheet) Column A represents the day of the month. The intention is summarise in the table at line 37 the 3 tests. Line 37 is the average overall for each test and what is required is to expand that in lines 38, 39, 40 to anaylyse the overall results in line 37 to show how it differs for the different time periods. The 3 tests actually relate to blood pressure - systolic, diastolic and Pulse rates respectively (abbreviated to test1,2 & 3 for simplicity and clarity).

EDIT #2 29 Jan 21 (15:45) In test sheet - "test analysis logic" tab (derived from Copie de Test) following intervention by @Oleg_S the conditional ranges have been modified and the number of results reduced to 6 - 2 from each time period of three to provide a more easy to follow test bed which appears to give the expected result. A second opinion on my understanding of that success would be useful.

Am I taking the right approach to solving this conundrum or is there a better way to make sure that the analysis of results in the various time slots are correctly reported?

Answer 1

A.   Avg.All Averages All Tests

=LET(hr,A1:A25, tests,B1:D25,
   start,{0;0;10;16}, end,{24;10;16;24},
   {{"Avg.All"; "Avg.Matin"; "Avg.Jour"; "Avg.Soir"},
     MAP(start,end, LAMBDA(s,e, BYCOL(tests, LAMBDA(t, 
       ARRAYFORMULA(AVERAGEIF(
         t*(hr>=s/24)*(hr<e/24),">0"))))))})

• If the time periods don't have an equal number of tests, then it could be the case that Avg.All <> AVERAGE(Avg.Matin, Avg.Jour, Avg.Soir)

B.  Avg.All Averages all Averages

=LET(hr,U5:U34, tests,V5:X34,
  start,{0;10;16}, end,{10;16;24},
  lbl,{"Avg.All"; "Avg.Matin"; "Avg.Jour"; "Avg.Soir"},
  avg,MAP(start,end, LAMBDA(s,e, BYCOL(tests, LAMBDA(t, 
    ARRAYFORMULA(AVERAGEIF(
      t*(hr>=s/24)*(hr<e/24),">0")))))),
  {lbl,{BYCOL(avg, LAMBDA(c, AVERAGE(c))); avg}})

• If the time periods don't have an equal number of tests, then Avg.All may not be an accurate representation of the average of all tests but it will always be the case that Avg.All = AVERAGE(Avg.Matin, Avg.Jour, Avg.Soir)

---

Images

A.   Avg.All Averages All Tests

B.   Avg.All Averages all Averages