Please help w/ INDIRECT

[St@cy]

I get "No Show" everytime for the following:

=IF(ISERROR(INDIRECT(ADDRESS(26,MIN(COLUMN(X33:IV33)*(X33:$IV33<>"")))))=TRUE,"No Show",INDIRECT(ADDRESS(26,MIN(COLUMN(X33:IV33)*(X33:$IV33<>"")))))

I have an INDIRECT function that works for MAX, but I now want to use MIN.
I need a way to return "No Show" if all columns are blank in columns X:IV.
Please help me fix this function.

 

[T. Valko]

Hard to tell what you're trying to do but the error is generated if any cell
is empty in this range:

X33:IV33

This will cause the MIN function to return 0 which is then passed to ADDRESS
as the column number arguemnt and it evaluates to an invalid address:

=ADDRESS(26,0)

This causes ISERROR to evaluate to TRUE resulting in "No Show".

If you're wanting to return the value from X26:IV26 that corresponds to the
first non-empty cell in X33:IV33 try this array formula** :

=IF(COUNTA(X33:IV33),INDEX(X26:IV26,MATCH(TRUE,X33:IV33<>"",0)),"No Show")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Biff

 

[JMB]

It could also be caused by any cell in that range having an error value. But
since the OP specified "No Show" when all columns are blank in X33:IV33, I
assume that was the purpose of the ISERROR in the original formula and not
because those cells can actually evaluate to errors.

 

[St@cy]

Your array formula seems to be going in the right direction. $X$26:$IV$26
are dates. I want the formula to return the first date a student attends
class and "no show" if they have not recieve any attendance hours (X33:IV33).

 

[T. Valko]

Ok, so what's in X33:IV33? Is it text or numbers or a mix of both? Are there
any formulas in that range that return blanks?

Biff

 

[St@cy]

X33:IV33 are attendance hours (ie 2.5), but some users like to type "E" for
excused. If a student does not attend class, it is left blank.

 

[T. Valko]

Try this array formula:

=IF(COUNT(X33:IV33),INDEX(X26:IV26,MATCH(TRUE,ISNUMBER(X33:IV33),0)),"No
Show")

Format as DATE

Biff

 

[St@cy]

It works wonderfully! Thanks.

Okay, maybe you can help with a very similar problem. The follow works, but
as I'm finding out it's causing a lengthy calculation time.
=INDIRECT(ADDRESS(26,MAX(COLUMN(V33:IV33)*(V33:$IV33<>""))))
I'm reading that the INDIRECT function is what is causing the problem.
Can you modify the your formula to now find the last date the student
attended class?

 

[T. Valko]

While we're at it, this version will replace your current formula for the
MAX date:

Still an array formula:

=IF(COUNT(X33:IV33),INDEX(X26:IV26,MATCH(1000,X33:IV33)),"No Show")

Format as DATE

Biff

 

[St@cy]

YOUR GOOD! Thank you!!! : )

While we're at it, this version will replace your current formula for the
MAX date:

Still an array formula:

=IF(COUNT(X33:IV33),INDEX(X26:IV26,MATCH(1000,X33:IV33)),"No Show")

Format as DATE

Biff

 

[T. Valko]

Still an array formula:

=IF(COUNT(X33:IV33),INDEX(X26:IV26,MATCH(1000,X33:IV33)),"No Show")

Check that! That formula does not need to be array entered. It can be
normally entered.

Biff

 

[T. Valko]

You're welcome. Thanks for the feedback!

Biff

YOUR GOOD! Thank you!!! : )