[Excel] INDIRECT() method receving #VALUE! in an array formula

[Duncanson]

Good afternoon,

I'm doing some final touches on a program that I've been making over the past month, and I received help previously here regarding averaging values over a variable range by using the INDIRECT() method {Thread: [Excel] Variable Average Function Coding Issue [Fixed]}.

As with all experimental data, some data sets that my group has gathered is not complete and skews the final averaged data, but it is very hard to notice since there are pages of data for any given experiment (we call these experiments 'Runs' because it's traffic speed data).

What I wanted to do was create a formula that when averaging multiple runs, the formula will ignore any data in a run that is incomplete or has an error [ { } represents that it is an array formula designated by pressing ctrl+shift+enter in excel]:

{=SUM(
	IF(
		AND(
			NOT(ISERROR($AA4:$AO4)),
			INDIRECT(ADDRESS(11,19+COLUMN($AA4:$AO4),2))=TRUE()
			),
		$AA4:$AO4,
		0
		)
	)}

The INDIRECT() portion of the code is used to locate the cell where it indicates wether the run has a complete set of data or not. However, I'm getting a #VALUE! error due to this portion of the array formula. The only reason that I can think of why is that the indirect formula does not like creating an array of referencing to be used.

I would prefer not to use macro's, but if anyone has any insight as to why the code is not functioning I would be extremely greatful. I will include a screenshot of the relevant portion using this code so people can have a better idea on what is going on.

Thanks in advance,

Dunc