combining INDIRECT in one formula?

Formula = AVERAGE(INDIRECT(B3&B4):INDIRECT(B3&B5))

INDIRECT is volatile, meaning formulas which call it are recalculated any time anything triggers recalculation. If cell B3 had the formula =INDIRECT("X99"), cell C3 had the formula =X99, and cell X99 contained the constant numeric value 1.25, then changing X99 to 2.75 would cause both B3 and C3 formulas to recalculate. However, if you then entered xyz in cell Z100, Excel wouldn't recalculate C3's formula, but it would recalculate B3's formula even though X99 hadn't changed.

If you have a lot of formulas calling INDIRECT, you can slow down recalculation substantially.

Given your layout,

Formula:

=AVERAGE(INDEX(INDEX($1:$1048576,B4,0):INDEX($1:$1048576,B5,0),0,MATCH(B3&"1",INDEX(ADDRESS(1,COLUMN($1:$1048576),4),0),0)))

would accomplish what you're trying to do without using volatile functions.

If you really must use INDIRECT, all you need is =AVERAGE(INDIRECT(B3&B4&":"&B3&B5)). INDIRECT's 1st argument must be a text string which looks like a range reference, and range references can contain multiple cell references joined by colons. You'd only need multiple INDIRECT calls if you needed to construct range references using cells which contain the cell addresses of other cells containing addresses.

Thanks guys! That helps answer the first part of the question, and is so easy to see I feel like a dummy for over-complicating it trying to nest INDIRECTs.

But here's the part where I might be opening Pandora's box, because more than likely I'm making it harder than I need to. But I'm still relatively novice at understanding what's going on in certain Excel formulae that when I find one that works for me, I tend to try and stick with it and tweak it since I can wrap my head around it.

It might be easiest if I don't try to describe the data set too much, so rather let's say I am successfully using an array formula like this:

{=(SUM(IF(MOD(ROW(Input!$AE$1:$AE$10000),15)=0,Input!$AE$1:$AE$20999,0)))}

Which pulls information from worksheet "Input", from a defined range, and essentially I'm just looking to see if I can replace the "Input!$AE$1:$AE$10000" portion (and also the latter "Input!..." part) and maybe utilize INDIRECT function to pull either "AE" and the starting and ending numerical values (e.g., "1" and "10000" in this case) from other cells?

Or perhaps having one cell that contains the string "Input!$AE$",

another containing "1",

and another containing "10000"

?

I think I'm trying to do something too convoluted, and there's most likely a way easier way to do this, but any help or suggestions if very much appreciated.

Thank you guys a ton! I'm always wowed by these Excel forums and the depth of knowledge so many people have in using Excel. Makes me wish I'd gone down a career where I needed to use it more often and in more in-depth ways.

Anyway, thanks, and happy New Years!

If you define the name Input_ALL referring to =Input!$1:$1048576, and the name ColumnLetters referring to =SUBSTITUTE(ADDRESS(1,COLUMN(Input_ALL),4),1,""), and you had AE in cell X97, 1 in X98 and 10000 in X99 in another worksheet, then in that same other worksheet,

Formula:

=SUMPRODUCT(--(MOD(ROW(INDEX(Input_ALL,X98,0):INDEX(Input_ALL,X99,0)),15)=0),INDEX(INDEX(Input_ALL,X98,0):INDEX(Input_ALL,X99,0),0,MATCH(X97,ColumnLetters,0)))

would give you the same result as the array formula you show.

I'll be more direct: in the long run you want to avoid INDIRECT and OFFSET when possible. This is a situation in which it's relatively easy to avoid using them.

Simpler than the formula above would be

Formula:

=SUMPRODUCT(--(MOD(ROW(INDEX(Input!$AE:$AE,X98):INDEX(Input!$AE:$AE,X99)),15)=0),INDEX(Input!$AE:$AE,X98):INDEX(Input!$AE:$AE,X99))

Note: when X98 is 1 and X99 is 10000, INDEX(Input!$AE:$AE,X98) is INDEX(Input!$AE:$AE,1) is Input!AE1, INDEX(Input!$AE:$AE,X99) is INDEX(Input!$AE:$AE,10000) is Input!AE10000, so INDEX(Input!$AE:$AE,X98):INDEX(Input!$AE:$AE,X99) is Input!AE1:AE10000.

Originally Posted by hrlngrv

If you define the name Input_ALL referring to =Input!$1:$1048576, and the name ColumnLetters referring to =SUBSTITUTE(ADDRESS(1,COLUMN(Input_ALL),4),1,""), and you had AE in cell X97, 1 in X98 and 10000 in X99 in another worksheet, then in that same other worksheet,

Formula:

