0

enter image description here

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?

9
  • Try using =query(u24:v34, "Select Avg(V) where U <= timeofday '09:59:00'") Link to similar question webapps.stackexchange.com/questions/88774/…
    – Oleg_S
    Jan 28, 2021 at 13:43
  • @Oleg_S Thanks. I tried your suggestion and it returns a parse error. I changed the comma to a semi-colon (u24:v34;) due to locale and it returns the string avg to cell V40 and the number 8 to cell V41. However, that still won't select only records between times, will it? Jan 28, 2021 at 14:47
  • @Oleg_S further analysis of the result figure 8 in V41 and what it represents is unclear.I have provided a link in the question to the sheet. Jan 28, 2021 at 15:59
  • Still don't fully understand if Time in Column U is only for test1, or for all tests. Following is for assumption that time is one for all tests, in other case you'd need 2 more time columns. Added Copy of your Sheet.
    – Oleg_S
    Jan 29, 2021 at 8:53
  • @Oleg_S sorry, my bad explanation. The time in column U is for all tests. The 3 tests are part of the same event so at that time, 3 values are recorded. I've added a further clarification in the body of the question. Jan 29, 2021 at 10:46

1 Answer 1

0

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
Averages All Tests

B.   Avg.All Averages all Averages
Averages All Tests

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.