• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Oldest Outstanding record formula (array) showing #N/A error

Carley

New Member
Hello Hoping that someone can assist,


I have a formula which I want to bring back the oldest outstanding record for the current month.


I have a worksheet named Outstanding which has three columns

Column A has date range showing

One for outstanding not refered (Column B)

One for refered to one team and still outstanding (Column c)

One for referred to other team and still outstanding (Column d)


On the front sheet I have the array formula as follows but is showing #N/A error


{=IF(IF(MIN(IF(Outstanding!B95:B124=0,"",Outstanding!A95:A124),IF(Outstanding!D95:D124=0,"",Outstanding!A95:A124),IF(Outstanding!C95:C124=0,"",Outstanding!A95:A124))=0,10000,MIN(IF(Outstanding!B95:B124=0,"",Outstanding!A95:A124),IF(Outstanding!D95:D124=0,"",Outstanding!A35:A124),IF(Outstanding!C95:C124=0,"",Outstanding!A35:A124)))=10000,"Complete",MIN(IF(Outstanding!B95:B124=0,"",Outstanding!A95:A124),IF(Outstanding!D95:D124=0,"",Outstanding!A95:A124),IF(Outstanding!C95:C124=0,"",Outstanding!A95:A124)))}


Please can someone assist as to where I have gone wrong?
 
Hi Carley ,


Your ranges are not matching ; suppose we eliminate all the sheet+cell references , and use named ranges such as :


First : =Outstanding!A95:A124

Second : =Outstanding!B95:B124

Third : =Outstanding!C95:C124

Fourth : =Outstanding!D95:D124


then your formula will become :


=IF(IF(MIN(IF(Second=0,"",First),IF(Fourth=0,"",First),IF(Third=0,"",First))=0,10000,MIN(IF(Second=0,"",First),IF(Fourth=0,"",A35:A124),IF(Third=0,"",A35:A124)))=10000,"Complete",MIN(IF(Second=0,"",First),IF(Fourth=0,"",First),IF(Third=0,"",First)))


Here , the problems are the two usages of the range A35:A124 ; change this to A95:A124 , and you should probably get the correct result , though I have not verified the correctness of the formula.


Narayan
 
Thank you so much - this has worked!!


It was due to me having A35:A124 rather than A95:A124, and I did a find replace to change the numbers, must have missed this one.


thanks again Narayan
 
Back
Top