Average formula with a variable range

[Cmic]

Hi guys

I'm trying to create an Average forumula with a variable range.

My problem (maybe it's a problem?) is that my reference for my Match's is in the file the formula is in but the amounts I want averaged are in another excel file. So I'd like the variable range to be dictated by one sheet (a moving date each month) but the average doing its thing on data in another excel file.

My current unsuccesful formula is:

=AVERAGE('AnotherExcelFile'!$P$9:INDIRECT(ADDRESS(MATCH('Cover Page'!B28,'AnotherExcelFile'!$O$4:$AA$4,0),MATCH('Cover Page'!B28,'AnotherExcelFile'!$A$4:$AA$4,0),,,)))

So this formula is in an excel file that I want the output in and is also in the same file as the 'Cover Page' source, which is just like 01/05/11. All the data is in AnotherExcelFile.

So not sure what I'm doing/not doing, if I drop the average part at the start and just have an Indirect function I get the right cell reference I'm after, but I don't know how to add it to the average function, so without all the formula stuff it would look like this:

=AVERAGE('AnotherExcelFile'!$P$9:$W$9)

Any help would be greatly appreciated Looking to keep the formula as simple/smart as I can and not involve macros/names, etc

Thanks!

 

[borgem]

Have you tried using the subtotal function? What you would need to do is make the data range you are referencing into a table and name it, so that when data is added, you use the names reference and it resizes. Subtotal(1,Array) will give you an average.

 

[Cmic]

Thanks for the reply. I mightn't have explained myself properly, or I may not being understanding your answer.

So the data in my other file is already set, it's monthly data, so Jan = 5.1, Feb = 4.7, Mar = 5.2, etc. As I move through the year I'm looking for my range in my Average formula, which starts at Jan (Say Jan is cell A1 for ease of explaining, then Feb is B1, etc), to move along as the months move along. The month is indicated by CoverPage!B28 in my formula in my original post. This Cover Page and also where the formula is being set is in one file while the month data is in another file. So each month when I change the Cover Page from March to April for example I'd like the range in my Average function to move from A1:C1 (for March) to A1:D1 (for April). So A1 is fixed so that's all good, I'm just trying to make C1/D1/etc, dependent on what is at CoverPage!B28. I was trying to solve this by using Indirect and Match (as I need a cell reference output) to populate the C1/D1/etc part of the range but my formula isn't working

I hope that's a little clearer......also I'm trying not to use names where possible as if I can get a successful formual I'll be copying it to many spreadsheets so would like to keep it as smart and as simple as possible so setup time isn't that bad

Thanks!

 

[Aladin Akyurek]

Does this...

=AVERAGE('AnotherExcelFile'!$P$9:INDEX('AnotherExcelFile'!$P$9:$AA$9,MATCH('Cover Page'!B28,'AnotherExcelFile'!$P$4:$AA$4,0)))

help?

 

[Cmic]

It does, thanks for you help!

 

[Aladin Akyurek]

It does, thanks for you help!

You are welcome. Thanks for providing feedback.


Posted from Güzelçamlı at the Aegean cost.