DRSteele
Well-known Member
- Joined
- Mar 31, 2015
- Messages
- 2,640
- Office Version
- 365
- Platform
- Windows
Can someone please help me?
I am looking to create a formula using function FILTER to extract data using dates as criteria. As you can see, it is not as easy as expected. The problems arise when the Start Month is a higher value than the End Month because it wraps around the year and when the Start Day is a higher value than the End Day because it wraps around a month.
I want to use function FILTER to extract and report the filtered Dates and the average of Value for each of the four examples.
[The formula in F10 obviously goes all the way down. I did not paste all the data here.]
I am looking to create a formula using function FILTER to extract data using dates as criteria. As you can see, it is not as easy as expected. The problems arise when the Start Month is a higher value than the End Month because it wraps around the year and when the Start Day is a higher value than the End Day because it wraps around a month.
I want to use function FILTER to extract and report the filtered Dates and the average of Value for each of the four examples.
[The formula in F10 obviously goes all the way down. I did not paste all the data here.]
MrExcel posts18.xlsx | |||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | |||
1 | |||||||||||||||||||||
2 | Example1 | Example2 | Example3 | Example4 | |||||||||||||||||
3 | Start | End | Start | End | Start | End | Start | End | |||||||||||||
4 | Month | 2 | 3 | 4 | 5 | 11 | 1 | 10 | 2 | ||||||||||||
5 | Date | Month | Day | Year | Value | Day | 1 | 28 | 20 | 7 | 6 | 16 | 23 | 9 | |||||||
6 | 4/11/2010 | 4 | 11 | 2010 | -10 | Year | 2019 | 2020 | 2005 | 2009 | 2017 | 2020 | 2019 | 2020 | |||||||
7 | 4/12/2010 | 4 | 12 | 2010 | 2 | Date | 2/1/2019 | 3/28/2020 | 4/20/2005 | 5/7/2009 | 11/6/2017 | 1/16/2020 | 10/23/2019 | 2/9/2020 | |||||||
8 | 4/13/2010 | 4 | 13 | 2010 | -4 | ||||||||||||||||
9 | 4/14/2010 | 4 | 14 | 2010 | -9 | Date | Average Value | Date | Average Value | Date | Average Value | Date | Average Value | ||||||||
10 | 4/15/2010 | 4 | 15 | 2010 | 0 | need dynamic formulas | need dynamic formulas | need dynamic formulas | need dynamic formulas | ||||||||||||
Sheet11 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
B6:B3655 | B6 | =SEQUENCE(3650,,40279) |
C6:C3655 | C6 | =MONTH(B6#) |
D6:D3655 | D6 | =DAY(B6#) |
E6:E3655 | E6 | =YEAR(B6#) |
J7:K7,S7:T7,P7:Q7,M7:N7 | J7 | =DATE(J6,J4,J5) |
F6:F10 | F6 | =RANDBETWEEN(-10,10) |
Dynamic array formulas. |