Hello, Im trying to write a formula that calculates an average for a location under a specific period.
I have to specify the start and end dates and also the location and then get the average.
With one location I was able to calculate the average using averageifs, but if there are multiple locations I don't know how to make it go calculate the average on the correct column. The data set is a few hundred columns long, so when I specify the location I need the formula to use it to refer to the correct column in the spreadsheet.
For example, the formula should return the following 2 results for the dataset at the below.
<tbody>
</tbody>
<tbody>
</tbody>
I have to specify the start and end dates and also the location and then get the average.
With one location I was able to calculate the average using averageifs, but if there are multiple locations I don't know how to make it go calculate the average on the correct column. The data set is a few hundred columns long, so when I specify the location I need the formula to use it to refer to the correct column in the spreadsheet.
For example, the formula should return the following 2 results for the dataset at the below.
| Start | End | Location | Average | ||
| 2/1/2018 | 5/31/2018 | C | 4.25 | ||
| 8/1/2018 | 11/30/2018 | D | 6 |
<tbody>
</tbody>
| Date | A | B | C | D | E | |
| 1/1/2017 | 5.00 | 2.00 | 6.00 | 6.00 | 6.00 | |
| 2/1/2017 | 8.00 | 7.00 | 4.00 | 1.00 | 5.00 | |
| 3/1/2017 | 5.00 | 9.00 | 8.00 | 9.00 | 6.00 | |
| 4/1/2017 | 0.00 | 1.00 | 1.00 | 4.00 | 4.00 | |
| 5/1/2017 | 9.00 | 8.00 | 4.00 | 4.00 | 4.00 | |
| 6/1/2017 | 8.00 | 7.00 | 4.00 | 6.00 | 6.00 | |
| 7/1/2017 | 1.00 | 7.00 | 1.00 | 0.00 | 7.00 | |
| 8/1/2017 | 7.00 | 4.00 | 6.00 | 8.00 | 3.00 | |
| 9/1/2017 | 5.00 | 3.00 | 6.00 | 4.00 | 0.00 | |
| 10/1/2017 | 4.00 | 0.00 | 2.00 | 4.00 | 2.00 | |
| 11/1/2017 | 6.00 | 6.00 | 3.00 | 8.00 | 9.00 | |
| 12/1/2017 | 8.00 | 8.00 | 0.00 | 9.00 | 6.00 |
<tbody>
</tbody>
