CODEWITHSUNDEEP
excelexcel-formula
Jesse Wyatt
Jesse Wyatt

Reputation: 113

Excel: Applying functions to a range of ranges using multi-cell array-formula

Can you apply a builtin function that takes a range (e.g. AVERAGE()), and have it apply to a multiple ranges?

I'm currently finishing up a pretty basic data-analysis unit with a small Excel segment and I had a past assignment that involved analysing some housing data.

One task involved averaging and rounding the residential housing prices of a few cities for each time period.

The target data is in the worksheet Residential_Houses, formatted such that cities are columns and time periods are rows spanning: C7:J30. With the goal to have the averages of each row in the worksheet Analysis spanning C7:C30.

My simple solution was to write the formula:

=ROUND(AVERAGE(Residential_Houses!C7:J7), 2)

Into Analysis!C7 and then used Excel's "AutoFill" feature to generate the formulae for Analysis!C8:C30. This worked fine, and the results were correct but I lost marks for not explicitly using cell array formula.

Instead the sample answer I was given was this monolithic line:

{=ROUND((Residential_Property!C7:C30+Residential_Property!D7:D30+Residential_Property!E7:E30+Residential_Property!F7:F30+Residential_Property!G7:G30+Residential_Property!H7:H30+Residential_Property!I7:I30+Residential_Property!J7:J30)/8,2)}

It seems clear that the intent of this code is to take the average of columns C-J for each row 7-30, but it avoids using the AVERAGE() builtin. This works, but it's cumbersome, and wouldn't scale if there were more than a few columns in this test data.

The crux of my question here is how can you combine functions that already operate on ranges, with cell array formula to process multiple ranges with a single formula?

My intuition tells me it might be something like this:

{=AVERAGE((C7:J7):(C30:J30))}

Or something involving indirect addressing, but I can't seem to find any detailed information on the topic. All of the array formula resources I've found so far have been limited to basic arithmetic and single dimension ranges. Could someone point me in the right direction here, or is this just not possible with array formula?

EDIT: For anyone who stumbles upon this in the future. I chose Barry's solution because it helped me understand how to generalise this concept (replacing the range with a function that can slice a range). But as Scott notes, OFFSET() is volatile; so INDEX() is a better way to do this if you're working with large datasets.

Upvotes: 2

Views: 1020

Answers (2)

barry houdini
barry houdini

Reputation: 46371

Similar to Scott's suggestion, SUBTOTAL can process an array of ranges created by an OFFSET function, so with the first argument of SUBTOTAL as 1 (for AVERAGE) this formula will give an array of rounded averages for each row

=ROUND(SUBTOTAL(1,OFFSET(C7:J30,ROW(C7:J30)-ROW(C7),0,1)),2)

entered in a vertical range and confirmed with CTRL+SHIFT+ENTER

or you can use the same OFFSET inside an AVERAGE function like this:

=ROUND(AVERAGE(OFFSET(C7:J30,ROW(C7:J30)-ROW(C7),0,1)),2)

Upvotes: 3

Scott Craner
Scott Craner

Reputation: 152585

Here is a formula that can be entered as one array formula and get results. It can also be entered normally and copied down.

=ROUND(AVERAGE(INDEX($C$7:$J$30,ROW()-MIN(ROW($C$7:$J$30))+1,0)),2)

enter image description here

Upvotes: 3

Related Questions