I have two worksheets both have a date column and columns corresponding with the 24 hours of the day. One worksheet has a percentage in each hour cell and the other worksheet has a number in each hour cell. I am trying to average the numbers worksheet cells when the percentage of the corresponding date is over a certain percentage. I would like to be able to change the date range and also the percentage range. So if I set criteria as greater than 90% for the 0:00 hour in Workbook 1 Column B I would want to average Workbook 2 B3 (11), B4(11) and B7(13) and get 11.67. These workbooks have 5 years worth of dates if that makes a difference
WORKBOOK 1 | WORKBOOK 2 | |||||||||||
A | B | C | A | B | C | |||||||
1 | 0:00 | 1:00 | | | 1 | 0:00 | 1:00 | | | |||
2 | 1/1/2020 | 88% | 75% | | | 2 | 1/1/2020 | 14 | 13.75 | | | |
3 | 1/2/2020 | 100% | 75% | | | 3 | 1/2/2020 | 11 | 11 | | | |
4 | 1/3/2020 | 94% | 33% | | | 4 | 1/3/2020 | 11 | 11 | | | |
5 | 1/4/2020 | 67% | 75% | | | 5 | 1/4/2020 | 14 | 13.75 | | | |
6 | 1/5/2020 | 60% | 75% | | | 6 | 1/5/2020 | 14 | 13.75 | | | |
7 | 1/6/2020 | 91% | 67% | | | 7 | 1/6/2020 | 13 | 13 | | |