Hi all, been a long time viewer of the forum and felt it was finally time I signed up, said hello, thanked you all and then asked you all for help.

I have a column with dates running down it. I need a column that will collect every third month and copy it into a column next to it. Effectively I need a column (without spaces) next to the date column that will show quarterly dates. I need this formula to be automated for future drag and autofill features when newer dates are added in.

If i've not been clear with what I am looking for then please ask any questions you need.

Thanks for your help in advance

Hi,

Do you mean you need it to sum all values that fall into each quarter?

Which version of Excel are you using?

Any chance of a sample workbook with dummy data?

S.

Hi Spencer,

Thanks for getting back to me, I've attached an example of what I'm working with. I'm sure there is a fairly simple solution but excel can't seem to recognize the pattern so I'm having to do it manually for over 300 workbooks. Plus these workbooks are update automatically each month so I would like for the formula to be able to account for that.

Thanks,

Dan

Do you want to recreate the smaller table (J6:L11) from the values already in columns E & F, or am I completely missing the idea here?

Yes that's it, well along with the value from column B as well. Effectively it is a table to show quarterly results, so return the values of cells B, E + F every 3 months with the intervals being every March, June, September and December.

Many thanks

Put the below formulas in these cells and copy down.

K6 - =VLOOKUP(J6,B:E,4,FALSE)
L6 - =VLOOKUP(J6,B:F,5,FALSE)

That is perfect, I didn't even think to do a vlookup. Thanks for your help Spencer,

Dan

Not a problem, Dan :)

Also, I don't want to start a new thread because I don't want to clog up the board with my questions but if a moderator feels I should ask this question somewhere else then please feel free to move the question.

If anyone could take a look at the new attachment I've just added to this post. What you will see is a yellow box with certain formulas which work out percentages for year to date statistics, 1 year , 3 year (annualized) and since the inception (annualized). I'm wondering if there is a way to tweak these formulas so that they adjust when a new month is added.

For example when a new month is added the 1 year stat formula would go from:

{=PRODUCT(1+C32:C38)-1}

to

{=PRODUCT(1+C33:C39)-1} to account for the extra month

The year to date figure is a little more difficult because as extra months are added then the range would increase and not shift. If anyone has any thoughts on this or experience with trying to automate this process then please get in touch.

Much appreciated,

Dan

Just thought as well, to be clear I wouldn't want a running record of the statistics, just a box that has the latest figures.

Which version of Excel are you using?

I will have a look at this when I get home in about 90 minutes.

I'm currently running 2010, I'm leaving the office myself in a about an hour so there is no rush as I won't be able to respond until tommorow (i'm in the UK).

But many thanks in advance for taking a look,

Dan

I'm in the UK too.

Is there a reason you're using that product array formula? Is it used to create a specific number or should it just showing a combined total for three months? Replying from my phone so cannot view the spreadsheet right now.

Not a problem, I'm not 100% sure why I use the product array formula, I'm fairly new to excel, only been using it about 6 months, and a lot of stuff I was shown and that was one of them. I think it's something to do with that fact that the percentages represent investment returns so as the return increases so does the increase in the percentage, so a simple sum(...) does not add it up correctly in the context of the sum. If that makes sense...although that is just my assumption.

Hi

Can you not use dynamic ranges.

Ermm...let's just assume I have not heard of dynamic ranges... because I've never heard of dynamic ranges. I'm still very much a beginner at excel. Do you have the time to explain it to me?

Many thanks,

Dan

"dpgreen" wrote:
I have a column with dates running down it. I need a
column that will collect every third month and copy
it into a column next to it.

By "copy", I presume that you mean a formula. Suppose your original data
are in A2:A1000, and you want the "copy" to start in B2. Put the following
formula into B2 and copy down as needed:

=INDEX($A$2:$A$1000,3*ROWS($B$2:B2)-2)

If you want to set up one time so it covers new data, you can put the
following formula into B2 and copy down through B1000:

=IF(INDEX($A$2:$A$1000,3*ROWS($B$2:B2)-2)="","",
INDEX($A$2:$A$1000,3*ROWS($B$2:B2)-2))

Pay close attention to the use of absolute references (e.g. $A$2) and
relative references (e.g. B2).

Also, although OFFSET and INDIRECT might seem easier to use, they are
"volatile" functions. That causes those formulas and all dependent formulas
to be recalculated every time any cell in any worksheet is modified. INDEX
is more efficient.

"dpgreen" wrote:
I've attached an example of what I'm working with.
[....]
|Download: http://www.excelbanter.com/attachment.php?attachmentid=658|

Sorry, I did not see this response until long after I posted my previous
response.

Adapting to your example, enter the following formulas as indicated, then
copy down through row 11:

J6: =INDEX(B$6:B$24,3*ROWS(J$6:J6)+1)
K6: =INDEX(E$6:E$24,3*ROWS(K$6:K6)+1)
L6: =INDEX(F$6:F$24,3*ROWS(L$6:L6)+1)

If you would like those formulas to work as add data following row 24, enter
the following as indicated, then copy down as many rows as you like:

J6: =IF(INDEX(B$6:B$24,3*ROWS(J$6:J6)+1)="","",
INDEX(B$6:B$24,3*ROWS(J$6:J6)+1))
K6: =IF(INDEX(E$6:E$24,3*ROWS(K$6:K6)+1),"","",
INDEX(E$6:E$24,3*ROWS(K$6:K6)+1))
L6: =IF(INDEX(F$6:F$24,3*ROWS(L$6:L6)+1)="","",
INDEX(F$6:F$24,3*ROWS(L$6:L6)+1))

Obviously, it would be better if the values in columns E and F were unique
so that you could see that the formulas are "copying" the correct values.

That's perfect, thanks for your help