=SUMPRODUCT(--(MOD(ROW(INDEX(Input_ALL,X98,0):INDEX(Input_ALL,X99,0)),15)=0),INDEX(INDEX(Input_ALL,X98,0):INDEX(Input_ALL,X99,0),0,MATCH(X97,ColumnLetters,0)))

would give you the same result as the array formula you show.

I'll be more direct: in the long run you want to avoid INDIRECT and OFFSET when possible. This is a situation in which it's relatively easy to avoid using them.

Simpler than the formula above would be

Formula:

=SUMPRODUCT(--(MOD(ROW(INDEX(Input!$AE:$AE,X98):INDEX(Input!$AE:$AE,X99)),15)=0),INDEX(Input!$AE:$AE,X98):INDEX(Input!$AE:$AE,X99))

Note: when X98 is 1 and X99 is 10000, INDEX(Input!$AE:$AE,X98) is INDEX(Input!$AE:$AE,1) is Input!AE1, INDEX(Input!$AE:$AE,X99) is INDEX(Input!$AE:$AE,10000) is Input!AE10000, so INDEX(Input!$AE:$AE,X98):INDEX(Input!$AE:$AE,X99) is Input!AE1:AE10000.

Hey thanks! That smiplified formula (using SUMPRODUCT as a non-array formula instead of the array formula I had been using) did serve the same purpose and with the added benefit of not having to be an array formula.

I haven't given the first portion of your suggestion a try yet, as it uses some features I'm not yet familiar with (for example defining names, as well as the SUBSTITUTE and ADDRESS functions), but I'll play around with those to get acquainted and give it a go.

There could be a minor snag in using Names, as the workbook also needs to be portable over to Google Sheets -- which I'm sure isn't within the scope of these forums, since they're for Excel users and not Google Sheets -- but I think you've really helped point me in a good direction.

Thanks so muche for the help everyone!

Originally Posted by AliGW

What is the reason for wanting to set the range parameters this way? Could there be something else in the data, perhaps in adjacent columns, that could be used to identify where the ranges should start and stop?

You're probably right in that it's an odd way to want to pull information from a range. I think the nature of the sheet I'm working with being somewhat ill-defined in purpose means it wasn't laid out the most efficient way.

I'll try and describte the sheet a bit below, but essentially it's probably just a mess and I'd do well to clean it up or start from scratch...

The nature of the spreadsheet is to input daily sales quantities of various items to track their depletion. As time has gone on, I've been looking to pull more and more analysis out of the data, so being able to parse it efficiently can take on various forms.

In the worksheet "Input", I have a number of columns, say E:BB, and in each one of those there is a header for the item being sold (ex - Brand #1, Brand #2, Brand #3, etc). There area various sizes of each item that may be sold, "Size #1, Size #2, Size #3, etc.); so one of the rows calculates a total of each brand sold (Brand #1 x Size #1 + Brand #1 x Size #2 + ...).

Every day is a distinct subrange of 15 rows (e.g. Row 4 to Row 18 contains data entry for Day #1; Row 19 to Row 33 is Day #2, etc)

That formula above, in another worksheet, let's say called "Analysis1", looks at all the data from all of the "Input" worksheet, and calculates the total sales of that brand over all entry dates in "Input", which happens to be the sum of every 15th cell, in a given column (Row 15 being the one summing the brand total sales in this case).

If I were to have a column in the table of sheet "Analysis1", I was hoping to insert the appropriate label of the column in "Input" for that given brand (for example, "Column AE" is the columna associated with Brand #50, "Column AF" is the name for Brand #51, etc). So that I wouldn't have to edit the formula every time I add a new brand to the list; that I could just copy the formula and it would know it's supposed to pull data from column "CA" by the time I get to adding that brand.

The total number of brands for which I'm tracking data will continue to grow, but not at a pre-determined rate, which is why I end up adding new columns (Brands) in Input as I go, resulting in a new row in my table on Analysis1 tab, and therefore having to add a new cell with the formula described above.

Apologies for the long description of the purpose of the sheet -- I'm sure someone with more foresight than myself could construct the whole workbook in a way that did these things a lot cleaner layout and more efficiently.

Thanks for reading though, and hopefully as I continue to grow in my Excel abilities, I'll get to that point.

Thanks y'all!

And to boot, simply using Input!$AE:$AE instead of Input!$AE$1:$AE$10000 saves the whole need of having to define and update the range anyway. So I definitely appreciate the help on getting me to see that too!

Would use of the array formula I had previously slow down performance any less or more when using whole column references?

Example:

{=(SUM(IF(MOD(ROW(Input!$AE:$AE),15)=0,Input!$AE:$AE,0)))}