Excel online - Array formulas using INDIRECT are not working

Question

Hi there,

I'm using the INDIRECT trick to calculate columns based on the dynamic length. It's portfolio data so the number of rows changes all the time.

Eg, Say Column AG has a range of AG2:AG55 with the value B or M, and in column AH I wish to use an array formula to determine the following:

=IF (AG2 = "B",1,0)

In cell AG1 I have a formula which prints the dynamic column range. (AG2:AG55).
With the INDIRECT function I have been using this format:

= IF (INDIRECT (AG1) = "B", 1, 0)

This produces a 1 or 0 all the way down to row 55 (and longer/shorter if the number of rows is more/less)

So far, this method has worked a treat for every other column - the formula fills in to the used range and adapts to changing row lengths. There are many tabs and columns of data, and so I've been using alot of INDIRECT formulas.

However, it has ceased working on a couple of columns now. It is possible that Excel online restricts how many of these can be used in a sheet?

Eg, in the image, see how the columns around this empty column all have data, based on indirect functions, but it has simply stopped working!

I want to only calculate for the range used, and I figured the INDIRECT solution is the best, instead of say, using:

= IF (AG2:AG1000 = "B", 1, 0) which will put an array formula in and capture all rows up to 1000, but I find it to be not so elegant.

Thanks!

Answer 1

Could you share a test file without sensitive information?

You may upload file in private message.

Answer 2

Thanks so much for the quick reply!

The method of using the # to denote a range is genius - it still doesn't work on this column, however.

I will quadruple check my formula for this column and see if using less INDIRECT functions will kick start the calculation. Just checking - even though it's excel online, is it worth replacing the INDIRECT functions with the # method, since INDIRECT is a volatile function?

Actually, whilst trying to replace the INDIRECT formulas, I found a good example of formulae not calculating (despite being automatic)

I've attached an image of a cell where the logic clearly should return a 1, but it is zero instead. Very odd!

Thanks

Steve

Answer 3

From your description, If AG column is an array, you may try this one.

AG column.

=IF(AG2#="B",1,0)

If AG column is not an array.

=IF(INDIRECT("AG2:AG55")="B",1,0)