Nested if statements Arrays - help!!

[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.

--

 

[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")

 

[Diver Dan]

=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!!!!!!

 

[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

 

[Diver Dan]

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