I need to create a logic excel formula that calculates the average of 3 months prior to a specified start date. There are multiple start dates. For instance:
For ID 1, The Start Date is May-2021. I would need for the logic to calculate the average between Feb-2021 to Apr-2021 to get 91.67. For ID 2, the Start Date is Jun-2021, so I would need to calculate the average of Mar-2021 to May 2021 to get 108.33. I also would need to calculate the average of 6 months prior in a separate column.
| ID | Start Date | Calculation Result | Jan-2021 | Feb-2021 | Mar-2021 | Apr-2021 | May-2021 | Jun-2021 |
|---|---|---|---|---|---|---|---|---|
| 1 | May-2021 | 91.67 | 50 | 100 | 75 | 100 | 25 | 0 |
| 2 | Jun-2021 | 108.33 | 0 | 25 | 100 | 175 | 50 | 125 |
| 3 | Apr-2021 | 83.33 | 100 | 150 | 0 | 75 | 0 | 200 |
Any help is greatly appreciated! (Not opposed to VBA suggestions either)
CodePudding user response：
use INDEX to create the range.
=AVERAGE(INDEX(D2:I2,MATCH(B2,$D$1:$I$1,0)-3):INDEX(D2:I2,MATCH(B2,$D$1:$I$1,0)-1))
Or if they are true dates we can use AverageIfs:
=AVERAGEIFS(D2:I2,$D$1:$I$1,">"&EOMONTH(B2,-4),$D$1:$I$1,"<="&EOMONTH(B2,-1))
