Average to date

[Sean Bartleet]

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

[Biff]

Hi!

Try this:

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

Biff

"Sean Bartleet" <seanbxxx@yahoo.com> wrote in message
news:%23428ouA6FHA.4012@TK2MSFTNGP14.phx.gbl...
> 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
>

[Sean Bartleet]

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" <biffinpitt@comcast.net> wrote in message
news:%231q4O8A6FHA.1416@TK2MSFTNGP09.phx.gbl...
> Hi!
>
> Try this:
>
> =AVERAGE(C7:INDEX(C7:N7,MATCH(O$6,C$5:N$5,0)))
>
> Biff
>
> "Sean Bartleet" <seanbxxx@yahoo.com> wrote in message
> news:%23428ouA6FHA.4012@TK2MSFTNGP14.phx.gbl...
>> 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
>>
>
>

[Biff]

Hi!

>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.

That is correct.

> Is this documented anywhere?

I'm sure it is "somewhere" but I've never seen it myself. I learned it from
this group! 90% of what I know I've learned right here!

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

I've never tried (or needed) to use that method in a lookup (that I can
recall). How are you trying to implement it?

Biff

"Sean Bartleet" <seanbxxx@yahoo.com> wrote in message
news:uLCGuOB6FHA.3676@TK2MSFTNGP10.phx.gbl...
> 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" <biffinpitt@comcast.net> wrote in message
> news:%231q4O8A6FHA.1416@TK2MSFTNGP09.phx.gbl...
>> Hi!
>>
>> Try this:
>>
>> =AVERAGE(C7:INDEX(C7:N7,MATCH(O$6,C$5:N$5,0)))
>>
>> Biff
>>
>> "Sean Bartleet" <seanbxxx@yahoo.com> wrote in message
>> news:%23428ouA6FHA.4012@TK2MSFTNGP14.phx.gbl...
>>> 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
>>>
>>
>>
>
>