Please help w/ INDIRECT

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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
 
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.
 
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).
 
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
 
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.
 
Try this array formula:

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

Format as DATE

Biff
 
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?
 
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
 
YOUR GOOD! Thank you!!! : )

T. Valko said:
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
Click to expand...
 
Still an array formula:
=IF(COUNT(X33:IV33),INDEX(X26:IV26,MATCH(1000,X33:IV33)),"No Show")
Click to expand...

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

Biff
 
Back
Top