Forum Discussion
End of quarter data
Hello Every one,
I have daily data and need quarterly data with value at end of the quarter. Not average, sum or anything.
Its 7 years daily data and for each quarter need end-of-quarter data against it.
Let's say your data are on a sheet named Data, starting in row 3, in columns A to K.
If you have Microsoft 365 or Office 2021:
On another sheet, enter this formula in A2:
=EDATE(DATE(2006,12,31),SEQUENCE(29,,0,3))
DATE(2006,12,31) is the date of the first end-of-quarter that you need (31-12-2006).
In B2, enter the formula
=XLOOKUP(A2,Data!$A$3:$A$3000,Data!$B$3:$K$3000,"",-1)
Fill down to the last used row.
If you have an older version:
In A2 and A3, enter the first two end-of quarter dates, for example 31-12-2006 and 31-03-2007.
Select A2 and A3, then use the fill handle in the lower right corner of A3 to fill down until you the end-of-quarter dates for 7 years.
In B2, enter the formula
=VLOOKUP($A2,Data!$A$3:$K$4000,COLUMN())
Fill to the right to K2, then fill down.
4 Replies
- You could create a Pivot Table and group by quarter.
https://exceljet.net/pivot-tables/pivot-table-group-by-quarterPatrick2788 Thanks buddy, but I don't need sum, average, or anything. Just need value at the end of quarter
Let's say your data are on a sheet named Data, starting in row 3, in columns A to K.
If you have Microsoft 365 or Office 2021:
On another sheet, enter this formula in A2:
=EDATE(DATE(2006,12,31),SEQUENCE(29,,0,3))
DATE(2006,12,31) is the date of the first end-of-quarter that you need (31-12-2006).
In B2, enter the formula
=XLOOKUP(A2,Data!$A$3:$A$3000,Data!$B$3:$K$3000,"",-1)
Fill down to the last used row.
If you have an older version:
In A2 and A3, enter the first two end-of quarter dates, for example 31-12-2006 and 31-03-2007.
Select A2 and A3, then use the fill handle in the lower right corner of A3 to fill down until you the end-of-quarter dates for 7 years.
In B2, enter the formula
=VLOOKUP($A2,Data!$A$3:$K$4000,COLUMN())
Fill to the right to K2, then fill down.
- Thank you so much 🙂
