# Nested if statements Arrays - help!!

D

#### Diver Dan

I basically have a collum of dates and I want to have a descriptive
cell with the range of dates. The dates are all entered from another
sheet by a user and this page is a summary page the formula looks
something like:

=A25&"-"&IF(AND(A26=0,A27=0,A28=0,A29=0,A30=0,A31=0,A32=0,A33=0,A34=0,A3
5=0,A36=0,A37=0),A25,IF(AND(A27=0,A28=0,A29=0,A30=0,A31=0,A32=0,A33=0,A3
4=0,A35=0,A36=0,A37=0),A26,IF(AND(A28=0,A29=0,A30=0,A31=0,A32=0,A33=0,A3
4=0,A35=0,A36=0,A37=0),A27

with A25 being the first date and the sequence of IF statements
determining what the end date should be. This works fine untill the 7th
If statement, because I guess that is the maximum nested ifs one can
have. It seems using an array will help with this but I am lost. Any
help is appreciated.

--

D

#### Debra Dalgleish

If all the cells contain a date or a zero, the following formula should
work:

=TEXT(A25,"mmm dd, yyyy") &" - "& TEXT(MAX(\$A\$26:\$A\$39),"mmm dd, yyyy")

D

#### Diver Dan

Debra Dalgleish said:
=TEXT(A25,"mmm dd, yyyy") &" - "& TEXT(MAX(\$A\$26:\$A\$39),"mmm dd, yyyy")

This did it-thank you!!! Just one question --- why the \$A\$26 or really
why the \$ in the formula. I looked up max - I should have done this
before but I am still not sure ..... Again much thanks!!!!!!

D

#### Debra Dalgleish

You're welcome.

The \$ character makes it an absolute reference. If you copy the formula
to another cell, it will continue to point to the range A26:A39 (For
consistency, I should have used \$A\$25 in the first part of the formula.)

There's information about absolute vs. relative references in Excel's
Help files, and a short tutorial here:

http://www.jmu.edu/computing/tutorials/microsoft/excel/move/creating.shtml

D

#### Diver Dan

This works great thank you , thank you!!!! One question why the \$ in
the Max arguements?