Excel Wizards, I got a stumper for ya...and a neat add-in!

Factual Questions
1

I have been working with Excel for a long time now, and consider myself fairly good at it. I’ve created TONS of workbooks, including a graphics based, country macroeconomic information tool that damn-near approached 50 megs!

Off and on I have been trying to work this one problem out, and I just can’t seem to get it right. I need to return values from certain cells based on their relation to the latest figure OR in relation to another cell. And I can’t seem to get =OFFSET & =VLOOKUP to work the way I want them to. Here’s the situation:

I mainly have to track fund and/or economic performance. So that means I get MONTHLY figures that generally come in a two column format- first column date, second column the number (be it % or ???). I do bunches of calculations on these returns/whatever, and come up with series that then need to be graphed or included in a table. Now, we use these spreadsheets over and over again for a long time. So that means that every month, a new figure gets added to the bottom of the list, one row below the previous month’s figure. Got that? So:

Jan-01 100.00
Feb-01 101.50
Mar-01 102.60
Apr-01 103.97
May-01 105.27
Jun-01 #N/A
Jul-01 #N/A

June and July aren’t (obviously) available yet. I use the #N/A because that allows you to extend the series in a graph, and when a new figure gets inputted, the graph updates automatically.

Problem: I can graph this fine, but I also need to do some stat work on the figures and put that info into a table that links to a Word doc. How do I do a rolling 3-month average that automatically updates every time a new figure is inputted? So, right now I can average March, April and May, but how do I get Excel to drop March’s 102.60 and add June’s figure to the 3-month calc when June’s new figure comes out?

I can do it with =IF statements that look for the #N/A and return the previous 3 months, but that is an extra column, and eats up memory. This gets to be a problem when emailing a 15 meg file to South Africa… PLUS, I often need to do 3 month averages/sharpes/std. deviations/alpha’s/sortino’s, etc. So that is one column per calc, and sometimes another column used to get the latest figure from THAT column…AAAHHHH!!! Pretty soon I have 30 columns doing the work that I wish a few cells could do.

So, How do I get a reference returned that changes B3:B5 to B4:B6 when B6 gets inputted (or something similar)? I’d be able to use that reference in formulas and do my calcs like that, eliminating extra columns of calcs. I’ve thought of Naming, Arrays, etc. But I can’t seem to get this one worked out.

OK, for your efforts, here is a site that has a FREE add-in that I have found quite useful:

http://www.asap-utilities.com/

200 little tricks to make your Excel life easier, for free. My favorite: Select a group and perform a calculation on all of them at once. I get percentage numbers in whole-number format ALL the time, so I need to divide them by 100 to get a % number. That is normally done in the next few columns, then copied and pasted as values. Which is my second favorite tool- you can highlight and then copy>paste Special> paste as values a set of formulas all with Ctrl-shift-P! Plus you can Transpose formulas, or even copy formulas and paste them without them changing their reference’s, or, or or or…

You get the picture.

Thanks for the help-
-Thomas

2

Use COUNT, COUNTA or DCOUNTA to calculate an index that you can use as an offset for your rolling average lookups. Make sure the argument is a range long enough to include all possible future cells. You can reference the entire column A with the syntax A:A.

When you add a new entry to the list, the result of these functions will increment, and you can use that to find the latest values for your average.

3

How about defining names for the relevant cells (start & end of qtr) and then using those names in your formulas. When you add a new month’s data, you can just delete the names and add the same name to the new cells. All the formulas will then use the new cell reference.

4

Thanks folks! Got me started in the right direction. I used a combination of COUNT (to count the rows like you said), two CONCANTENATE’s (to match a cell with the letter ‘B’ with the number from the COUNT) to give me a reference (B3 in one cell, B6 in another), and INDIRECT to put the concantenated cells into a formula.

What a pain!

But it works and when I fix my spreadsheets, it should take out about 30 columns of formulas per sheet (and close to 20 sheets!).

Thanks for the help-
-Tcat

5

Well, gee, that sounds like a total kludge, but hey - whatever works.

I was imagining using 3 INDEX or LOOKUP functions, each one using the result of a COUNT function (each one offset incrementallly) as arguments, to actually retrieve the last three entries.

But your way works too. :smiley:

6

The 3-mos moving average was just an example. I normally have to do 12-mos, life of fund, etc. (which I assume would require one COUNT for every instance…unless I just did COUNT minus 1, 2, whatever…HHHmmmm). I’ve never used INDEX before. I just started to play with it, and it looks neat. I think I can use it for something else…

Thanks for the help.

-Thomas