A formula giving a Reference to a Variable Column Location

[BrianO]

I need to calculate the average of several columns in a worksheet, but
the location of these columns varies due to varying exported data from
an Access data base export. That is, if there are no values in a
certain field for an entire set of records, then no column is created
in the resulting excel worksheet, which results in the column for say,
field 26, being in the 26th column (at most) but often in an ealier
column because another column was not created.

The columns have the field name as text in the top row, and if I could
create a formula that wouild in essence calculate 'The average of the
column whose top cell ="field_name"', I'd be a happy guy. But I can't
figure it out.

Any ideas ?

Brian

 

[Kieran]

A formula like
=AVERAGE(INDEX($D$1:$F$52,,MATCH(A2,$D$1:$F$1,0)))
works.

bult look at the attachement for the specifics.

Attachment filename: average by heading.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=397919

 

[PegL]

You might also want to look at DAVERAGE. The database
lookup functions can be kind of tricky to deal with, but
it sounds like that may be of help in your situation.

 

[BrianO]

I tried Kieran's method and it works beautifully. I'll try Peg's idea
when things slow down a bit. Thanks to both for taking the time to
help.

Next time I'll swallow my pride and ask for help a lot sooner.

Thanks again

Brian O'Hara

Toronto, Ontario, Canada