| Date | Group A | Group B | Group C | Group A |
|---|---|---|---|---|
| Sub Group | A1 | B1 | C1 | A2 |
| 1/1/2022 | 35 | 12 | 54 | 10 |
| 1/2/2022 | 43 | 45 | 62 | 93 |
| 1/3/2022 | 76 | 65 | 39 | 48 |
| 1/4/2022 | 12 | 25 | 81 | 18 |
| 1/5/2022 | 89 | 76 | 20 | 26 |
| 1/6/2022 | 23 | 87 | 47 | 17 |
| 1/7/2022 | 56 | 59 | 21 | 53 |
| 1/8/2022 | 29 | 51 | 9 | 68 |
| 1/9/2022 | 76 | 8 | 52 | 35 |
| 1/10/2022 | 36 | 53 | 38 | 53 |
User Input
Start Dt - 1/1/2022
End Dt - 1/5/2022
Group - Group B
Question What is the daily average of a Group given the above user input?
Formula
=AVERAGEIFS(INDEX($B$2:$E$11,,MATCH($I$3,$B$1:$E$1,0)), $A$2:$A$11, ">="&$G$3, $A$2:$A$11, "<="&$H$3)
Answer
44.6
User will select a start date, end date and Group. I want to compute the daily average of that. The issue arises when there are multiple columns with same group as Averageif takes the first column only.
Issue - How can I find the daily average of Group A for the given dates, given that Group A are in two columns (they can't be combined as there are multiple sub groups)
CodePudding user response：
If you are on Microsoft-365 then can try-
=AVERAGE(FILTER(FILTER(B3:E12,B1:E1=I2),(A3:A12>=H1)*(A3:A12<=H2)))
CodePudding user response：
If you have an older version you could use:
=AVERAGE(
INDEX(A1:E12,
AGGREGATE(15,6,ROW(A3:A12)/(A3:A12>=G3)/(A3:A12<=H3),
ROW(A1:INDEX(A:A,SUMPRODUCT((A3:A12>=G3)*(A3:A12<=H3))))),
AGGREGATE(15,6,COLUMN(B1:E1)/(B1:E1=I3),
TRANSPOSE(ROW(A1:INDEX(A:A,SUMPRODUCT(N(B1:E1=I3))))))))
Entered with ctrl shift enter (I think)
In the picture below J3 shows the answer cell. K3 is used to demonstrate the array that is used for the AVERAGE function (a Office 365 spill range).
CodePudding user response：
Another old school approach. User input fields in the formula are referenced using named ranges.
=SUMPRODUCT(($A$3:$A$12>=StartDt)*($A$3:$A$12<=EndDt)
*($B1:$E$1=Group)*($B$3:$E$12))/(COUNTIFS($A$3:$A$12,">="&StartDt,$A$3:$A$12,"<="&EndDt)
*(COUNTIF($B$1:$E$1,Group)))
