ADDRESS use inside a function

**aries** · 02-08-2010, 07:13 PM

I'm trying to calc a Simple Moving Average of financial Fund data (row=fund type, column=date) on a very large matrix for a particular fund and date range. I locate the Fund of interest and retrieve the Start and End dates of interest and retrieve their addresses using the ADDRESS function. Since ADDRESS returns a text string, HOW DO I incorporate it's results into the following array function (which ignors blanks and 0 cells): {=AVERAGE(IF($L$10:$V$10>0,$L$10:$V$10,FALSE))}?

**shg** · 02-08-2010, 08:07 PM

Welcome to the forum.

You could wrap the expression you use to generate the string in INDIRECT, and use that to replace the hard-coded addresses you have.

Better (much better) would be to use dynamic named ranges: see http://www.contextures.com/xlNames01.html and many other sources on the web.

**aries** · 02-09-2010, 12:01 AM

shg, Thanks for the suggestions but still stumped.
INDIRECT will give me the value for an individual cell but I need a string of cells, that string being variable based on how many cells are requested in the average. For instance I use: =ADDRESS(MATCH(NASDAQ,FUND,0),MATCH(02/04/10,DATE,0)) giving me the address for the cell representing end date for NASDAQ (FUND & DATE are range names for the various funds and dates in the matrix). A similar function gives me the start date address by offsetting from 02/04/10 by the requested number to be averaged. Wrapping these in the INDIRECT function is not accepted unless I'm doing something wrong.
Not sure a dynamic range will work either since the string could be anywhere in the matrix.....unless of course I don't understand how to write it.
ANY help is appreciated!

**protonLeah** · 02-09-2010, 02:02 AM

(NOT TESTED)

If you want to try the INDIRECT fn, you can build the range string by concatenating the two ADDRESS fns. Since you only provided one, I'll use it twice, green for start and red for end. In some unused cell, say AA1 put:

Please Login or Register  to view this content.

Then in your AVERAGE, formula, replace the $L$10:$V$10 with INDIRECT(AA1):

Please Login or Register  to view this content.

**DonkeyOte** · 02-09-2010, 03:26 AM

It strikes me that given your use of Excel 2007 you can simply use an AVERAGEIF with an INDEX ... I see no real need for use of INDIRECT (Volatile)

If we assume (interpret your initial formula):

a) dates are horizontal - defined by DATE named range
This is not a good name given DATE is a function - let's rename it DATES (let's assume for sake of explanation this is B1 to say Z1 onwards)

b) funds are vertical - defined by FUND named range (let's assume for sake of explanation this is A2:A100)

c) fund of interest is defined by NASDAQ named range

d) values <=0 are to be ignored

Then

1: create a named range that encompasses the data points - let's call this DATA (using our estimated ranges this would be $B$2:$Z$100)

2: create our function to return the AVERAGE based upon the above conditions

Please Login or Register  to view this content.

the above would return the Average of all data points > 0 for given Fund between specified dates.

However, if the dates listed in DATES named range are sequential you should find you can simplify the above yet further.

If you're unable to get the above to work post a sample - dummy any values that are confidential (eg Funds if not publicly available)

**aries** · 02-09-2010, 07:21 PM

Thanks to all for getting me over the hurdle. Problem solved!:
Suggestions from protonLeah and Donkey Ote work just fine in EXCEL7. If using 7 I'd use Donkey Otes' suggestion since it's cleaner (avoids the use of ADDRESS). If in EXCEL3, use protonLeah's.
Again, thanks....Aries