Converting Monthly Index Values to Quarterly

[filtered78]

Hello, new to the forum, first time caller. Appreciate any help someone can provide with the following issue:

I have two indices with historic gross returns, the start date of each being equal to 100. I would like to graph the performance of the two indices to illustrate the difference in returns over a period of time.

The problem is that one index is in quarterly format and the other index is in monthly format. I would like to convert the monthly format to quarterly so I can compare the two on a historical, quarterly return basis. Is there an excel formula that converts my monthly index data into quarterly index data?

As an example, I have attached an excel document with two tabs: Tab 1 shows the quarterly data and Tab 2 shows the monthly data.

Appreciate any help you can provide.

[Keebellah]

Hi welcome to the forum, the credits are not mine but try this:

place the following formula in column D row 1

Please Login or Register  to view this content.

Then drag it down to the last row and it will give you the quarter.
Found it doing a Google search and came across this link: http://www.vbaexpress.com/forum/arch...p/t-11375.html

[filtered78]

Hi welcome to the forum, the credits are not mine but try this:

place the following formula in column D row 1

Please Login or Register  to view this content.

Then drag it down to the last row and it will give you the quarter.
Found it doing a Google search and came across this link: http://www.vbaexpress.com/forum/arch...p/t-11375.html

Thank you for your response, but unfortunately that is not what I am seeking to do.

I am trying to convert the MONTHLY index data (column B) in Tab 2 into QUARTERLY index data like the QUARTERLY index data (column C) in Tab 1 so that I may then be able to line chart the two series (now that they are the same frequency in terms of time periods) on a chart and compare the difference in returns over time.

[mike7952]

This what you mean?

Please Login or Register  to view this content.

[avk]

Refer attach file.
In monthly index sheet : Add two column. In column "C" calculate "Q1" / "Q2" / "Q3" / "Q4" & column "D" extract year from column "A" Date
Column "C" Formula :

Please Login or Register  to view this content.

Column "D" Formula :

Please Login or Register  to view this content.

In sheet "Quarterly Index" in column "D" Formula

Please Login or Register  to view this content.

Criteria is "Q1" and year 1988

[JohnTopley]

Is the quarterly index an AVERAGE of the 3 monthly figures?

Try

in "Quarterly Index" D2

=AVERAGE(INDEX('Monthly Index'!$B$1:$B$348,(ROWS($1:1)-1)*3+1):INDEX('Monthly Index'!$B$1:$B$348,ROWS($1:1)*3))

copy down

[avk]

Yes John OP not mentioning any sum or avg.

Please Login or Register  to view this content.

If you required average then only change =AVERAGEIFS instead of =sumifs

Please Login or Register  to view this content.