[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

[kaliman79912]

Are you sure its the INDIRECT part? it looks as it would work.
I am not that experienced with array formulas and that may change this assesment, but that part, since COLUMN($AA4:$A04) would always be 27, why dont put 46 instead of 19+COLUMN($AA4:$A04). I tested it and changing that does get rid of the error.

[Duncanson]

Are you sure its the INDIRECT part? it looks as it would work.
I am not that experienced with array formulas and that may change this assesment, but that part, since COLUMN($AA4:$A04) would always be 27, why dont put 46 instead of 19+COLUMN($AA4:$A04). I tested it and changing that does get rid of the error.

It's definitely the INDIRECT() part because I did an array formula to just grab the values that it is suppose to be receiving but it gives me the same error for each column in the Valid Runs section. The whole point of an array formula is to be able to use ranges within functions that need only one input. It's similar to a conditional counting formula where it will only count the number of cells that satisfy a condition

{ =COUNT(
	IF(
		ISNUMBER($AA4:$AO4),
		)
	) }

Output = 9 for this row

The reason for the 19+COLUMN($AA4:$AO4) is to select the proper column depending on which run it is. So for the first run, I want it to look to see if it is a valid run and add it to the sum if it is indeed valid; for the second run, I need the address to shift to the right one column to select the valid condition for the second run. If I only had it set to 46, it would only look at the first run's validity and not the others.

I may just have to do it the hard way and create a whole new chart where if the conditions aren't satisfied then it will not include the numbers for that run; but for my program that would mean that I would add another 9000 formulas to get all of the required cells

[kaliman79912]

Have you looked into the OFFSET Function? it may be enaugh to replace all the INDIRECT(ADDRESS(11,19+COLUMN($AA4:$AO4),2))

This formula would do the same of what you are looking for, exept that the chek for the error may prevent the if of adding it, but then the sum is stopped. lets explore it.

{=SUM(
          IF( 
              AND(
                     NOT(ISERROR($AA4:$AO4)),
                    (OFFSET($AA4:$AO4,0,19)=TRUE)
                   ),
              $AA4:$AO4,
              0
             )
         )}

[kaliman79912]

BINGO! I think I have it...

{=SUM(IF(OFFSET($AA4:$AO4,7,19),IFERROR($AA4:$AO4,0)))}

{=SUM(IF(OFFSET($AA4:$AO4,7,19),IFERROR($AA4:$AO4,0)))}

[kaliman79912]

well, that will work for just the first row. I tried using 11-ROW($AA4:$AO4) to keep all rows relative but a #VALUE appears. The solution I came up though is very good i Think.

Select data from AT11:BH11 (where valid run value is), assign the range a name, like "ValidRun"

then your formula will read

{=SUM(IF(ValidRun,IFERROR($AA4:$AO4,0)))}

Without the range name still works just like this:

{=SUM(IF($AT$11:$BH$11,IFERROR($AA4:$AO4,0)))}

But I always think its more elegant to use range names

[kaliman79912]

You can even do it without the need of the array!

=SUMIFS(AA4:AP4,AA4:AP4,">0",$AT$11:$BI$11,TRUE)

or based on the named range:

=SUMIFS(AA4:AP4,AA4:AP4,">0",ValidRun,TRUE)