Hi,
I have a long series of data organized by date in column A and a number in column B. The numbers are produced weekly and I would like to calculate the average of these numbers by quarter. If possible I would like to use an array solution.
Thanks
Why not use a Pivot Table? It's ideal for this and you easily group the dates by quarter. And, PT's are more "resource friendly" than array formulas. See attached.
Palmetto
Do you know . . . ?
You can leave feedback and add to the reputation of all who contributed a helpful response to your solution by clicking the star icon located at the left in one of their post in this thread.
Thanks for the reply. And I agree that the pivot route is the most efficient.
However, if someone can guide me through a solution using an array formula for monthly and quarterly averages that would be great. Thanks
Assuming column-A holds the dates and column-B holds the values to average:
Qtr-1: {=AVERAGE(IF(MONTH(A1:A37)<4,B1:B37))}
Qtr-2: {=AVERAGE(IF(MONTH(A1:A37)>3,IF(MONTH(A1:A37)<7,B1:B37)))}
Qtr-3: {=AVERAGE(IF(MONTH(A1:A37)>6,IF(MONTH(A1:A37)<10,B1:B37)))}
Qtr-4: {=AVERAGE(IF(MONTH(A1:A37)>9,B1:B37))}
Note: all formulas must be commited using Ctrl + Shift + Enter keys. Excel automatically adds the curly brackets.
Palmetto
Do you know . . . ?
You can leave feedback and add to the reputation of all who contributed a helpful response to your solution by clicking the star icon located at the left in one of their post in this thread.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks