Biff,

Thanks for the formula, it works great.

I would appreciate some explanation as to how the formula works.

I have never seen a formula where a formula is used as part of an range
reference.

I understand the INDEX(C7:N7,MATCH(O$6,C$5:N$5,0)). This would normally
return the value of the cell for the specified month. It would seem that
when used as part of a range reference that it returns the cell reference
and not the value.

Is this documented anywhere?

I can get this to work with an If function but not the Vlookup function. Is
this a limitation of the Vlookup function?

Thanks again for the help.

Sean


"Biff" wrote in message
...
Hi!

Try this:

=AVERAGE(C7:INDEX(C7:N7,MATCH(O$6,C$5:N$5,0)))

Biff

"Sean Bartleet" wrote in message
...
Hi,

I have a number of items that I track monthly costs on in rows 7 to 21.
In columns C to N are the months Jan to Dec. I would like to average the
costs to a specified month. As this month can change It is entered in
Cell O6. At present I match the month specified in O6 and add that to 66
to get the character value of the column I am interested in. Then I use
indirect to create the reference I require the average to be done on.

vis:=AVERAGE(INDIRECT("C7:"&CHAR(MATCH(O6,C5:N5,0) +66)&"7"))

Now I want to copy this down to the 10 rows below. So that it works
correctly I will need to create references to the rows as well as the
columns. This can be done with some more indirect functions but I was
hoping that there was an easier way.

Any suggestions will be appreciated.

Regards.

Sean